Querydefs question

[millers92]

Hello all! I am trying to setup a basic search parameters and I am new at this.

I read the article about how to use Querydefs and am trying to follow it as closely as possible. I gotten to a point where Access keeps returning the following message:

Run-Time Error 2001: You cancelled the previous operation.

Here is the code that I am running:


Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete "Dynamic_Query"
On Error GoTo 0

where = Null

If IsNull(Drop_Point_s_) And IsNull(Text2) Then
MsgBox "You forgot to enter search parameter(s)", vbOKOnly
End If

If Drop_Point_s_ <> "" And Text2 <> "" Then
where = "[Dispatch Date]= '" & Text2.Text & "'"
where = where & " AND [Drop point(s)]= '" & Drop_Point_s_ & "'"
End If

If IsNull(Drop_Point_s_) Then
where = "[Forecast]![Dispatch Date]= '" & Text2 & "'"
End If

If IsNull(Text2) Then
where = "[Drop point(s)]= '" & Drop_Point_s_.Text & "'"
End If

MsgBox "Select * from Forecast where " & where

Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from Forecast where " & where & ";")
DoCmd.OpenQuery "Dynamic_Query"


I'm sure that I am missing something, but I can't seem to pinpoint what is going on. Any help would be greatly appreciated!

Thanks!

Steve

 

[Jon K]

I assume the article that you read was about dynamic queries. For dynamic queries, you don't need to check for the existence of parameter values as the user can leave the parameters blank. (That is why the parameter string in your code is declared as variant and initialised as Null.)
As an illustration, I have attached a small DB. You can open the form and click on the command button to view the records in table Forecast with or without entering the DispatchDate and DropPoint parameters.

The code is contained in the On Click event of the command button. Note the uses of & and + in the concatenation of the text string for the DispatchDate and DropPoint parameters:-
----------------------------------
Private Sub cmdViewRecords_Click()

Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim SQL As String
Dim vWhere As Variant

Set db = CurrentDb()

' delete query "Dynamic_Query" if it already exists.
On Error Resume Next
db.QueryDefs.Delete "Dynamic_Query"
On Error GoTo 0

' initialise vWhere as Null.
vWhere = Null

' surround date with # signs.
vWhere = vWhere & (" and [DispatchDate]=#" + Me.txtDispatchDate + "#")

' surround text with single quotes.
vWhere = vWhere & (" and [DropPoint]='" + Me.txtDropPoint + "'")

' use Mid(vWhere,5) to remove the beginning " and " from vWhere.
SQL = "SELECT * FROM [Forecast] " & (" WHERE " + Mid(vWhere, 5))

' create new query "Dynamic_Query".
Set QD = db.CreateQueryDef("Dynamic_Query", SQL)

' run the new query.
DoCmd.OpenQuery "Dynamic_Query"

End Sub
----------------------------------

Hope it helps.

The code was written in Access 97, so DAO was used. If you write the code in Access 2000 or 2002, you must make a reference to DAO (when the code window is open, choose menu Tools, References... and select the Microsoft DAO 3.6 Object Library.)


Note: To open the attached DB in Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.

 

[millers92]

Thanks Jon! This helped immensely! I ended up having to use IfNull statements still, but your example helped me get my head on straight. If you don't mind, could you take a look at my code here and see if you see any problems or have any suggestions?

Private Sub Command48_Click()
Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim SQL As String
Dim vWhere As Variant

Set db = CurrentDb()

' delete query "Dynamic_Query" if it already exists.
On Error Resume Next
db.QueryDefs.Delete "Dynamic_Query"
On Error GoTo 0

' initialize vWhere as Null.
vWhere = Null

If IsNull(Me.StartDispatchDate) And IsNull(Me.EndDispatchDate) Then
' surround text with single quotes.
vWhere = vWhere & (" and [Drop Point(s)]='" + Me.Drop_Point_s_ + "'")
End If

If IsNull(Me.StartDispatchDate) = False And IsNull(Me.EndDispatchDate) = True Then
vWhere = vWhere & (" and [Drop Point(s)]='" + Me.Drop_Point_s_ + "'")
vWhere = vWhere & (" and [Dispatch Date]=#" & Me.StartDispatchDate & "#")
End If

If IsNull(Me.StartDispatchDate) = False And IsNull(Me.EndDispatchDate) = False Then
' surround date with # signs.
vWhere = vWhere & (" and [Drop Point(s)]='" + Me.Drop_Point_s_ + "'")
vWhere = vWhere & (" and [Dispatch Date] between #" & Me.StartDispatchDate & "# AND #" & Me.EndDispatchDate & "#")
End If

' use Mid(vWhere,5) to remove the beginning " and " from vWhere.
SQL = "SELECT [Drop Point(s)], [Dispatch Date], [Carrier], [Trailer Number], [Closed?], [Time Closed], [Indoor Check], [Time Indoor], [BCCheck], [BC Paperwork Done], [TrailerLoaded Check], [Trailer Loaded Time], [BOLCheck], [BOL Print Time], [CarrierCheck], [Carrier Pickup Time] FROM [Forecast] " & (" WHERE " + Mid(vWhere, 5))

' create new query "Dynamic_Query".
Set QD = db.CreateQueryDef("Dynamic_Query", SQL)

' run the new query.
DoCmd.OpenQuery "Dynamic_Query"

End Sub



Thanks!

Steve

 

[Jon K]

Since you deal with the Drop Points simultaneously with the Dispatch Dates in each IF...EndIF, and you have left out the case where:-
IsNull(StartDispatchDate) = True AND IsNull(EndDispatchDate)= False

the Drop Points parameter will not take effect when StartDispatchDate is null and EndDispatchDate is not null.


You can deal with the Drop Points and Dispatch Dates separately. And use an If...ElseIF...EndIf for the Dispatch Dates:-
----------------------------------

' initialize vWhere as Null.
vWhere = Null

' Drop Points
vWhere = vWhere & (" and [Drop Point(s)]='" + Me.Drop_Point_s_ + "'")

' Dispatch Dates
If IsNull(Me.StartDispatchDate) = False And IsNull(Me.EndDispatchDate) = True Then
vWhere = vWhere & (" and [Dispatch Date]=#" & Me.StartDispatchDate & "#")
ElseIf IsNull(Me.StartDispatchDate) = False And IsNull(Me.EndDispatchDate) = False Then
vWhere = vWhere & (" and [Dispatch Date] between #" & Me.StartDispatchDate & "# AND #" & Me.EndDispatchDate & "#")
End If

' use Mid(vWhere,5) to remove the beginning " and " from vWhere.
SQL = "SELECT [Drop Point(s)], [Dispatch Date], [Carrier], [Trailer Number], [Closed?], [Time Closed], [Indoor Check], [Time Indoor], [BCCheck], [BC Paperwork Done], [TrailerLoaded Check], [Trailer Loaded Time], [BOLCheck], [BOL Print Time], [CarrierCheck], [Carrier Pickup Time] FROM [Forecast] " & (" WHERE " + Mid(vWhere, 5))

----------------------------------

Notes:
If you want to, you can deal the case where StartDispatchDate is null and EndDispatchDate is not null with an additional ElseIF. You may also consider using the operator >= or <= in the cases where only one of the dispatch dates is entered.

Avoid using spaces and special characters in field names, table names and control names. Sometimes these characters may cause trouble when you use the names in VBA code.

 

[millers92]

Thanks Jon! I appreciate all of the guidance! Hopefully I won't run into too many more snares...

Steve