Dynamic Queries - Conditions

[Guest]

Hi, I have a query which has among others; NetBiosName, SerialNum and
ServiceTags columns.

From a form - I want the user to be able to chose Netbios, SerialNum or
ServiceTag from a combo box, type in a value, and when they click "search"
have the query execute the select criteria for that field.

So if I chose "NetBios" then the query executes the Like [Forms]![.... in
the NetBios column corresponding to the value in my custom combo box and so
forth.

The form displaying the Hardware info can display the results from any of
the different selects.

I hope that's legible - the question was a lot harder to put down on paper
than I had thought it would be.

 

[Michel Walsh]

Hi,


Not very fast, probably, try to index all the three fields, but (within
Access , won't work in VB, in C++, in ASP, etc):



SELECT *
FROM myTable
WHERE
SWITCH( FORMS!myFormName!MyCombo = 'netbios' ,
Netbios=FORMS!myFormName!TextBox1 ,
FORMS!myFormName!MyCombo = 'Serialnum' ,
SerialNum=FORMS!myFormName!TextBox1 ,
FORMS!myFormName!MyCombo = 'tag',
tag=FORMS!myFormName!TextBox1 )




Hoping it may help,
Vanderghast, Access MVP