Enter Date Parameter not working

[SailorJerry7030]

Good Morning,

I've got a database where I run a Select Query every morning that requires me to enter in today's date. It worked fine last week and last month, but as of today I get the following error:

[FONT=&quot]"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

I haven't changed anything other than we are now in the double digit days opposed to single digit, but this all worked fine last month.

Any ideas? Or what information would you need from me[/FONT]

 

[Joe4]

Can you post the SQL code of your query so we can see it?
It would also be a beneficial if you code post a small example of your data, so we can see what the data your query is accessing looks like.

 

[SailorJerry7030]

I may have stumbled across the issue where it's not directly related to the Enter Date Parameter. In a previous version of the database I have a field that is "DUE DATE". The format however comes in as "43245", "43376", "43255", etc. This was changed to "DUE DATE 1: CDate([DUE DATE])". The weird thing is, if I enter 6/8/2018 in the Enter Date Parameter box, it works fine and converts the date. If I enter the current date, 6/11/2018, I get that error. If I change the field back to "DUE DATE" and link it to the table, the query runs fine but still has the 5 character format rather than a date.

Let me know if that makes any sense! And here's the SQL with the current non working version, it's a bit messy

PARAMETERS [Enter Date] DateTime;
SELECT [LA Data Import].[TEAM NAME], [LA Data Import].[TYPE OF WORK], [LA Data Import].[UNIT CODE], [LA Data Import].[WORK TYPE], [LA Data Import].[STATUS CODE], [LA Data Import].[CREATE DATE TIME], [Life RE].[AvgOfWeighted RE] AS [Weighted RE], DateDiff("d",[DUE DATE],[TODAYS DATE]) AS Age, [Life RE]![AvgOfWeighted RE]/60 AS [Hours Req], CDate([DUE DATE]) AS [DUE DATE 1], [LA Data Import].[TODAYS DATE], [LA Data Import].[CREATE DATE], [LA Data Import].[DUE DAYS], [LA Data Import].[DUE DAYS FOR SORTER], 1 AS [Count], [LA Data Import].[POLICY NUMBER], [LA Data Import].[CASE TRANSACTION EXTRACT DATE], [LA Data Import].[RECEIVED DATE], [LA Data Import].[QUEUE CODE], [LA Data Import].[SORTER NAME], [LA Data Import].[INSURED LAST NAME]
FROM [Life RE] RIGHT JOIN [LA Data Import] ON ([Life RE].[Status 1] = [LA Data Import].[STATUS CODE]) AND ([Life RE].[Work Type] = [LA Data Import].[WORK TYPE]) AND ([Life RE].[Business Are] = [LA Data Import].[UNIT CODE])
GROUP BY [LA Data Import].[TEAM NAME], [LA Data Import].[TYPE OF WORK], [LA Data Import].[UNIT CODE], [LA Data Import].[WORK TYPE], [LA Data Import].[STATUS CODE], [LA Data Import].[CREATE DATE TIME], [Life RE].[AvgOfWeighted RE], DateDiff("d",[DUE DATE],[TODAYS DATE]), [Life RE]![AvgOfWeighted RE]/60, CDate([DUE DATE]), [LA Data Import].[TODAYS DATE], [LA Data Import].[CREATE DATE], [LA Data Import].[DUE DAYS], [LA Data Import].[DUE DAYS FOR SORTER], 1, [LA Data Import].[POLICY NUMBER], [LA Data Import].[CASE TRANSACTION EXTRACT DATE], [LA Data Import].[RECEIVED DATE], [LA Data Import].[QUEUE CODE], [LA Data Import].[SORTER NAME], [LA Data Import].[INSURED LAST NAME]
HAVING ((([LA Data Import].[TODAYS DATE])=[Enter Date]) AND (([LA Data Import].[DUE DAYS])<>"NOT ACTIONABLE WORK"));

 

[Joe4]

Access and store dates as numbers, specifically, the number of days since 1/0/1900.
So values like 43245 are actually how Access stores the date behind the scenes (to see what this date actually is, enter that number into Excel, and then change the cell to any date format that you want).

I don't think you want to use the CDATE function on the DUE DATE field, since it is already a date. You just want to change the data type or format.

Also, have you defined your Parameter Data Type to be a date?
See: https://support.office.com/en-us/ar...-reports-8209eb5c-1589-42e2-9b20-4181f4c7a356

 

[SailorJerry7030]

I understand what you're saying, but unfortunately I'm just in charge of running it and not making any design changes. Any ideas why it works for previous dates 6/8/2018, 6/7/2018, etc, but not for today?

 

[Joe4]

If you take the parameter off the query temporarily, does it run?
Make sure that there are not any errors, blanks, or odd entries in your TODAYS DATE field.

Also, which version of Access are you using (US, European, other)?

 

[SailorJerry7030]

I removed the criteria [Enter Date] and < > "NOT ACTIONABLE WORK" and was given the error "Invalid use of Null". I filtered the table by today's date 6/11/2018, filtered Type of Work by Actionable and tried to see if there were any blanks in the Due Date or any dates that might not actually exist 2/31/18, but wasn't able to find anything. I'm using US Access 2016.

 

[Joe4]

I removed the criteria [Enter Date] and < > "NOT ACTIONABLE WORK" and was given the error "Invalid use of Null".

Try removing the whole "HAVING..." clause, along with the Parameter.

Note that when something stops working all of a sudden, and nothing in the code or programming has changed, the most likely culprit is bad data being added that is blowing things up.

 

[SailorJerry7030]

Bad data it was! Filtered by Today's date for current day, filtered Due Days by removing "Not Actionable Work" and then filtered Due Date by blanks and sure enough there were blanks for 6/11/18, no blanks for 6/8/18 or 6/7/18, but blanks for 6/6/18. So that's why 6/8 and 6/7 worked because it was able to CDATE the DUE Date field but when I ran it for 6/6/18 it error-ed out as it did for today's date.

 

[Joe4]

Excellent!

Glad we got to the bottom of this!