Solved

Run a query specifying a date range.

Posted on 2008-09-29
8
447 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 22615972
You are welcome!

/gustav
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server views 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 Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

759 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