If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Access Query - Criteria Help Needed

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,172
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!
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,172
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!
Reply With Quote
  #5 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,189
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,189
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #9 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,189
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On