Access Query via VBA

[mikeymay]

I need to return some access records where a field doesn't contain two specific text strings.

I am using the following to build the query

strQuery = "SELECT InvDate, Agent, BoxRef, Supplier, Invoice FROM SageData WHERE (Supplier Not Like '*CNG*' AND Supplier Not Like '*Swalec*') AND InvDate = #" & Format(Range("Controls_ExcInvDate"), "m/d/yyy") & "#"

This executes but returns nothing and there are records in the table that meet the criteria so I'm assuming the 'Not Like' part isn't correct.

I have tried using a % instead of a * and multiple variants of using parenthesis to no avail......

Could anyone correct this for me?

TIA

 

[Fazza]

hello

I'm pretty sure for Excel VBA querying MS Access use % wildcard

I think the problem is the date format with THREE y - m/d/yyy
Likely that should be FOUR, m/d/yyyy

Anytime like this I have a problem I debug.print the SQL & review it in the immediate window. (Or you can look in the locals window.)

If you still have a problem, please debug.print & see if that reveals an issue. If still stuck, post back with the result.

cheers

 

[mikeymay]

The issue was the date format....schoolboy error!!

Thanks

 

[Fazza]

the sort of thing I'm expert at - I do errors like that every day