Trouble with Parameter Query 1

[teamssc]

Access 2016

I've created a query of 2 identical tables, combining different years of data.

There is a text field called "Loss Reported". Sample Data "1/1/2018 1:10:25 PM"

I created a new field in the query from "Loss Reported" to trim to time and convert the data to a true date (this works fine): New Job Date: (CDate(Format([Loss Reported],"mm/dd/yyyy")))

Next I want to query the date range, real simple 1/1/2020 to 12/31/2020, with a parameter.

This works
>=#1/1/2020# And <=#12/31/2020#

This doesn't
>=[Start Date] And <=[End Date]

What am I doing wrong? Any help is appreciated. Trying to have annual sales reports done this week.

Here's the SQl query if it's easier to solve (I haven't messed with the SQL Query.

SELECT [All Jobs Data].[(Do Not Modify) Invoice], [All Jobs Data].[(Do Not Modify) Row Checksum], [All Jobs Data].[(Do Not Modify) Modified On], [All Jobs Data].[Job #], [All Jobs Data].[Job Name], [All Jobs Data].[Job Type], [All Jobs Data].[Loss Reported], (CDate(Format([Loss Reported],"mm/dd/yyyy"))) AS [New Job Date], [All Jobs Data].Adjuster, [All Jobs Data].Contractor, [All Jobs Data].[Customer Contacted], [All Jobs Data].[Site Inspected], [All Jobs Data].[Rush Delivery Date], [All Jobs Data].[Invoice Sent], [All Jobs Data].[Program Complete Date], [All Jobs Data].Billed, [All Jobs Data].[Textiles Billed], [All Jobs Data].[Electronics Billed], [All Jobs Data].Received, [All Jobs Data].[Balance Due], [All Jobs Data].[Address 1], [All Jobs Data].[Address 2], [All Jobs Data].City, [All Jobs Data].State, [All Jobs Data].Zip, [All Jobs Data].[Bag Count], [All Jobs Data].Contaminant, [All Jobs Data].[Total Pieces Cleaned], [All Jobs Data].[Total Pieces Not Restored], [All Jobs Data].[Sales Rep], [All Jobs Data].[Sales Rep Commission], [All Jobs Data].[Sales Rep Commission %]
FROM [All Jobs Data]
WHERE ((((CDate(Format([Loss Reported],"mm/dd/yyyy"))))>=[Start] And ((CDate(Format([Loss Reported],"mm/dd/yyyy"))))<=[End]));

 

[dhookom]

I would use:
DateValue([Loss Reported])

I also recommend you stop using query parameters prompts faq701-6763.

You can set the data type of parameter prompts and references to controls on forms. It's in the Query Design ribbon -> Show/Hide.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[teamssc]

Thanks Duane, I had used DateValue([Loss Reported]) before, but couldn't get the parameters to work, still can't with DateValue([Loss Reported]).

So I'll read the link you posted and see if I can figure it out.

Thanks

 

[teamssc]

ok, Setting the Parameter data type on the ribbon was the trick. Real lifesaver. Thanks again! (and yes, I'll read why not use parameters prompts)

 

[Andrzejek]

Since you want to include selected Dates in your outcome, you may consider:

...
WHERE CDate(Format([Loss Reported],"mm/dd/yyyy")) [blue]BETWEEN[/blue] StartDate [blue]AND[/blue] EndDate

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson