asked on

Proper syntax to use a querry in a string with VBA

I have the following query which is working in MS Access 2003:

SELECT tLenSupplier.LSupplier, tLenSupplier.LastSub, tLenSupplier.LastSubResult FROM tLenSupplier WHERE (((tLenSupplier.LastSub)<>IsNull("LastSub"))) OR (((tLenSupplier.LastSubResult)<>IsNull("LastSubResult"))) ORDER BY tLenSupplier.LSupplier"

I would like to place this into a string to use in VBA for other purposes. I am having trouble converting the query into a string to set the variable "sSql" --> (Set rs = CurrentDb.OpenRecordset(sSql))

Any assistance is appreciated. Thank you.

John Tsioumpris

I think that this should at least would error free

SELECT tLenSupplier.LSupplier
	,tLenSupplier.LastSub
	,tLenSupplier.LastSubResult
FROM tLenSupplier
WHERE (
		tLenSupplier.LastSub <> "LastSub"
		OR (tLenSupplier.LastSubResult <> "LastSubResult")
		)
ORDER BY tLenSupplier.LSupplier

Open in new window

John Tsioumpris

EDITED...because i am trying to guess...the IsNull is applied on a field to check if its Null or Not..
Either you are matching against a word or maybe Nulls....so you need Nz like
Nz(LastSub,"")

Gustav Brock

Your query makes no sense, so it would be better if you describe in plain English what you are trying to accomplish.

thandel

ASKER

Thanks John but if I set your first solution to a string variable I get an error. I tried this:

sSql = "SELECT tLenSupplier.LSupplier, tLenSupplier.LastSub,tLenSupplier.LastSubResult FROM tLenSupplier WHERE (tLenSupplier.LastSub <> "LastSub" OR (tLenSupplier.LastSubResult <> "LastSubResult")) ORDER BY tLenSupplier.LSupplier"

That is the issue I'm having... I need to set to a string so use in a variable.

Gustav Brock

That would be:

sSql = "SELECT tLenSupplier.LSupplier, tLenSupplier.LastSub,tLenSupplier.LastSubResult FROM tLenSupplier WHERE (tLenSupplier.LastSub <> 'LastSub' OR (tLenSupplier.LastSubResult <> 'LastSubResult')) ORDER BY tLenSupplier.LSupplier"

Open in new window

Lambert Heenan

I'm with Gustav as far as making sense of your query goals.

If we just look at the first condition of the where clause...

WHERE (((tLenSupplier.LastSub)<>IsNull("LastSub")))

Open in new window

The condition is obscure, but as written it is testing to see if the literal constant "LastSub" is not null. Of course it isn't, therefore IsNull("LastSub") is a long winded way of saying FALSE, and so the condition is really

WHERE (((tLenSupplier.LastSub)<>FALSE

Open in new window

Is this your intention? Is the field LastSub in the table tLenSupplier a Boolean field?
The same questions apply to the second condition in your query.

John Tsioumpris

If you don't share a small sample bof your data..I am afraid we are playing the Guessing game.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thandel

ASKER

Thanks Gustav has it... I just needed to take an existing query that was working and change it for variable use. I always have issues with the quotes. Thank you.

thandel

ASKER

Sorry Pat I just saw you comment and you are 100% right....

The chosen solution didn't have anything for a null value and you followed what a I needed with:

sSQL = "SELECT tLenSupplier.LSupplier, tLenSupplier.LastSub, tLenSupplier.LastSubResult FROM tLenSupplier WHERE tLenSupplier.LastSub Is Not Null OR tLenSupplier.LastSubResult Is Not Null ORDER BY tLenSupplier.LSupplier"

Is there a way I can change my chosen solution?

PatHartman

I believe that there is a button you see that I don't that lets you call a moderator to undo you acceptance.