Problems mixing numeric fields and text fields in Query by Form search method

[Sharky II]

Hi lads. I reckon this might have come up before - despite my search efforts.

Everything was going swimmingly using the query by form method taken fromt eh MS support pages - a link that jack cowley kindly gave me. if i want to search using text boxes everything is fine - i created drop downs etc. However when i want to search for customerID (and after i've done that i'll want to search by telephone + fax +mobile number and DOB etc) i get hassles. First of all, here's my code:

Private Sub cmdRunQuery_Click()
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
where = where & " AND [CustomerID]= " & Me![SrchCustomerID]
where = where & " AND [FirstName]= '" + Me![srchFirst] + "'"
where = where & " AND [LastName]= '" + Me![SrchLast] + "'"
where = where & " AND [Address]= '" + Me![SrchAddress] + "'"
where = where & " AND [Postcode]= '" + Me![SrchPostcode] + "'"
where = where & " AND [Country]= '" + Me![SrchCountry] + "'"

MsgBox "Select * from Customer " & (" where " + Mid(where, 6) & ";")
Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from Customer " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
DoCmd.OpenForm "frmDynamicQuery"
End Sub

I don't care to have the msg box come up but for now it allows me to see whats happening. I don't really understand the code that well to be hoenst - i don't know what the 6 and stuf is for - i'm really just copying code and using it - which was working up until now. On the MS site it said to use where = where & (" AND [CustomerID]= " + Me![SrchCustomerID]) but this didn't work. I had to use where = where & " AND [CustomerID]= " & Me![SrchCustomerID]

Anyway, when i run the search form with NOTHING in the fields i get Select * from Customer where [CustomerID]=;. So it's searching for a customerID of "" instead of ignoring it. Wierd? Then if i click ok i get the following error: Run Time error '3705' - Syntax error (missing operator) in query expression '[CustomerID]='.. When i go to debug the following lines are highlighted:

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

Any idea how i can sort this out? Everything works fine when i use text boxes. I want to go and add loads of other fields in my search form, is there anything i will have to add to the code to do this or can i just keep going and adding fields to the part where it has:

where = Null
...
where = where & " AND [Country]= '" + Me![SrchCountry] + "'"....etc

and i can then add where = where & " AND [sex]= " & Me![sexCustomerID] and then telephone number, and then passport number, etc etc etc etc etc.

Any help appreciated.

Cheers

 

[Sharky II]

oh and what on earth does the 6 stand for or represent?

 

[Sharky II]

forgot to add: on the form if i set the customerID to a certain number (ie if i search for a customer with customerID of 44 or whatever) then the correct results come up fine.

it's only when i leave the customerID field blank that i get problems. if i ONLY wanna search for customers with lastname 'smith' (so i leave all fields blank apart from 'last name' i choose 'smith') i STILL get this query coming up in the msgbox: Select * from Customer where [CustomerID]= AND [LastName]='smith';

also if i leave the whole form blank and search, instead of searching for nothing it searches for 'customerID=;'

This is wrong.

cheers

 

[Jack Cowley]

Modify the customerID where clause like this:

If Not IsNull(Me.CustomerID) Then
where = where & " AND [CustomerID]= " & Me![SrchCustomerID]
End if

The '6' in the code is to remove the last AND from the SQL string...

 

[NachoMan]

Good man.

Thanks again, Jack. This same problem was taking up way too much of my Saturday. (I did know what the "6" was though.)