1

I seem to be having issues with MS Access in the following query based on a SAGE database

The following code does not work - it doesn't like the "NOT like" part

SELECT tran_number, Inv_ref, Details, date, Net_amount, amount_paid
FROM AUDIT_HEADER

WHERE type = 'BP' and 
deleted_flag= 0 and details NOT like "*WAGES*"

This code does work but is very slow and doesn't work when I link it to excel -

parameters expected = 1 error??

SELECT tran_number, Inv_ref, Details, date, Net_amount, amount_paid
FROM AUDIT_HEADER

WHERE type = 'BP' AND deleted_flag= 0 and 
details NOT IN (SELECT details from audit_header where details like "*WAGES*");

Also, bizarrely, this code works, but obviously, I want to exclude transactions that include WAGES in the details

SELECT tran_number, Inv_ref, Details, date, Net_amount, amount_paid
FROM AUDIT_HEADER

WHERE type = 'BP' and 
deleted_flag= 0 and details like "*WAGES*"
kcrisman
  • 4,374
  • 20
  • 41
Owen Partridge
  • 63
  • 2
  • 9

3 Answers3

1

Since this works:

SELECT tran_number, Inv_ref, Details, date, Net_amount,
amount_paid FROM AUDIT_HEADER
WHERE type = 'BP' and 
deleted_flag= 0 and details like "*WAGES*"

While above does have a mix of single and double quotes (hint: in the future when trying to fix something with computers – being consistent is VERY important).

The only REAL issue that jumps out in above is that date is reserved word in Access. Details is also. Thus place [] brackets around the above reserved words

So you MUST MUST MUST verify that the follow as you claim works:

SELECT tran_number, Inv_ref, Details, date, Net_amount,
amount_paid FROM AUDIT_HEADER
WHERE type = "BP" and 
deleted_flag= 0 and details like "*WAGES*"

Now try above with the reserved words in brackets like this:

SELECT tran_number, Inv_ref, Details, [date], Net_amount,
amount_paid FROM AUDIT_HEADER
WHERE type = "BP" and 
deleted_flag= 0 and [details] like "*WAGES*"

So note the [] around date and details.

Again, test the second above example. If the above works, and you DOUBLE, AND TRIPLE checked, then this should also work:

SELECT tran_number, Inv_ref, Details, [date], Net_amount,
amount_paid FROM AUDIT_HEADER
WHERE type = "BP" and 
deleted_flag= 0 and [details] NOT like "*WAGES*"

if above fails, then a good possibility that the SAGE system does not support “NOT” for the like command.

So try the [] on the WORKING query. If the [] works, then try adding the NOT to the working query.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
0

The user also gets an error:

ODBC Call Failed - Syntax - Invalid filter in WHERE clause (#0)

If you're using an ODBC call and calling a non-Access DB from Access you need to check your SQL is valid for whichever DB you are calling. So if you were using SQL server you would use:

SELECT tran_number, Inv_ref, Details, date, Net_amount, amount_paid
FROM AUDIT_HEADER WHERE type = 'BP' and deleted_flag= 0 and details NOT LIKE '%WAGES%'

As SQL uses single quotes and not double quotes. This quotes point might be worth checking as you have used single quotes around 'BP' but double around "*WAGE*". In addition, the wild card is a percentage symbol. You might want to check this against whichever DB you are using.

JCollerton
  • 3,227
  • 2
  • 20
  • 25
  • This doesn't work, it returns ODBC Call Failed - Syntax - Invalid filter in WHERE clause (#0) – Owen Partridge Dec 04 '15 at 10:15
  • Hi Owen, check this new answer. – JCollerton Dec 04 '15 at 12:24
  • Access ODBC is the same regardless of the server side or data source. You ONLY have to change the SQL if your using a pass-though. So the above wild cards don't change if your using SQL server or SAGE or whatever as the back end. And Access works just fine if you use double quotes. – Albert D. Kallal Dec 04 '15 at 17:13
  • It was just a suggestion as he was using an ODBC connection (like a pass-through) and that could have been the source of his problem. – JCollerton Dec 04 '15 at 17:18
0
and NOT (details like "*WAGES*")

As a side note wildcards at the start of strings is going to be slow.

Hogan
  • 69,564
  • 10
  • 76
  • 117