Swanson1962
New Member
- Joined
- Jan 1, 2010
- Messages
- 17
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-com
ffice:smarttags" /><st1
lace w:st="on">Main</st1
lace>] " & (" 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
![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
Dim db As Database<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><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 <st1
lace w:st="on">Main</st1
lace>] " & (" where " + Mid(Where, 6) & ";"))<o
></o
>
AFrmMWOStatisticalSubform.SourceObject = "AFrmMWOStatisticalSubform"<o
></o
>
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-com
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-com
Dim qd As QueryDef<o
Dim Where As Variant<o
<o
Set db = CurrentDb()<o
<o
On Error Resume Next<o
db.QueryDefs.Delete ("AQryDynamicQBF")<o
AFrmMWOStatisticalSubform.Requery<o
<o
On Error GoTo 0<o
<o
Where = Null<o
Where = Where & " AND [Status]= '" + Me![MWOStatusCL] + "'"<o
Where = Where & " AND [Maint Rep]= '" + Me![MaintRepCL] + "'"<o
Where = Where & " AND [Priority]= '" + Me![PriorityCL] + "'"<o
Where = Where & " AND [Location]= '" + Me![LocationCL] + "'"<o
Where = Where & " AND [Area]= '" + Me![AreaCL] + "'"<o
Where = Where & " AND [Equip]= '" + Me![EquipCL] + "'"<o
Where = Where & " AND [Requested By]= '" + Me![RequestedByCL] + "'"<o
Where = Where & " AND [Call Backs]= '" + Me![CallBackCL] + "'"<o
<o
If Not IsNull(Me![IssueEndingDate]) Then<o
Where = Where & " AND [Issue Date] between #" + _<o
Me![IssueBeginningDate] + "# AND #" & Me![IssueEndingDate] & "#"<o
Else<o
Where = Where & " AND [Issue Date] >= #" + Me![IssueBeginningDate] _<o
+ " #"<o
End If<o
<o
Set qd = db.CreateQueryDef("AQryDynamicQBF", _<o
"Select * from [A Maintenance Work Order <st1
AFrmMWOStatisticalSubform.SourceObject = "AFrmMWOStatisticalSubform"<o