Refer to ONLY THE MONTH??

[taimysho0]

hello im having trouble writing a query. how do i select ONLY the ones that are ONLY within the month of the request date, and if there is a request date that is NEXT MONTH or MORE, then ignore it?? thanks



also, ive been told to use Month (#date value #) the only problem with this is, i want this query to be self sustaining so that no one will have to constantly change the date value every month, similar to Date(). Date() always refers to the CURRENT month and day

[June7]

Depends what you mean by CURRENT. If you run report on January 1, do you really want January or really need December? Best to have users input the period and use that as the criteria.

Is Request Date the field in table or the input criteria?

WHERE Month([field name]) & Year([field name]) = Month(user input) & Year(user input)

[taimysho0]

request date is an input criteria, but it is not controlled by me, it is input into a third party app. I create a query that grabs the request date from this table of this 3rd party app. If im looking for current month, the month would be January. Anything outside of January should be ignored.


The reason is because I have to create a table with multiple due dates. There is only one specific due date for each month, but same IDs.


example:

ID/ REQ DATE / ---DUE DATE

A/ 1/18/2012 -----1/23/2012
A / 2/20/2012 ----2/23/2012
A/ 3/5/2012-------3/29/2012
B / 1/5/2012--------1/23/2012


^ There will only be 1 request date per ID for the same month. Same goes for Due Date. How do i get the query to know WHICH due date to pick for the particluar ID of "A" for example. That is why i wanted to use the month function. If i set criteria to only look for the due date that is the same as the request date, then i think it will work, the question is HOW. thanks

[June7]

You have to create a table with multiple due dates? You mean build a query that shows the due date for each request date? What is the data source for the due dates? If there is no table then it must be calculated. If there is a table then can either do a join on extracted month/year parts or use DLookup function. Provide sample source data.

This is odd, the due date varies by month but not by ID? Life would be much easier if everything due by the 25th every month.

[boblarson]

The criteria on the field can be this (for the current month):

Between DateSerial(Year(Date()), Month(Date()), 1) And DateSerial(Year(Date()), Month(Date())+1, 0)

That gives you the range between the first of the current month and the last day of the current month (the month + 1 gives it next month and the day of 0 moves it back one day to the last day of the previous month).

[taimysho0]

i see, thanks bob,


how about if i just want to only refer to the current month?

i tried Month(Date()) for criteria but that does not work, any ideas? thanks

[taimysho0]

this question is semi somewhat related to the original post but for example

i have a column in a table that is out of my control and can not be edited. the fields in this column are in the format of date with the time

ex: 01/24/2012 1:00 pm etc

i formatted all the fields in this column to

Format([Date Column],"mm/yyyy")

for the criteria i put "Year(Date())" , the query runs but no data comes up even though i have data that is in the current year

[June7]

Format property setting does not alter the original data. The display is different from the original data but it is the original data that filter applies to. Also, your criteria has only the year whereas formatted date has month and year.

Create another field in query with an expression and set that field with criteria.

YearReq: Year([Date Column])

criteria would be Year(Date())

See my comments in post #2

[boblarson]

You can use what I wrote with no modifications for the current month (that is what the criteria I gave you is for), except if you have time in there too, you will need to change it to this:

Between DateSerial(Year(Date()), Month(Date()), 1) And DateSerial(Year(Date()), Month(Date())+1, 1)

Because the time would need

From #01/01/2012 12:00:00 AM# to #02/01/2012 12:00:00 AM#

in order to ensure you got the full day of 1/31/2012. So, the default with dates in the criteria is that it will automatically add on the 12:00:00 AM part if you don't include the time if it is on a field which has time included. So you don't need MONTH(DATE()) you need to use the criteria I gave in this post on the date/time field.

[taimysho0]

thanks june and bob,

for june: how would i write
YearReq: Year([Date Column]) to include month also?



for bob: lets say i want to modify this in the future to include the current month as well as the previous month, what would i need to add to your formula. thanks!

[June7]

MonYearReq: Month([Date Column]) & Year([Date Column])

Refer to Post #2

[taimysho0]

i see, last question and i should be done with this query.

i have:


Serial---------------Request Date

A ------------------1/11/2012
A ------------------1/12/2012
B ------------------ 2/14/2012
B ------------------ 2/15/2012


How do i NOT select any request dates that are ONE MORE day than the request date before it for the SAME serial? thanks!


ex: what do i put in criteria of the request date. something like

not like Request Date()+1

[June7]

That won't be so easy. Probably need a subquery. I would have to work with data to figure it out.

[taimysho0]

i see, if you can, that would be greatly appreciated. if not, thats fine too. thanks everyone for the help

[June7]

I would need your data if you want to provide it.