WHERE query help required

[prodtest]

Hi,
I have a subform which is populated using the SQL below,

SELECT data.customer, data.importairwaybill, data.shipmentid, data.customsimportno, data.importdate, data.exportairwaybill, data.date, data.customsexportno FROM data WHERE customer Like '*' & Text4 & '*'; #

thing is I would like to be able to change the "customer" bit in the "FROM data WHERE customer Like". I would like this to be populated by the result of a Combo box on the main form. If I enter what I would have expected Combo2 everytime I open the form it asks for the value Combo2.

Any ideas where I am going wrong ??

Cheers all in advance

Ben

 

[Golom]

You can enter values as parameters but not field names, table names, SQL predicates or Operators. If you want to do this then you need to build the SQL in code and then save it as a query.

 

[prodtest]

Hi,
Thanks for replying, I think I understnad what you mean. I have written the code below:

Dim strsql As String

strsql = "SELECT data.customer, data.importairwaybill, data.shipmentid, data.customsimportno, data.importdate, data.exportairwaybill, data.date, data.customsexportno FROM data WHERE " & Combo2 & " Like '*' & Text4 & '*';"

Is this the sort of thing you mean ? Adn how would I store it as a query ?

many thanks
Ben

 

[Golom]

If you want to replace an existing query then

CurrentDb.QueryDefs("myQuery").SQL = strsql

or if you need to build a new one

CurrentDb.CreateQueryDef("myQuery",strsql)