Invalid Date Value from Form Data

[infomania]

I am trying to filter a query using a date value from a form.
I keep getting an error: "The expression you entered has an invalid date value." and points to the WHERE criteria:
#[Forms]![fmReportSelection]![fmReportSelectionSubform].[Form]![txFlag3]#

The unbound form field (txFlag3) has a format of ShortDate and the value in the field is 7/22/2004.

When I use #7/22/2004# as the WHERE criteria, the query returns 9 rows of data (correct).

The whole SQL looks like this (used for a chart report):
SELECT
qryWoodsArrivalTime.HOUR,
Sum(qryWoodsArrivalTime.CountOfTRACKING_DATA_ID) AS NBR_TRUCKS,
qryWoodsArrivalTime.SUPPLIER_NAME,
qryWoodsArrivalTime.DATE_LOADED

FROM qryWoodsArrivalTime

GROUP BY
qryWoodsArrivalTime.HOUR,
qryWoodsArrivalTime.SUPPLIER_NAME,
qryWoodsArrivalTime.DATE_LOADED

HAVING (((qryWoodsArrivalTime.SUPPLIER_NAME)=[Forms]![fmReportSelection]![fmReportSelectionSubform].[Form]![txFlag1]) AND ((qryWoodsArrivalTime.DATE_LOADED)=#[Forms]![fmReportSelection]![fmReportSelectionSubform].[Form]![txFlag3]#));

How can I resolve this? I tried using DateValue() around the Form field but this produced no results (and no errors).

If I can resolve this I want to move on to a a Between WHERE critera to bracket two date field on the form.

Thanks, in advance.

Infomania

 

[PHV]

You may try to replace this:
#[Forms]![fmReportSelection]![fmReportSelectionSubform].[Form]![txFlag3]#
By this:
CDate([Forms]![fmReportSelection]![fmReportSelectionSubform].[Form]![txFlag3])
Or even by this:
[Forms]![fmReportSelection]![fmReportSelectionSubform].[Form]![txFlag3]

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

 

[infomania]

Both of those suggestions, while eliminating the error message, produce no results.

Is there some way I can create a string variable in the AfterUpdate event for the field and then use that in the query (as #strDate#)?

 

[infomania]

I seem to have resolved the problem by reconstructing the query such that it is based off of the table instead of an intermediate query. As such the following produces the correct result set:

SELECT
DatePart("h",[TIME_ARRIVE_WOODS]) AS [HOUR], Count(tblTRACKING_DATA.TRACKING_DATA_ID) AS NBR_TRUCKS,
SUPPLIER_NAME

FROM
tblTRACKING_DATA INNER JOIN SUPPLIERS ON SUPPLIERS.SUPPLIER_ID = tblTRACKING_DATA.SUPPLIER_ID

WHERE
(tblTRACKING_DATA.DATE_LOADED) = [Forms]![fmReportSelection]![fmReportSelectionSubform].[Form]![txFlag3])

GROUP BY DatePart("h",[TIME_ARRIVE_WOODS]), SUPPLIERS.SUPPLIER_NAME
HAVING (((SUPPLIERS.SUPPLIER_NAME)=[Forms]![fmReportSelection]![fmReportSelectionSubform].[Form]![txFlag1]));

Since the date restriction is now in the WHERE clause instead of the HAVING clause, this may have made the difference. No # literals are necessary since the field data is already properly typed.

 

[pkailas]

try cdate('strdate') in your query