MS Access 2007 Date Query Question

55 pts.
Tags:
Access Database
Microsoft Access database
Microsoft Access queries
My question is 2 parts:

1) I need to build a query that will look for dates in a date field that are >= 4 1/2 years from a date specified by the user. For example, if the user enters Sep 2011 the query would return all records in the database that are 4 1/2 years older than the specified date (i.e. Mar 2007). There is another date field if the record meets the query criteria but the 2nd date field is populated then the record will not be returned.

2) How do I format the input request from the user to ensure Access understands what to compare with the Inv_Date field?

The query results would be in a new colum title Investigation Due.



Software/Hardware used:
MS Access 2007, Windows Vista Enterprise

Answer Wiki

Thanks. We'll let you know when a new response is added.

you may need to play with the format a bit but under the criteria for query of that field give this a shot and adjust as need: >DateAdd(“m”,-52,[Enter date. I.E. June 2011])
My test field was defined as ‘Date/Time’ with a format of ‘Medium Date’

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • orangehat
    typo. I retyped it with errors. take out the periods. >DateAdd("m",-52,[Enter date IE June 2011])
    1,720 pointsBadges:
    report
  • Smidkiff
    Thanks for the response. This patrially worked. When I ran the query without specifying a date per your instructions it reruned the correct date for the field. If I tried entering a month and year it returned nothing. So, if the user inputs a specific date, (i.e. Sep 2011) you get 0 returns eventhough there are numerous records that are 4 1/2 years older than that date. If I don't enter any date I get the entire table returned (600+ records) with the appropriate date listed (i.e. the date field + 4 1/2 years). How can I format the criteria the say give me the dates that are 54 months or older than the date entered?
    55 pointsBadges:
    report
  • carlosdl
    "So, if the user inputs a specific date, (i.e. Sep 2011)" "Sep 2011" is not a specific date. It represents a month. To be able to compare this kind of data to a date field you need to convert the entered data into a date format. You will probably need to concatenate a day, and the convert the string to a date. Take a look at the DateValue and DateSerial functions.
    69,920 pointsBadges:
    report
  • Smidkiff
    Thanks. I appreciate the response. I'll take a look at that.
    55 pointsBadges:
    report
  • tlsanders1
    And I think you want -54 for 4 1/2 years instead of -52.
    1,340 pointsBadges:
    report
  • Smidkiff
    I had already made the correction for -54 vice -52. I appreciate all the help.
    55 pointsBadges:
    report
  • orangehat
    sorry or the -52 error. (-54) I don't know what I thinking when I did that.
    1,720 pointsBadges:
    report
  • Smidkiff
    No problem. I really appreciate the input. Your input gave me the results I was hoping for.
    55 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following