Ms access due date query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Ms access due date query

  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Ms access due date query

    Greetings everyone. I'm a US Marine of 28 years working with this new MS Access database that was created to record individuals who submit appeals claiming unfair etc. evaluation reports. With that said, I'm wanting to create a past due date using the query builder. I have selected the fields form the table that was build by a government contractor 4 years ago. Bear in mind that I am a novice at this lingo terms you all use. Moreover, I'm fumbling through pages for the "Access for Dummies" picking you terms and left with more questions than answers. I will type out the fields from the table. Then color red is what I'm trying to identify petitions that my office routed to another office with a due date followed by date returned. I'm wanting to locate those petitions that are pass the due date.

    Field NAME RANK Routed to #1 Date Out #1 Date Due #1 Date Rtn'd #1

    FYI the table name is PERB. The part I'm having difficulty with is the Expression Builder. While the curser is on the field "Date Due #1 and I click on the Icon "Expression Builder", a menu box appears. Inside shows the field name. I'm not sure if that should remain if and when someone gives me advise on what to type in.

    Thank you for your time

    Semper Fi,
    MSgt, USMC
    Last edited by Kestrel Falcon; 07-08-2009 at 11:26 PM.

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    I have been sitting here for over 20min, trying to figure out how to answer your question. I try to be very flexible when dealing with someone new to Access, but I am having trouble figuring out your request.
    First I think you mean past, not pass. You are past the due date, not passing a due date.
    Next I can't tell what all of the Field names are. They all run together like one big long name. I suggest that you seperate the names, like with commas, or just list them one name on a line.
    What is the criteria for the past due date? Like due dates that have expired and are without a date retn'd. Or do you just want records where the date retn'd is past the due date. Or do you want both thoses kinds of records? Or maybe any record at all with an expired due date?

    One thing to think about, Access, uses SQL when it builds it's queries. So it is much clearer if you post the SQL, rather than try to explain all of the steps you used to create the query. Any user interface takes alot more to explain as text then to actually do it. So posting the SQL is mush eaiser and clearer. To get to the SQL switch the query builder from design view to SQL view.

    So the best way to go is to build as much of the query as you can. Then switch to SQL view and copy the SQL and post it. Also you will need to describe what the creteria is for selecting the records that you want. From there we can give you revised versions of your SQL which you can paste back into your Access query and then run to see if it's getting you what you want.
    Also you can switch back to design view when you are done, so that you can see how it would look had it been built in the Access Query Designer.

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    Thanks for your reply, Sir. I'm want to retrieve records that are past due and show up blank in the date return field. Meaning no one entered when it arrived back from another office.

    Example:
    Today's date is 20090708
    Date due is 20090701
    Return date (blank).

    I'm wanting to find out what external office is sand bag'n stuff my office sent to them for an advisory opinion.

    Kestrel

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    In the query designer, once you have dragged down, all of the fields that you want to see. Include the Due Date and Return date. Now under the fields there is a section called Criteria.
    Under the Due Date column, but in the criteria row add this: <=Date()
    Next Under the Date Returned, and in the same criteria row add this: Is Not Null

    Now run your query, this should give you what you want.

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    Thanks Ron. I have it copied down and will give it a try tomorrow morning.
    Thanks
    Semper Fi,
    MSgt USMC

Similar Threads

  1. Replies: 5
    Last Post: 05-08-2008, 09:09 AM
  2. SQL Query on Excel and Access
    By Joey_G in forum Database
    Replies: 3
    Last Post: 04-11-2005, 11:55 AM
  3. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 12:22 PM
  4. Access And SQL date statement
    By Saiful in forum VB Classic
    Replies: 7
    Last Post: 11-24-2000, 05:21 AM
  5. Replies: 2
    Last Post: 10-24-2000, 09:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center