Solved

Run a query specifying a date range.

Posted on 2008-09-29
8
444 Views
Last Modified: 2010-04-21
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?
0
Comment
Question by:rxmijares
  • 4
  • 3
8 Comments
 
LVL 6

Expert Comment

by:twintai
ID: 22601023
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 22603282
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
 

Author Comment

by:rxmijares
ID: 22605763
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 22607163
The Property sheet of the report. Pick the tab Data.

/gustav
0
 

Author Comment

by:rxmijares
ID: 22614169
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 22614282
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
 

Author Closing Comment

by:rxmijares
ID: 31501353
Thank you. That worked great. Once again, sorry for the earlier misunderstanding.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 22615972
You are welcome!

/gustav
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question