varible concatenation-datediff possible?

[qwkslvr1999]

I found the Microsoft QBF sample and edited it. I am getting a Syntax error in one of the lines I added and wondering if it is really possible to do it. The error happens in the line that has the datediff function. I would like to be able to enter something like <=30 or >=30, etc. in the search form through an unbound text box [AGE]. Everything else works fine. What could be the problem?

Thanks in advance!

=============================
Private Sub Command4_Click()
Dim mydatabase As Database
Dim myquerydef As QueryDef
Dim where As Variant

Set mydatabase = CurrentDb()

If ObjectExists("queries", "qrydynamic_QBF") = True Then
mydatabase.QueryDefs.Delete "qrydynamic_QBF"
mydatabase.QueryDefs.Refresh
End If

where = Null
where = where & (" AND [city]= '" + Me![citysearch] + "'")
where = where & (" AND [Dx]='" + Me![Dxsearch] + "'")

ERROR >> where = where & (" AND (DateDiff("yyyy",[DOB],Now()))" + Me![Age])


Set myquerydef = mydatabase.CreateQueryDef("qrydynamic_QBF", "SELECT TABLE1.*, TABLE2.* FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "qrydynamic_QBF"


End Sub
=============================


[This message has been edited by qwkslvr1999 (edited 06-07-2002).]

 

[Fizzio]

The line

ERROR >> where = where & (" AND (DateDiff("yyyy",[DOB],Now()))" + Me![Age])

does not make sense. You need to either create a calculated Age field or easier filter by DOB. To use arithmetic operators, I feel you will need to use a separate combo to select the operator (but you will need a 2 column combo with the opposite operator in each column) and a textbox to select the age So therefore the line would look something like

where = where & (" AND [DOB] " & Me!txtOperator & "#" & DateAdd("y",-Me![Age],Date()) "#")

The reason you need reverse operators in your combo is because if you want Age <30 then DOB >1972. Show the correct operator in Column1 but have the correct operator in column0.
I'm not sure if you will need '' around the operator reference (or in fact if this will work! - but heck it's worth a try

)


[This message has been edited by Fizzio (edited 06-10-2002).]