Dynamic Query

[Swanson1962]

It's been awhile since I worked with Access -- I was always a novice but didn't do to bad -- but now haven't worked in a while and need to make a dynamic query based on the old dynamic query code I had gotten in 1997 but it's not working in the new version of 2003.

The problem seems to be that it trips up on the where statement in the "mid" part of it:

"Select * from [A Maintenance Work Order <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Main</st1lace>] " & (" where " + Mid(Where, 6) & ";"))

I remember at that point it would "open" up a query but I implemented into requerying the subform...

Any help would be appreciated and thank you,

L



Dim db As Database<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim qd As QueryDef<o></o>
Dim Where As Variant<o></o>
<o> </o>
Set db = CurrentDb()<o></o>
<o></o>
On Error Resume Next<o></o>
db.QueryDefs.Delete ("AQryDynamicQBF")<o></o>
AFrmMWOStatisticalSubform.Requery<o></o>
<o></o>
On Error GoTo 0<o></o>
<o> </o>
Where = Null<o></o>
Where = Where & " AND [Status]= '" + Me![MWOStatusCL] + "'"<o></o>
Where = Where & " AND [Maint Rep]= '" + Me![MaintRepCL] + "'"<o></o>
Where = Where & " AND [Priority]= '" + Me![PriorityCL] + "'"<o></o>
Where = Where & " AND [Location]= '" + Me![LocationCL] + "'"<o></o>
Where = Where & " AND [Area]= '" + Me![AreaCL] + "'"<o></o>
Where = Where & " AND [Equip]= '" + Me![EquipCL] + "'"<o></o>
Where = Where & " AND [Requested By]= '" + Me![RequestedByCL] + "'"<o></o>
Where = Where & " AND [Call Backs]= '" + Me![CallBackCL] + "'"<o></o>
<o></o>
If Not IsNull(Me![IssueEndingDate]) Then<o></o>
Where = Where & " AND [Issue Date] between #" + _<o></o>
Me![IssueBeginningDate] + "# AND #" & Me![IssueEndingDate] & "#"<o></o>
Else<o></o>
Where = Where & " AND [Issue Date] >= #" + Me![IssueBeginningDate] _<o></o>
+ " #"<o></o>
End If<o></o>


<o></o>
Set qd = db.CreateQueryDef("AQryDynamicQBF", _<o></o>
"Select * from [A Maintenance Work Order <st1lace w:st="on">Main</st1lace>] " & (" where " + Mid(Where, 6) & ";"))<o></o>
AFrmMWOStatisticalSubform.SourceObject = "AFrmMWOStatisticalSubform"<o></o>

 

[james_lankford]

change
Dim Where As Variant
to
Dim Where As string

get rid of
On Error GoTo 0

change
Where = Null
Where = Where & " AND [Status]= '" + Me![MWOStatusCL] + "'"

to
Where = " [Status]= '" + Me![MWOStatusCL] + "'"


change

Set qd = db.CreateQueryDef("AQryDynamicQBF", _
"Select * from [A Maintenance Work Order Main] " & (" where " + Mid(Where, 6) & ";"))

to

dim sql as string
sql = "Select * from [A Maintenance Work Order Main] where " & Where
debug.print sql
Set qd = db.CreateQueryDef("AQryDynamicQBF", sql)

 

[Norie]

Why code?

Couldn't this be done using a parameter query?

If you are creating queries in code all the time then I'm pretty sure there might be a good chance that you might end up with a corrupt/corpulent database.

 

[Swanson1962]

Thank you, I will try it and let you know how it goes... I have no clue if a parameter would work (as I said I am a novice). I know the dynamic worked well due to all the combo lists that it needed to search through...

This time I think I have 15 combo lists they want to be able to filter on -- yikes...

 

[Swanson1962]

it will take a bit to transpose but


thank you )))))

 

[Swanson1962]

Well, I'm trying just to quickly see if this will work with the older one and it's a bit better but it gets tripped up on this code:

Set qd = db.CreateQueryDef("AQryDynamicQBF", sql)

It probably can't recreate as I have the query open... I remember the way I did it at first was to make a reset form, but I think I thought I was smarter then I really was and took out the reset form... lol.

I will work some more on it and reply tomorrow... thanks again

 

[Swanson1962]

Hey Norie (and all),

Well, it's starting to work out, but still problems.

When I combo the text fields the query (and subform) work

Unfortunately it doesn't work for the date or number combo lists, or the reset button (that has all CL's = null and then run the query again to show all records).

Any ideas?

Somedays I think I should just stay in bed

 

[Swanson1962]

Hi -- Well, it only took me all day Saturday and most of Sunday but I got the code right. But, I didn't use the code here, used my old code and made sure to grab the Mod that went with it...

So I got this afternoon off -- I'll post the code tomorrow for anyone who wants to use it... it's an awesome form and is perfect for anyone who has too many fields they want to search where's null from.

Thanks for helping

Luclarie

Be aware part of any process is to stumble...

 

[Swanson1962]

Hello, It's been awhile since I've been on here, and I've long since figured out the dynamic query -- part of my problem was I was not writing the code correct for the numbers, but after really thinking it through got it right. (and also added a wildcard feature).

Course now I've learned how to use the programs own filters in 2007 so this is basically obsolete but it is quite impressive when you want to create a dynamic form.

Another feature I did was to create combo lists on the form that have their own queries and are redefined as the query runs.

So you can either put two elements on your form with the code below, or take cmdrunquery and revamp to your combo lists afterupdate events. Then I add a X element and revamp the ResetQry for every combo list.

Lisa
_________________________________________________

Private Sub cmdrunquery_Click()
'Recall the wait subform...
FrmDynamicSubform.SourceObject = "FrmDynamicWait"

Dim MyDatabase As DATABASE
Dim MyQueryDef As QueryDef
Dim where As Variant
Set MyDatabase = CurrentDb()
' Delete the existing dynamic query; trap the error if the query doesn't exist...
If ObjectExists("Queries", "qryDynamic-B_QBF") = True Then
MyDatabase.QueryDefs.Delete "qryDynamic-B_QBF"
MyDatabase.QueryDefs.Refresh
End If

'Run the where is statements...
where = Null
'Store #...
where = where & (" AND [Store#]= " + Me![CLStore#].Column(0))

' Store Name with WILDCARD OPTION...
If left(Me![CLStoreName], 1) = "*" Or Right(Me![CLStoreName], 1) = "*" Then
where = where & " AND [Name] like '" + Me![CLStoreName] + "'"
Else
where = where & " AND [Name] = '" + Me![CLStoreName].Column(0) + "'"
End If

' Store Address with WILDCARD OPTION...
If left(Me![CLStoreAddress], 1) = "*" Or Right(Me![CLStoreAddress], 1) = "*" Then
where = where & " AND [Address] like '" + Me![CLStoreAddress] + "'"
Else
where = where & " AND [Address] = '" + Me![CLStoreAddress].Column(0) + "'"
End If

' City
If left(Me![CLStoreCity], 1) = "*" Or Right(Me![CLStoreCity], 1) = "*" Then
where = where & " AND [City] like '" + Me![CLStoreCity] + "'"
Else
where = where & " AND [City] = '" + Me![CLStoreCity].Column(0) + "'"
End If
' State with WILDCARD OPTION...
If left(Me![CLStoreState], 1) = "*" Or Right(Me![CLStoreState], 1) = "*" Then
where = where & " AND [State] like '" + Me![CLStoreState] + "'"
Else
where = where & " AND [State] = '" + Me![CLStoreState].Column(0) + "'"
End If
' ZIP...
where = where & " AND [Zip] = '" + Me![CLStoreZip].Column(0) + "'"
'Dates...
If Not IsNull(Me![CLEndDate].Column(0)) Then
where = where & " AND [Date] between #" + _
Me![CLBeginDate].Column(0) + "# AND #" & Me![CLEndDate].Column(0) & "#"
Else
where = where & " AND [Date] >= #" + Me![CLBeginDate].Column(0) _
+ " #"
End If

'Run the dynamic query and reset into subform...
Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic-B_QBF", _
"Select * from stores " & (" where " + Mid(where, 6) & ";"))

Me.FrmDynamicSubform.Requery
FrmDynamicSubform.SourceObject = "FrmDynamicSub"

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Me.CLBeginDate.Requery
Me.CLEndDate.Requery
Me.CLStore_.Requery
Me.CLStoreName.Requery
Me.CLStoreAddress.Requery
Me.CLStoreCity.Requery
Me.CLStoreState.Requery
Me.CLStoreZip.Requery

End Sub

_________________________________________________________

Private Sub ResetQry_Click()
'Reset all CL's to null...
Me.CLBeginDate = ""
Me.CLEndDate = ""
Me.CLStore_ = ""
Me.CLStoreName = ""
Me.CLStoreAddress = ""
Me.CLStoreCity = ""
Me.CLStoreState = ""
Me.CLStoreZip = ""

'Recall the wait subform...
FrmDynamicSubform.SourceObject = "FrmDynamicWait"

Dim MyDatabase As DATABASE
Dim MyQueryDef As QueryDef
Dim where As Variant
Set MyDatabase = CurrentDb()
' Delete the existing dynamic query; trap the error if the query doesn't exist...
If ObjectExists("Queries", "qryDynamic-B_QBF") = True Then
MyDatabase.QueryDefs.Delete "qryDynamic-B_QBF"
MyDatabase.QueryDefs.Refresh
End If

'Run the where is statements...
where = Null
'Store #...
where = where & (" AND [Store#]= " + Me![CLStore#].Column(0))

' Store Name with WILDCARD OPTION...
If left(Me![CLStoreName], 1) = "*" Or Right(Me![CLStoreName], 1) = "*" Then
where = where & " AND [Name] like '" + Me![CLStoreName] + "'"
Else
where = where & " AND [Name] = '" + Me![CLStoreName].Column(0) + "'"
End If

' Store Address with WILDCARD OPTION...
If left(Me![CLStoreAddress], 1) = "*" Or Right(Me![CLStoreAddress], 1) = "*" Then
where = where & " AND [Address] like '" + Me![CLStoreAddress] + "'"
Else
where = where & " AND [Address] = '" + Me![CLStoreAddress].Column(0) + "'"
End If

' City
If left(Me![CLStoreCity], 1) = "*" Or Right(Me![CLStoreCity], 1) = "*" Then
where = where & " AND [City] like '" + Me![CLStoreCity] + "'"
Else
where = where & " AND [City] = '" + Me![CLStoreCity].Column(0) + "'"
End If
' State with WILDCARD OPTION...
If left(Me![CLStoreState], 1) = "*" Or Right(Me![CLStoreState], 1) = "*" Then
where = where & " AND [State] like '" + Me![CLStoreState] + "'"
Else
where = where & " AND [State] = '" + Me![CLStoreState].Column(0) + "'"
End If
' ZIP...
where = where & " AND [City] = '" + Me![CLStoreCity].Column(0) + "'"
'Dates...
If Not IsNull(Me![CLEndDate].Column(0)) Then
where = where & " AND [Date] between #" + _
Me![CLBeginDate].Column(0) + "# AND #" & Me![CLEndDate].Column(0) & "#"
Else
where = where & " AND [Date] >= #" + Me![CLBeginDate].Column(0) _
+ " #"
End If

'Run the dynamic query and reset into subform...
Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic-B_QBF", _
"Select * from stores " & (" where " + Mid(where, 6) & ";"))

Me.FrmDynamicSubform.Requery
FrmDynamicSubform.SourceObject = "FrmDynamicSub"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.CLBeginDate.Requery
Me.CLEndDate.Requery
Me.CLStore_.Requery
Me.CLStoreName.Requery
Me.CLStoreAddress.Requery
Me.CLStoreCity.Requery
Me.CLStoreState.Requery
Me.CLStoreZip.Requery

End Sub