VBA syntax for Query

[Dizzzy44]

Hi guys

i am having trouble with the syntax of the VBA for this a query.

The first part works but the second doesn't. Any ideas?

strWhere1 = " WHERE [Product Description] in (" & Left(strIN, Len(strIN) - 1) & ")"

strWhere2 = " AND ([Location] Like IIf('" & [Forms]![frmDataEntry]![fltrLocation] & "'=<All>, * ,'" & [Forms]![frmDataEntry]![fltrLocation] & "'))"

Cheers

 

[namliam]

What doesnt work about the second part?

Looking at the IIF, I think your trying to do something the hardway instead of the easy way as well as the wrong way....

Instead of what you have ... try:

If [Forms]![frmDataEntry]![fltrLocation] <> "<All>" then
    strWhere2 = " AND ([Location] = '" & [Forms]![frmDataEntry]![fltrLocation] & "' "
end if

 

[tyronedjackson]

I'm new so please bare with my explanation. If it doesn't make sense, let me know and I will explain again differently.

I would first try this....
strWhere2 = " AND ([Location] Like '" & IIf([Forms]![frmDataEntry]![fltrLocation]= "<All>", "*" ,[Forms]![frmDataEntry]![fltrLocation] & "'))"

I think the IIF statement is best to evaluate outside the variable.

But I have another suggestion, if you can tell me how you set up your combobox? If the combobox is just a simple list of values, then make two columns, assigning the value "*" to the first column in the <All> row. All other rows should have identical values in both columns. If the combobox is based on a two column union query to include <All>, then simple add "*" to the first column for the dependent value for <All>.

Then, for your variable....

strWhere2 = " AND ([Location] Like '" & [Forms]![frmDataEntry]![fltrLocation] & "')"