VBA access query using AND & Or

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,407
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
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,833
Office Version
365
Platform
Windows
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.
Code:
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

Well-known Member
Joined
Dec 3, 2007
Messages
986
I expect so, but I think you have two " characters too many and your codeline should look like this:
Code:
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") & "'"
 
Last edited:

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,407
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,833
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,086,065
Messages
5,387,585
Members
402,070
Latest member
hyperf0

Some videos you may like

This Week's Hot Topics

Top