VBA access query using AND & Or

[mikeymay]

I am wanting to get some data from an Access table based on multiple criteria and I'm not sure if my criteria is working and I can't seem to find anything that would let me verify it.

the query as it stands is

strQuery = "SELECT Name, Wage, Pre_App_Adds, Food, Home_Start, Other, Mileage_Pay, Holiday_Pay, Gross_Pay, ID " & _
"FROM Data WHERE (Pay_Pack=''"" OR isnull(pay_pack)) AND Exp_Month = '" & Format(Range("UL_AccrualsMonth"), "mmm yy") & "'"

As you can see I want ot chek if Pay_Pack is either null or blank AND Exp_Month equals the text string of the date in the referred to cell.

Can anyone confirm if this is the correct way to use AND & OR?


TIA

 

[Joe4]

The way I usually do these, is to manually do an example of exactly what I want in Access. Once I get that working, I switch the query to "SQL View", and copy and paste this code to a Word document that I used as my guide. This is the query that I want to build using the Excel VBA code.

Then, build it in VBA, like you have done. Then add a MsgBox to return the value of what you just built, i.e.

MsgBox strQuery

and compare it to what you copied out of Access.

Keep tweaking it until you get it to look the same as what you copied out of Access. When you successfully do that, you should have the code that you need.

 

[jkpieterse]

I expect so, but I think you have two " characters too many and your codeline should look like this:

strQuery = "SELECT Name, Wage, Pre_App_Adds, Food, Home_Start, Other, Mileage_Pay, Holiday_Pay, Gross_Pay, ID " & _
"FROM Data WHERE (Pay_Pack='' OR isnull(pay_pack)) AND Exp_Month = '" & Format(Range("UL_AccrualsMonth"), "mmm yy") & "'"

 

[mikeymay]

Thanks Joe

Unfortunately my experience of building queries in Access is pretty poor and I've learnt how to do a query in Excel/VBA the wrong way round. I have tried may times to master the art of Access and just never done it enough times for it to stick.

 

[Joe4]

It is hard to build something when you don't know what it is you are building!

If you really have no idea, I would want to see what your data looks like, and an explanation from you of what the exact criteria is (I do not like to try to figure/assume what it is from faulty code).
Perhaps Jan "guessed" right.