Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2013
    Posts
    4

    Access Query - Criteria Help Needed

    I have a table that I have listed all our warranty dates. I want to query my table to have it select the warranties that are going to expire during the month of March. So, I need it to list the school, the project & the date the warranty is going to expire. How do I write a query to make it produce this list? I want to redo the query each month, so I am guessing whatever the query looks like, I will just replace the date/month. Can someone help me. I have Access version 07 - 10 - haven't used Access since version 03. Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,255
    I can see two solutions to this problem:

    1. You can use the Month() function in the query expression:
    Code:
    ... WHERE Month(Table.DateColumn) = 3
    Though simple, this solution can be very slow and un-efficient if the table has many rows, because a call to a VBA function is made for each row of the table.

    2. You can base your query on the SQL Between operator:
    Code:
    WHERE Table.DateColumn Between #03/01/2013# And #03/31/2013#
    In this case, you can encounter a problem if the Time part of the Date was stored in the table (i.e. a non-zero value is stored in the Time part). You would then have to use:
    Code:
    WHERE Table.DateColumn Between #03/01/2013# And #03/31/2013 23:59:59#
    or:
    Code:
    WHERE Table.DateColumn Between #03/01/2013# And #04/01/2013#
    Both expressions yield the same result sets in most cases, if the Time part of the Date/Time value was systematically stored in the table.

    Note: The format of a Date/Time expression in a query is fixed and does not depend on the Locale settings of the Control Pannel of Windows. It can be: "mm/dd/yyyy" (US) or the ISO format "yyyy-mm-dd", the query builder (if you use it), will automatically convert the ISO format to the US format.
    Have a nice day!

  3. #3
    Join Date
    Jan 2013
    Posts
    4
    Thanks so much. My table only deals with dates, no time. I have taken the two ways you listed and typed them exactly as you typed it into my query criteria. When I run query, I get aEnter Parameter Value box pop up that ask for Table.DateColumn. I'm sorry, but what are they asking for?

    Month(Table.DateColumn) = 3


    Run results:
    Enter Parameter Value
    Table.DateColumn



    [Table].[DateColumn] Between #3/1/2013# And #3/31/2013#

    Run results:
    Enter Parameter Value
    Table.DateColumn

  4. #4
    Join Date
    Mar 2009
    Posts
    5,255
    Please post the SQL expression of the query: open it in SQL view and copy the text, then paste it here.

    I won't have much free time within the coming days but I'll try to find a moment to adapt the code to your needs and post it back.
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    what you could do is make the query return, say ALL warranties expiring between the current date AND the end of next month, or say the month after that.
    the advantage, there is no coding no parameters, its the same query

    its all down to the where clause...
    where Expirydate between date() and ....

    the .... depends on how tightly you wish to define the end limit
    no point int he end of this month.... to tight
    its down to how you use the dattime functions, specifically dateadd (or datesub)

    end of next month is (effecctively)
    dateadd("M", 2, format(date(),"#YYYY/MM/01#"))
    ..this (should) work because we are setting the limit to
    dateadd("M",2 ... adds two months to the sepcified date
    ..which is format(date(),"#YYYY/MM/01#"))
    the format takes the current month and year but sets the day element to 01
    which based ontodays date that will be 01 March 2013
    so we ask for rows whose expiry date will be less than or equal to 01 March 2013
    if you wanted the next 2 months
    dateadd("M",3


    OK so you dont want o1, you want one day before that
    so either subtract a day off the
    dateadd("d",-1,dateadd("M", 2, format(date(),"#YYYY/MM/01#")))

    or
    WHERE expiry date >= date() AND expiry date < dateadd("M", 2, format(date(),"#YYYY/MM/01#"))
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2013
    Posts
    4
    SELECT [Warranties Table].[Warranty Expires On], [Warranties Table].[Warranty Expires On]
    FROM [Warranties Table]
    WHERE (([Table].[DateColumn] Between #3/1/2013# And #3/31/2013#))
    ORDER BY [Warranties Table].[Warranty Expires On];

    I opened my query in SQL, copied and pasted it above. I hope this helps in determining what my problem is...

    Again, thank you so much for taking time out of your day to assist an Access Dummy.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    shouldn't datecolumn be replaced with the name of the column containing the date value which looks like its [Warranties Table].[Warranty Expires On]

    Code:
    SELECT [Warranties Table].[Warranty Expires On]
    FROM [Warranties Table]
    WHERE (([Warranties Table].[Warranty Expires On] Between #3/1/2013# And #3/31/2013#))
    ORDER BY [Warranties Table].[Warranty Expires On];
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2013
    Posts
    4
    Thanks Healdem. This worked and gave me the dates in March. The only thing I need help now with is getting the Campus ID and Project Description information to also show up when I run the query. Meaning, I need the information to look something like this:

    Campus ID Project Description Warranty Expires On

    KMS Install new lockers 3/1/2013


    The only thing showing up is the Warranty Expires On column and dates.

    Hate to ask for more help, but I need to know what date goes with what campus and project. Thanks so much.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,436
    So add the required columns to the SELECT part of the query. Eg:-
    SELECT my, comma, separated, column, list FROM mytable
    WHERE column = somecriteria
    ORDER BY another, column, list
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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