Is it possible to use variables in sql?

[Premy]

Hi there, is it at all possible to use variables in sql? Please consider the following snippet:

... (WHERE [DateDue] < Date())...

The idea is to return a recordset where the DateDue is prior to the current date. But sometimes I need the opposite, ie a recordset where DateDue is in the future: ... (WHERE [DateDue] > Date())...

Right now I have 2 long queries which are exactly the same, except for the operator < for the first case above and the operator > for the second.
I of course did not want to repeat the queries so I tried:

Dim op As String
op = Me.txtOp 'txtOp is invisible and gets its value (either "<" or ">" ) thru
'a button click
... (WHERE [DateDue] & op & Date())...

I also tried:

... (WHERE [DateDue] & forms!frmInvoices!txtOp & Date())...

But to my "surprise" both failed.

Can it work? what should I correct or do differently here? I might need this kind of trick for other routines too, so I'd like to avoid copying the same query, just for the operator change.
I hope one of you seasoned sqlers can tell me more about it, I appreciate.

Regards,
Jaime

 

[The_Doc_Man]

Let's address one thing at a time.

Asking if a given date is earlier than (<) Date() is perfectly valid. You may indeed use a function in that way. Ditto asking if the given date is later than (>) Date(). This is not a case of a variable, though. It is a library function.

The catch for using a true variable in SQL is trickier because SQL doesn't know anything about modules except entry points, and then only if they are global and in a general module as opposed to a class module (form/report).

You can search this forum for "Variables in SQL" if that's really what you wanted.

OK, next question - SQL and a variable operator "<" or ">" - this is tricky only because of the need to build the SQL "on the fly" and get the quotes right.

You cannot do that substitution as you showed it except in one way. Build the string as a string. Perform the substution. Then run the SQL implied by the string. I would (myself) build the string in two parts split by the operator you wanted to insert.

Then,

stSQL = leftpart$ & chosen-operator & rightpart$
Then execute the SQL string in a separate step after you have built it.

 

[EMP]

... (WHERE [DateDue] & forms!frmInvoices!txtOp & Date())...

You can use the Eval() function in the Where Clause of the query:-

... WHERE Eval(CDbl([DateDue]) & forms!frmInvoices!txtOp & CDbl(Date()))


The CDbl() function converts [DateDue] and Date() to two numbers for evaluation. So Access doesn't have to evaluate something like 14/4/2007 > 14/5/2007 and return erroneous results.

^

 

[Premy]

Ok I followed Doc Man's advice to search the forum and I found the answer in t122314 by Bob Larson. It seems that in order to use a variable in sql you just need to wrap it up in double quotes:

Dim op As String
op = Me.txtOp 'txtOp is invisible and gets its value (either "<" or ">" ) thru
'a button click
... (WHERE [DateDue] "& op &" Date())...

That's what I wanted, just to be able to use variables, whatever the data types may (ie whether the comparison involves dates or not).

Say EMP, are u saying that the following code would result in an error:
... (WHERE [DateDue] > Date())... ?

For it doesn't. I've been using exactly that syntax and it worked fine. My problem was when I would like to change > to < or vice versa. Since I didn't know the syntax for representing < and > by a variable I was using 2 queries: 1 with < and the other with >. But all that is solved now, I can use only one query and feed < or > to it thru a variable.

Much thanks guys

regards,
jaime

 

[Jon K]

Originally posted by EMP
You can use the Eval() function in the Where Clause of the query:-

... WHERE Eval(CDbl([DateDue]) & forms!frmInvoices!txtOp & CDbl(Date()))


The CDbl() function converts [DateDue] and Date() to two numbers for evaluation. So Access doesn't have to evaluate something like 14/4/2007 > 14/5/2007 and return erroneous results.

^

This is the Query forum and the hint of referencing a form control with forms!frmInvoices!txtOp must have led EMP to think that it was a query issue and offer a query solution. In a query using Eval() on dates, we do need the CDbl().

In VBA building an SQL statement on the fly, the variable needs to be put outside the double-quotes:-

" WHERE [DateDue] " & op & " Date()"
.

 

[The_Doc_Man]

Jon - you need the double quotes only where literals are involved. My suggestion should have been taken as { variable-holding-left-part & op & variable-holding-right-part } for which no quotes are required. But if that was not clear, then the above is what I was thinking.

 

[Premy]

Thank u Jon K, I'll keep that in mind when building queries in the query builder.

As for your suggestion Doc Man, it was clear, it just seems that doing it directly with a variable is easier, in this case.

Thanks again guys

Regards,
Jaime

 

[Jon K]

Doc,

When I wrote: "In VBA building an SQL statement on the fly, the variable needs to be put outside the double-quotes:-
" WHERE [DateDue] " & op & " Date()"

I was referring to Jaime's
... (WHERE [DateDue] "& op &" Date())...

It seems that in order to use a variable in sql you just need to wrap it up in double quotes:

... (WHERE [DateDue] "& op &" Date())...

.

 

[Premy]

Ok, mea culpa, I now understand that I didn't actually wrap the variable in double quotes:

... (WHERE [DateDue] "& op &" Date())...

What I really did was seperating the SQL string in 2 SQL strings with the variable in between:

"... (WHERE [DateDue]" &op& "Date())..."

Am I right (at last)?

regards,
Jaime

 

[Dennisk]

this digresses but it would be possible to use a vba variable in an sql staement by using a function (which may be used in an expression) to retrieve the value of the variable.

eg

private intvar as integer

public sub SetVar(varValue as integer)
intvar = varValue
end sub
public function GetVar() as integer
GetVar = intVar
end function

and your SQL would look like this.

SELECT * from myTable WHERE myTable.Counter < GetVar()

 

[Premy]

Ok Dennisk thanks for the hint, it might come handy along the way I'm sure.

Regards,
Jaime