Date Criteria from a form's txtbox

[VegaLA]

Hi all,
I have a select query that pulls in two date values from a textbox on a form.

SELECT DISTINCT Table01.SRV_NAME, Table01.IMP_CUTOFF_DT
FROM Table02 INNER JOIN Table01 ON Table02.Servicer = Table01.SRV_NAME
WHERE (((Table01.IMP_CUTOFF_DT)=[forms]![frmreport].[txtsqldate].[value]))
ORDER BY Table01.SRV_NAME;

However when I run it I get the following error message:"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may...." but when I copy and paste the value of that txbox from the form into the query it works fine.

Can anyone see where the problem lies? I can't work it out.

Thanks in advance,
Mitch....

 

[Jon K]

Try changing the Where Clause to:-

WHERE Table01.IMP_CUTOFF_DT=[forms]![frmreport]![txtsqldate]
.

 

[VegaLA]

Thanks Jon.
Gave that a try but getting the same error message.

I really can't figure out why pulling in the value from the form is giving the qry so much grief since it works if you were to paaste in the value, which, just so you know, is : #4/30/2007# Or #5/17/2007#

Any ideas ?

Cheers,
Mitch...........

 

[Jon K]

Access treats what is typed in a text box as one single value.


Since all you need are two dates, you can add another text box on the form and change the Where Clause to reference the two text boxes separately:-

WHERE Table01.IMP_CUTOFF_DT In ([forms]![frmreport]![txtsqldate1], [forms]![frmreport]![txtsqldate2])


In the text boxes, the user does not need to type the # signs. Just type 4/30/2007 on a system using US date format, or 30/4/2007 on a UK system.

If you need to pull an indefinite number of dates, you will have to modify the SQL statement on the fly using VBA.
.

 

[Rabbie]

Is [forms]![frmreport]![txtsqldate] typed as a date field or a text field. It needs to be a DATE field

 

[VegaLA]

Thanks Jon, that worked perfectly.

Mitch...........