Results 1 to 11 of 11

Thread: Month Query

  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Unanswered: Month Query

    How would I write the expression for a criteria in an access query (Access 2000) for a field that is a Month field where it would only shows me the records that were older than two months prior to today's date?

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Either use the "DateDiff" function or the "DateAdd" function.

    For example, either of the 2 following expressions in a query would work:

    Field: DateDiff("m",[YourDate],Now())
    Criteria: > 2

    Field: DateAdd("m",2,[YourDate])
    Criteria: < Now()

    The only difference between the 2 is that the first one results in a number, the second one results in a date.

    The first one is more flexible, since you can remove the criteria and then do things like group by the number of months prior to today in a report.

    Hope this helps
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Oct 2004
    Posts
    3
    I should rephrase my question. If I had a month field where it only contained month names. How would I be able to pull up all records that were two months old?

    Thank you for responding tcace!

  4. #4
    Join Date
    Jul 2004
    Posts
    156
    Essentially, you would use the DateAdd("m",2,[YourDate]) as Todd had but it would go in your criteria. You also need to add a "<=" greater than sign and change the 2 to a -2 and the [Your Date] to Now() to give:

    <= DateAdd("m",-2,Now())

    You may have to fool around with the comparison signs to get it just right, but this would take you back exactly 2 months and include that date and any before it. Check out the DateAdd function in Access's help to get the low down on what each argument means and the options available for it.
    Last edited by DocX; 10-18-04 at 20:00.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  5. #5
    Join Date
    Oct 2004
    Posts
    3
    Thank you both for the help in this matter. It's still not pulling up any records though. I think the problem might be the fact that the Month column in the table is set to Text when I think it should it should be set to Date/Time.

    Would that matter?

    If so, how can I change a date/time column in a table to only give me the month names instead of the entire date?

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Yes, text instead of a date does matter. Text is just letters, whereas Date/Time values are actually numbers, so the comparisons (along with the Date calculating functions) don't match.

    My first suggestion in this matter would be to convert the Text dates into either Date/Time values or straight numbers (if you are only concerned about the month, an integer field 1 to 12 would work quite nicely - you'll need a seperate field for the year).

    If you want to continue to use text, make your date literals a 4 digit year + a 2 digit month. In other words, 200403 is March, 2004. 200410 is October 2004. The reason is that the month names are not alphebetical, so a text comparison won't work, but making your text dates fit this scheme will make them order correctly when sorted alphabetically. Actually, adding the last 2 digits for the day also works - just make sure you have the leading zero for single digit numbers!

    Now, to convert the current date to a matching text string, use the format function. For example, Format(Now(),"YYYYMM") puts todays date in the format shown in the previous paragraph. Often, I'll use a format function in a totals query that grabs just the month to group data by month. To get you date string representing 2 months ago, use the function I had above inside the format function:Format(DateAdd("m",-2,Now()),"YYYYMM")

    I still think you're best bet is to use a valid date format, however. In your query, add a field that converts your text date into a valid date and work off that field. For example, if your dates are VALID 3 DIGIT ABBREVIATIONS of the month, use this function: DateValue([YourDateText] & "1, 2004") This will be prone to fail, however, since an unrecognized abbreviation will generate an error.

    Keep in mind that text comparisons take longer than numeric comparisons. You won't notice it on a small database, but introduce a large recordset, a network and multiple users and you will.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  7. #7
    Join Date
    Jul 2004
    Posts
    156
    Myself, I have wondered how to input dates in month/year format and have Access recognize it as a date. That way you can avoid all the intricacies discussed above. The only real way I've found is to enter any date into a date field that is in the month you're looking at and then have Format change it on display. But in the heart of the table, it's still that original date you typed in.

    Is there a way to input something like 4/2004 for April 2004 and have it recognize that as a date? All I've seen is that it converts it to 4/1/2004. And forget about putting it in as 4/04, which will make it 4/4/[CurrentYear].
    DocX

    The teachings of God's Begotten: 2 John 1:9

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by DocX
    Myself, I have wondered how to input dates in month/year format and have Access recognize it as a date. That way you can avoid all the intricacies discussed above. The only real way I've found is to enter any date into a date field that is in the month you're looking at and then have Format change it on display. But in the heart of the table, it's still that original date you typed in.

    Is there a way to input something like 4/2004 for April 2004 and have it recognize that as a date? All I've seen is that it converts it to 4/1/2004. And forget about putting it in as 4/04, which will make it 4/4/[CurrentYear].
    I would think so long as you base it on the underlying Date in the table and only Format it as a Text Date you should be able to make it work. But to show a date 2 months old (prior) there is no need for the ComparisonSigns. Simply place this:

    =DateAdd("m",-2,[PurchaseDate])

    That would give you a date that is 2 months prior to whatever date you compare it to. Could be a date in a TextBox such as PurchaseDate or could just be the current date Date()

    have a nice one,
    Bud
    Oh....btw, how do you have the date field set in your table? How do you input it? As just plain text? Or in a Date/Time Field???

  9. #9
    Join Date
    Jul 2004
    Posts
    156
    Actually, he does need the comparison operator because he wants all records older than 2 months prior.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  10. #10
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Red face

    Quote Originally Posted by DocX
    Actually, he does need the comparison operator because he wants all records older than 2 months prior.
    My mistake on that, I was doing that in a form. Late night and didn't notice the very beginning where he stated a query...... gimme a beer......

    apologies....
    have a nice one,
    Bud

  11. #11
    Join Date
    Jul 2004
    Posts
    156
    No prob, bob...er...Bud.
    DocX

    The teachings of God's Begotten: 2 John 1:9

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •