×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Trouble with Parameter Query

Trouble with Parameter Query

Trouble with Parameter Query

(OP)
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]));

RE: Trouble with Parameter Query

I would use:
DateValue([Loss Reported])

I also recommend you stop using query parameters prompts FAQ701-6763: Why not use Parameter Prompts.

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

RE: Trouble with Parameter Query

(OP)
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

RE: Trouble with Parameter Query

(OP)
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)

RE: Trouble with Parameter Query

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

CODE

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

---- Andy

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close