Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Run a query specifying a date range.

Posted on 2008-09-29
8
Medium Priority
?
450 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 51

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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 51

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 51

Accepted Solution

by:
Gustav Brock earned 2000 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 51

Expert Comment

by:Gustav Brock
ID: 22615972
You are welcome!

/gustav
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

660 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