SQL Variable in VBA 3

[jake7363]

Hi,
SQL is a bit new for me, so patience is appreciated.

I am trying to create a paramater query that searches eight fields for the same value. I want to do this by entering the data value just once, so the MS Query grid will not apply.

I have surmised that I need to create a variable from the value that is entered for the first field, then assigned to the other 7 fields as well. This is where I am getting stuck. How can I create the variable? Do I do this in SQL or in VBA and pass it to the SQL statement? Or is what I am asking too complicated an operation?

Thanks in advance,
Jake

 

[PHV]

WHERE [parameter value] IN (field1, field2, ..., field8)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PWise]

Great Post!

Never Thought of that to put the search value on the right side of the equation

But this gives you an <or > query can this be done with a <and > query

 

[Golom]

PHV's code is equivalent to

WHERE
     [parameter value] = field1
  OR [parameter value] = field2
  OR [parameter value] = field3
   :
  OR [parameter value] = field8

You could change the "OR" to "AND" in that expanded form.

There's also the obscure SQL

WHERE [parameter value] = ALL (field1, field2, field3, ... field8)

That requires that [parameter value] be equal to ALL the listed values (and of course, that they are equal to each other.)

 

[jake7363]

Thanks to all...works like a charm! The options will be helpful as well..

 

[CaptainD]

Thank's for that bit of info Golom, I've never seen it done that way. It should work great for a search. Gave you a star for that one...

 

[PWise]

i am trying to run this sql and this is the error that i get


SELECT Table1.*
FROM Table1 where 1= all([f1],[f2],[f3])



Syntax error (comma) in query expression '1= all([f1],[f2],[f3])'.

what am i doing wrong?

 

[PWise]

Golom
have a star

 

[ItIsHardToProgram]

FROM Table1 where [highlight]1[/highlight]= all([f1],[f2],[f3])

I find that weird.....

Try it with a string instead 'AB' for example, in a test table.

 

[PWise]

ItIsHardToProgram
tried with a string it is still no good same error
maybe all does not work in access 2003

 

[ItIsHardToProgram]

Can you try it with a stored field in a table?

 

[PHV]

I'm afraid that Golom's expression works only for subqueries[/s] and not for a list ...

 

[ItIsHardToProgram]

That requires that [parameter value] be equal to ALL the listed values (and of course, that they are equal to each other.)

Shouldve read carefully....

 

[PHV]

I talk about syntax.
I suspect that Golom's code is not only obscure but of more incorrect.

 

[ItIsHardToProgram]

I see, my bad...

 

[PWise]

i think this should do it

SELECT Table1.*
FROM Table1 where 1 =[f1]=[f2]=[f3]

 

[PHV]

Still waiting on Golom's explanation about how to avoid syntax error with it's suggestion:

WHERE [parameter value] = ALL (field1, field2, field3, ... field8)

 

[CaptainD]

Try adding "Cast" to datatypes that are numric or dates.

"SELECT Table1.* FROM Table1 WHERE 'Parameter' IN (sLName, sFName, CAST(iNumberField AS NVARCHAR(10)))

 

[PWise]

cast is for sql server not jet

 

[CaptainD]

My Bad, forgot what forum I was in. Try "CStr"

"SELECT Table1.* FROM Table1 WHERE 'Parameter' IN (sLName, sFName, CStr(iNumberField))