Proper syntax to use a querry in a string with VBA

thandel
thandel used Ask the Experts™
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer
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 TsioumprisSoftware & Systems Engineer
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,"")
Most Valuable Expert 2015
Distinguished Expert 2018
Your query makes no sense, so it would be better if you describe in plain English what you are trying to accomplish.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
Most Valuable Expert 2015
Distinguished Expert 2018
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

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 TsioumprisSoftware & Systems Engineer
If you don't share a small sample bof your data..I am afraid we are playing the Guessing game.
Distinguished Expert 2017
I have the following query which is working in MS Access 2003:
So far, we all think that the query isn't actually working.  When you enclose a value in quotes, you are referencing a literal value.  So
IsNull("LastSub")
ALWAYS returns false because the string contains the letters L-a-s-t-S-u-b and so it can never be null.

Whereas
IsNull(LastSub)
Might return true or false depending on the contents of the column name LastSub

We are also not quite sure what you are asking for.  Are you trying to write this query as a string in VBA and don't know how to handle the embedded quotes?

Since you are using literals, you can change to embedding the double quotes as single quotes:

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

But please tell is in words what you think this query is doing.  I think the query should be:

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"

This version selects rows when the LastSub is not null OR the LastSubResult is not null.  But if you want them to BOTH be not null, you need to use AND rather than OR as the relational operator.

Author

Commented:
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.

Author

Commented:
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?
Distinguished Expert 2017
I believe that there is a button you see that I don't that lets you call a moderator to undo you acceptance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today