Dynamic query example question

[Autoeng]

I recreated the dynamic query example from Microsoft http://support.microsoft.com/default.aspx?scid=KB;en-us;117544& but when I click the Run Query button it displays a message box showing the SQL WHERE statement and then after I click OK it starts prompting me for the criteria via popup boxes. And I have removed the _'s in the code below per the instructions.

Can anyone think of what I have done incorrectly? I know this is vague but I don't know how else to ask the question.

Private Sub Command23_Click()
Dim db As Database
      Dim QD As QueryDef
      Dim MyWhere As Variant

      Set db = DBEngine.Workspaces(0).Databases(0)

      ' Delete the existing dynamic query; trap the error if the query does
      ' not exist.
      On Error Resume Next
      db.QueryDefs.Delete ("Dynamic_Query")
      On Error GoTo 0

      ' Note the single quotation marks surrounding the text fields _
      ' [Ship Country] and [Customer ID].
      ' Note that there are no type-casting characters surrounding the _
      ' numeric field [Employee Id].
      MyWhere = Null
      MyWhere = MyWhere & (" AND [Ship Country]= '" + _
         Me![Ship Country] + "'")
      MyWhere = MyWhere & (" AND [Customer Id]= '" + _
         Me![customer id] + "'")
      MyWhere = MyWhere & (" AND [Employee Id]= " + Me![Employee Id])

      ' The following section evaluates the Ship City criterion you enter.
      ' If the first or the last character of the criterion is the wildcard
      ' character (*), then the function uses the "LIKE" operator in the
      ' SQL statement instead of "=." Also note the single quotation
      ' marks surrounding the text field [Ship City].

      If Left(Me![Ship City], 1) = "*" Or Right(Me![Ship City], 1) = "*" Then
         MyWhere = MyWhere & (" AND [Ship City] like '" + Me![Ship City] _
         + "'")
      Else
         MyWhere = MyWhere & (" AND [Ship City] = '" + Me![Ship City] + "'")
      End If

      ' Note the number signs (#) surrounding the date field [Order Date].
      If Not IsNull(Me![order start date]) And _
            Not IsNull(Me![order end date]) Then
         MyWhere = MyWhere & (" AND [order date] between #" & _
            Me![order start date] + "# AND #" + Me![order end date] + "#")
      ElseIf IsNull(Me![order end date]) Then
         MyWhere = MyWhere & (" AND [order date] >= #" + _
            Me![order start date] + " #")
      ElseIf IsNull(Me![order start date]) Then
         MyWhere = MyWhere & (" AND [order date] <= #" + _
            Me![order end date] + " #")
      End If

      ' Remove the following MsgBox line if you do not want to display the
      ' SQL statement.

      MsgBox "Select * from orders " & (" where " + Mid(MyWhere, 6) + ";")
      Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from orders " & (" where " + Mid(MyWhere, 6) + ";"))
      DoCmd.OpenQuery "Dynamic_Query"

End Sub

Autoeng

 

[rhesus]

Did you add this code to NWIND.MDB or did you start a new file?

 

[Autoeng]

I am working in A2K and used the Northwind.mdb as the base. That probalby explains the problem as the article was written for A 2.0. Back to the drawing board.

Autoeng

 

[rhesus]

sry can't help u then

 

[Jon K]

"I am working in A2K and used the Northwind.mdb as the base. That probably explains the problem as the article was written for A 2.0."


The code is generic and should work so long as a reference is made to the DAO object library.


If a query prompts you for something with a popup input box, it means Access can't find something stated in the SQL statement. Most likely it is caused by some inconsistencies in the spelling of field names. I would suggest examining the Orders table in design view and checking the spelling of these fields used in the code:-

Ship Country
Customer Id
Employee Id
Ship City
order date

In my Access97 Northwind.mdb, these field names do not contain spaces.

Probably it was these spaces in the code.

 

[Autoeng]

I have yet to get this dynamic query to function in A2K. I would be most appreciative if someone would create a sample db for me that shows how to make this work and posts it. If in A97 that is ok.

Thank you,
Autoeng

 

[Jon K]

The attached DB contains the Orders table from the Access97 Northwind.mdb.

Its structure shows that the following fields do not contain spaces:-
Ship Country
Customer Id
Employee Id
Ship City
order date

so the spaces were removed form the code:-

MyWhere = Null
MyWhere = MyWhere & (" AND [ShipCountry]= '" + _
Me![Ship Country] + "'")
MyWhere = MyWhere & (" AND [CustomerId]= '" + _
Me![customer id] + "'")
MyWhere = MyWhere & (" AND [EmployeeId]= " + Me![Employee Id])


If Left(Me![Ship City], 1) = "*" Or Right(Me![Ship City], 1) = "*" Then
MyWhere = MyWhere & (" AND [ShipCity] like '" + Me![Ship City] _
+ "'")
Else
MyWhere = MyWhere & (" AND [ShipCity] = '" + Me![Ship City] + "'")
End If


If Not IsNull(Me![order start date]) And _
Not IsNull(Me![order end date]) Then
MyWhere = MyWhere & (" AND [orderdate] between #" & _
Me![order start date] + "# AND #" + Me![order end date] + "#")
ElseIf IsNull(Me![order end date]) Then
MyWhere = MyWhere & (" AND [orderdate] >= #" + _
Me![order start date] + " #")
ElseIf IsNull(Me![order start date]) Then
MyWhere = MyWhere & (" AND [orderdate] <= #" + _
Me![order end date] + " #")
End If

After removing the spaces, the code works.


Note.
If you open the Orders table in design view and look at the Lookup tab, you will find that in the original Northhwind.mdb the fields CustomerID and EmployeeID were made to display the Customer name and Employee name from the Customers table and the Employees table.

As the attached DB does not contain these two tables, when the Orders table is open, the actual CustomerID and EmplyeeID are displayed (although their captions are still Customer and Employee.)

 

[Autoeng]

Thank you Jon. I tried several times without success to get this working but I guess I was always missing some little thing. And you know how those little things can be. Now I've got my starting point I just have to figure out how to get this to do what I want it to do.

Happy Holidays!
Autoeng