Run a query specifying a date range.

This is a two part question, but first let me explain a few things. I am using Microsoft Access 2003 with linked tables from an AS400. We typically use Access to run small or quick reports that we do not need often. In this case, I wish to pull a report that shows what was shipped during a specific period. Obviously, I can enter the date as YYYYMMDD and it will run the query and show me what I need.

Now for the first question. How can I tell Access that the specific column with the date is an actual date column? In other words, as far as Access is concerned now, it is just a column with numbers in its records. I want Access to recognize that column from the linked AS400 table as a date.

The second question is then how do I ask Access to run a report using a range of dates?
rxmijaresAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Gustav BrockConnect With a Mentor CIOCommented:
Modify the query in SQL view like this:

PARAMETERS
  StartDate DateTime,
  EndDate DateTime;
SELECT
  *,
  CDate(Format([YourFieldName], "0000/00/00")) AS DateShip
FROM
  tblYourTable
WHERE
  [YourFieldName] Between
    Format([StartDate], "yyyymmdd")
    And
    Format([EndDate], "yyyymmdd")

/gustav
0
 
twintaiCommented:
Use the date conversion function.

CDate : See link below:

http://www.techonthenet.com/access/functions/datatype/cdate.php

Of course, the data has to be order in a good order.
0
 
Gustav BrockCIOCommented:
1. In your query, use an expression like:

DateShip: CDate(Format([YourFieldName], "0000/00/00"))

2. Use the Filter property of the report:

DateShip Between StartDate And EndDate

Set the property FilterOn to True.

/gustav
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
rxmijaresAuthor Commented:
ok, I used the expression DateShip: CDate(Format([YourFieldName], "0000/00/00")) and when I run the report, it now shows the value as a date. However, I dont know how to apply the FilterOn property. Could you please elaborate a bit more?
0
 
Gustav BrockCIOCommented:
The Property sheet of the report. Pick the tab Data.

/gustav
0
 
rxmijaresAuthor Commented:
Oh, I see where I was confused. I mistated my earlier explanation. I refered to it as a report when in fact I should have stated that I was just using the datasheet. My user prefers to be able to run the query and then export it to Excel.
I have already formatted it that the column is seen as a date. Is there a way to pick a range of dates through the query itself?
I apologize for not explaining this properly.
0
 
rxmijaresAuthor Commented:
Thank you. That worked great. Once again, sorry for the earlier misunderstanding.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.