Creating a query using SQL and strings in Access

[Brettrb]

I have been creating some code that will sort entries in my database based on the user input. Once the code has taken all the data in, it will need to then create a query based upon the information it has recieved. How do I create a new query in access based upon the input?

i.e. The user requests that he wants all entries in which data from [Value1] and [Value2] is less than 3. The user specifies the criteria (less than 3) and he specifies which fields he wants to sort together ([Value1] and [Value2] as opposed to him choosing [Value3] and [Value1]).

Any help would be greatly appreciated.

Brett Beckett
[email protected]

[Ludmila]

Hi,Brettrb!
May be this idea would help you. You can create temporary table and use iif function.
It makes query flexible:
SQL = "INSERT INTO TempTable(Data,Value)" & _
" SELECT iif(Data1 = Null,Data2,Data1)," & _
" iif(Value1 = Null,Value2,Value1) " & _
"FROM Table1 ORDER BY Data,Value"
If you need you can make iif more complicated:
iif(Data1 = Null,iif(Data2 = Null,Data3,Data2),Data1)

Good Luck!