Date in Query criteria returning incorrect records

[dragonsel]

Hi

I am having a wonderful date problem when trying to limit records in a query.

I am trying to return all the records with a date (entered by the user) earlier than the calculated field, TargetDate

TargetDate: DateAdd("m",2,[Request rec'd])

The expression works if I hardcode the date into the criteria but I need to be able to make it dynamic - allowing the user to enter the date of their choice.

Criteria:
<=#09/01/2004# (this works and displays the correct records)

<=&quot;#&quot; & [Enter Date] & &quot;#&quot; (returns no records - the data is there)

<=[Enter Date] (returns incorrect records, each differing with different dates entered - cannot find a pattern)

SQL of entire query:
SELECT tblRequestMain.[Allocation No], tblRequestMain.[Court Ref], tblRequestMain.[Family Name], tblProceedingType.Proceedings, tblRequestMain.[Request rec'd], DateAdd(&quot;m&quot;,2,[Request rec'd]) AS TargetDate, tblRequestMain.[Initial Court], tblRequest.[Date appointed]
FROM (tblRequest RIGHT JOIN tblRequestMain ON tblRequest.[Allocation No] = tblRequestMain.[Allocation No]) INNER JOIN tblProceedingType ON tblRequestMain.Proceedings = tblProceedingType.Code
WHERE (((DateAdd(&quot;m&quot;,2,[Request rec'd]))<=#1/1/2004#) AND ((tblRequest.[Date appointed]) Is Null));

I'd appreciate any help I can get.
Thanks,
Sel

 

[jason246day]

Have you tried using the full path to the user entered date for the criteria?

<=[Forms]![your_form_name]![Enter Date]

 

[dragonsel]

I am just using a prompt box to get the date from the user not a form (i.e. in the criteria of the query <=[Enter Date]). Is there anything I am leaving out of the criteria (to prompt for the date)?

Thanks,
Sel

 

[TonyJollans]

Hi dragonsel,

Is this a date format issue? Jet SQL uses US format (mm/dd/yyyy) to resolve ambiguities in date literals. Try ..

<=Format([Enter Date],&quot;mm/dd/yyyy&quot

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[dragonsel]

For some reason the format function won't work in this instance and the query does not return any records. I don't get any error messages. Any ideas on why this would be?

Thanks
Sel

 

[TonyJollans]

Hi dragonsel,

Sorry, I should have read your SQL a bit more carefully.

This can get a bit confusing sometimes, but you must compare like with like - dates against dates, strings against strings, etc.

The DateAdd function returns a Date and so must be compared against another date, so this should work ..

DateAdd(&quot;m&quot;,2,[Request rec'd])<=CDate([Enter Date])

.. alternatively, this would convert both to strings in comparable formats and should also work ..

Format(DateAdd(&quot;m&quot;,2,[Request rec'd]),&quot;mm/dd/yyyy&quot<=Format([Enter Date],&quot;mm/dd/yyyy&quot

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.