Related Topics

Month To Date Comparison Query This Month To Last Month

Refer to ONLY THE MONTH??
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


Refer to ONLY THE MONTH??
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?

also, I've 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


How do I convert an inout month number into a month name?
I have a report based on a query where in the query the user inputs a month from 1 - 12. How do I make the report say the name of the month instead of the inputted number?

The field in the query is constructed like this:

Field Month: Month([tbl_2_CustomerOrder.OrderDate])

Criteria [Input month (1-12)]

I've tried using =Format$([Month],"mmmm") but it always returns "January


Records for next month
Looking to create a report criteria that would display all records with a due date for the next month. I know I can get the current month with Month(Now()) what is the syntax for the following month?


Using Current Date to find 16th
I have a few queries that use date parameters. We use a 15th date as month end.
Example Month - 05/16/2011 - 06/15/2011 (I usually just do a less than 06/16/2011 to make it easy).

I have created these to figure out the 16th of the current month and the 16th of previous month:
MONTH BEGIN: DateSerial(Year(Date()),Month(Date())-1,1)+15
MONTH END: DateSerial(Year(Date()),Month(Date()),1)+15

This will work great assuming the query runs between 06/16/2011 and 06/31/2011.

MY QUESTION:
Is there a way to create an IF statement and between the 16th of current month and 15th of next month, find the correct dates.

FOR EXAMPLE -
IF RUN TODAY I WANT - 05/16/2011 and 06/16/2011
IF I RUN TOMORROW I WANT - 05/16/2011 and 06/16/2011
IF I RUN 07/10/2011 I WANT - 05/16/2011 and 06/16/2011
BUT.IF I RUN 07/16/2011 I WANT - 06/16/2011 and


Month and Year Criteria
I am setting up a form and I would like the date for the next 12 months to be updated automatically. I would like it to read the month and year for each month. What is the code in achieving this?

September - Current month and year
October - Current month and year +1
November - Current month and year +2
December - Current month and year +3

You get the picture =) I appreciate your help with this!


Query for finding Min & Max of a Month
I have to create a select query (which I think is a bit complex). I have to retrieve Week, Year and month from a Master table. I find this simple. Apart from this I need to find the starting date of a particular month and last date of the particular month for the respective month. The problem is the month is not a calender month and dates differ. It will be easy to understand if you have look at the table(attached Zipped Image file).

The columns "Week", "Year" & "Month" are directly selected from the master table. The Column "Beg Week Month" & "End Week Month" have to be created in the query. I am able to find Min of "Beg Week Month" & Max of "End Week Month", but I need for that particular month as displayed in the table


MonthName instead of month number
I have built a query that supplies me some totals by month. The query works awesome except that I don't want numbers for the month.

How do I get the numbers representing the month to reflect the alpha for that month?

Here is my SQL:


Custom Date in Query
I have a query I am merging with Publisher and need a specific date every month. In this date, I need the day of the month to be the 9th; the current month for the current year.

Example;

We are in sept so need the date field to be September 9, 2011. Next month when I run the report, the query will populate this column with October 9, 2011, and so on.

Is there a date function I can format for this field


Custom Date in Query
I have a query I am merging with Publisher and need a specific date every month. In this date, I need the day of the month to be the 9th; the current month for the current year.

Example;

We are in sept so need the date field to be September 9, 2011. Next month when I run the report, the query will populate this column with October 9, 2011, and so on.

Is there a date function I can format for this field?


Finding a field from last month to put in new month
In a query, or maybe it can be done in VB, how do I search for a record from last month. There is a date field in each record, and the primary key is the name of the employee AND the month. I am trying to carry over a number field from last month to the new month data for that employee and I can accomplish this in excel using the edate function but I cannot figure this out in access as I am migrating my data to access. I am familiar with SQL.


Sorting in a Query (Dates)
So I have a "gazillion" dates in my "Date" Column

I wanted to extract the year and month, so isued the =year[x] and =month[x] fx and then concatenanted the 2 =year[x]&month[x].

This doesn't allow for proper sorting

when I sort I get the following
ect

to work around this issue I used the followin f(x)
MONTH1: IIf(Len(Month([c-date]))=1,0 & Month([c-date]),Month([c-date]))

This fixed my issue, but was wondering is there was a more efficient solution


produces 3 sets of expiry dates (1 month apart)
I'm looking for some help with a query which produces 3 sets of expiry dates (1 month apart) which are printed on a voucher.

for example on Monday 17th September, and the query does this:

Adds 21 days to the date entered and returns the last day of the month (in the above case it would return 31 October 2012) "Month1"
Adds 1 month + 21 days (30 November 2012) "Month2"
Adds 2 months + 21 days (31 December 2012) "Month3"

The query below works just fine with the exception of days where the report is generated around + 21 days mark. Let's say we generated the report on 10 September 2012, month 1 would beOctober, Month 2 would also be October because of the 31 days in September, 30 in October.

Ideally I would like the query to work on Month 1 as it is (date inputed + 21 days, showing end of that month) and then Month 2 to be month after the end of the resulting month end, Month 3 to be the month after that.


Complex Select Query for finding Min & Max of a Group
I have to create a select query (which I think is a bit complex). I have to retrieve Week, Year and month from a Master table. I find this simple. Apart from this I need to find the starting date of a particular month and last date of the particular month for the respective month.

The problem is the month is not a calender month and dates differ. It will be easy tounderstand if you have look at the table (Image attached).

The columns "Week", "Year" & "Month" are directly selected from the master table. The Column "Beg Week Month" & "End Week Month" have to be created in the query.

I am able to find Min of "Beg Week Month" & Max of "End Week Month", but I need for that particular month as displayed in the table.


Complex Select Query for finding Min & Max of a Group
I have to create a select query (which I think is a bit complex). I have to retrieve Week, Year and month from a Master table. I find this simple. Apart from this I need to find the starting date of a particular month and last date of the particular month for the respective month. The problem is the month is not a calender month and dates differ. It will be easy to understand if you have look at the table (Image attached).

The columns "Week", "Year" & "Month" are directly selected from the master table. The Column "Beg Week Month" & "End Week Month" have to be created in the query. I am able to find Min of "Beg Week Month" & Max of "End Week Month", but I need for that particular month as displayed in the table.


DCOUNT: DATE CRITERIA month/day = todays month/day
Trying to count records in a Query were the Month and Day of DOB(date of birth) is equal to Todays Month and Day.

I tried to start with just month but this line is erroring.

=DCount("*","q_RET_PLN","MONTH([CIV_DOB_DTE]) = MONTH(Date()")

Ultimate air code is

=Dcount records in q_RET_PLN were month of DOB = todays month AND Day of DOB = todays day.


Same day last year comparison
I have a report that is giving me a calc for month to date on the current year(2010). On that same report I need to do a comparison for the same day of the previous year(2009). Example Thursday 02/25/10 is Thursday 02/26/09. I have the info for the the month to date and to figure the same day last year but I am unable to get these two, to work together to get me the data I need to view in the report. My month to date for current year I am using is:
=DSum("[Call Offered less Abdn <20 secs]","[Service Guarentee Daily QRY]","Date Between #" & DateAdd("ww",-52,[Date])+1 & "# AND #" & [Date] & "#")
the report is grouping on the month, and I found the same day last year is:
DateAdd("ww", -52, SomeDate).
I understand I should not have a field name the same as what is reserved in access for functions ( I am in process of updating


records in month a year ago?
I have a table with a date field in it and would like to be able to find those records which have a date in the same month as now, but 1 year ago.

Is it possible to have a general query which will work regardless of which actual month is the current month?

DateAdd() has been suggested but I'm new to this and am not sure how to use it!


Month/Month to date
I have created a data base which includes the 12 months of each year. For each month I have created 2 forms based on 2 seperate queries. Both forms contain the same 4 fields i.e. 'DueDate - Income - Expense - Balance. To the first form I have, under DueDate on the query side written the following criteria: Between 01-01-2011 and 31-01-2011 (the month of January) this shows all expected posts for the month of January for the complete month.

For the second form I have changed the query wording slightly to read:
Between 01-01-2011 and Now() this shows the posts from day to day as the month progresses however this can run into February and so on unless I change the criteria reading.

Is there any way I can word the query so that it stops automatically at the end of the month


Using Current Date in a Query
For a school project, I basically need to make a query that lists dates within a month of the current date. The problem is, the dates are laid out as such: PK field, Order FK, Payment 1, Payment 2, Payment 12. I need a query that will, when ran, return to me a list of the orders and payments within one month of the date. Using Month([Schedule].[Payment 1])=Month(Now()) as a criteria returns to me the entire record, not the individual dates. Is there any way to do what I want?