Have date from form as query criteria

[Padraigini]

Hi
Was just wondering how I would use a Date variable declared in my vba code
on a form as the criteria in query.
What is the synax is what i mean. I can only seem to find it for text box
values this is 2 variable StartDate and EndDate that get their vaues from 6
combo boxes
(3 & 3).
Thanks in advance.

 

[Allen Browne]

The query cannot use VBA variables directly.

You will need to place a (hidden?) text box on the form, and assign the
value of the variable to the text box.

If you are using Access 2007, you may be able to use TempVars. The query can
read them in this version.

 

[Jim Burke in Novi]

In addition to what Allen said, it's been my experience that you need to use
the Eval function in the query criteria, so for example a text box called
txtDate on form frmMain your criteria would be Eval("Forms!frmMain!txtDate").
You need the quotes around the control name.

 

[Douglas J. Steele]

Another option is to create a public function that returns the value of the
variable, and use the function in the query.

 

[Klatuu]

I have never had to use the Eval function to refer to the value of a text box
on a form in a query criteria.
Under what circumstances did you find that necessary?

 

[Jim Burke in Novi]

Offhand I don't remember, but I know there are times where it is necessary. I
thought dates was one of them, but maybe I'm wrong about that. It may be with
combobox values only?? I've had enough problems with it where I just got into
the habit of always using Eval.

 

[Klatuu]

Interesting. with dates, they need to be delimited with #
The only other thing I can think of would be trying to use a column other
than the bound column of a combo. A query can't understand that, but I have
never tried that so I don't know if an Eval would resolve it or not.

Just curious.

 

[Jim Burke in Novi]

When I use textbox dates in criteria in a 'stored' query, I use Eval, and
there is no need for #. If I specify dates in an SQL string in VBA code, then
I include the # around the date value, e.g.

"WHERE tbl1.myDate = #" & txtDate & "#"

As for the combobox thing, you may be right. I know I sometimes use a column
from a combobox that is not the bound column in criteria, so maybe that's the
only instance where you need Eval with a combo box. But query cirtieria with
form controls has caused me enough problems to the point where I always use
Eval, and it works every time. Maybe not quite as efficient, but I know it
works!

 

[Klatuu]

Not doubting it will work, just never had a case where I need it.