Hi, I'm trying to code some dynamic queries for a database I'm doing and have come across some problems...

Firstly, I don't know Visual Basic so I'm basically teaching myself from Microsofts Knowledge base.

Here's a code for one of my dynamic queries.

--------START CODE----------

Private Sub cmdRunQuery_Click()

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

Set db = CurrentDb()

' 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

where = Null

where = where & " AND [PurchaseID]= " + Me![PurchaseID]

'NOTE: In Microsoft Access 97, when you use the plus sign (+) in an
'expression in which you are concatenating a variable of the numeric
'data type, you must use parenthesis around the syntax, as in the
'following example:
'
' where = where & (" AND [EmployeeID]= " + Me![Employee Id])
'
'You must also use a conversion function to make sure that the proper
'conversion (to either NULL or String) takes place.


Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from products " & (" where " + Mid(where, 6) & ";"))

DoCmd.OpenQuery "Dynamic_Query"

End Sub

---------END CODE----------

The line that gives me a 'type mismatch' error is:

where = where & " AND [PurchaseID]= " + Me![PurchaseID]

The field in my table is called 'Purchase ID', but everytime I try to put that in where the second PurchaseID is, I get an error....

Help me please http://www.access-programmers.co.uk/ubb/smile.gif

Try changing your plus sign (+) to an ampersand (&):

Instead of:

where = where & " AND [PurchaseID]= " + Me![PurchaseID]

Use this:

where = where & " AND [PurchaseID]= " & Me![PurchaseID]