List records with recent month and previous 2

MS Office Access


  • 1. How do I detect #NUM! in a Query?
    I'm linking to an Excel spreadsheet which has values that don't match the field format e.g. a date field type with value "new". When I link this spreadsheet it has a NULL value, but I see #NUM! in the field. I would like to be able to detect this error and ignore this in my Access query, am I able to?
  • 2. How do I put in date criteria to search for birthdays within a pa.
    I am a novice. Please help. What wildcard is used for the year in a date field? I am trying to query for birthdays in a particular month for the employees. Thanks
  • 3. Help with calculations in query!
    I am creating a report based on a query...part of the report is to find the total average for all years of study by each med school applicant. For example: Applicant 1 Start Yr - 2000 # of courses - 10 Average - 85 Start Yr - 2001 # of courses - 8 Average - 90 and so on... So now I am trying to use these fields to... 3) total the # of yrs of study per applicant (2 yrs in this case) 1) get the total # of courses taken for all study years. (18 for this applicant) 2) get the overall average for all study years combined. (87.5) Not sure if there are any Null would want to eliminate those as well. Thanks!
  • 4. Each month for a query
    I have a database with month,day,year. I want to run a query using only one month at a time. for example, November month. What do I put in my criteria?
  • 5. Trincated query field
    I know similar questions have been posted before but none seem to help with my problem. I have a query: "SELECT t_customer.produce_letter_date, combine_text([t_customer].[customer_no]) AS letter_text FROM t_customer WHERE (((t_customer.produce_letter_date)=get_letter_date()));" where "get_letter_date()" is a function that determines a date from a form and "combine_text()" is a function that returns text from various other queries. I know the "combine_text()" function returns the text in full when run on its own but when I run it within the query it truncates to 255 characters. Can anyone suggest why or how to fix this. Thanks

List records with recent month and previous 2

Postby QUJN » Sun, 25 Mar 2007 00:49:29 GMT

I would like to create a query that would detect the most recent month and 
would list those records as well as records with dates from the previous 2 
months.  All dates are end-of-month values.    01/31/07, 12/31/06, 11/30/06, 
10/31/06 etc.

RE: List records with recent month and previous 2

Postby SmVycnkgV2hpdHRsZQ » Sun, 25 Mar 2007 01:31:03 GMT

Put something like the following in the criteria of the date field. Replace 
both "ex_date" with the field name and "tblDates" with the table name, You 
might also need to change the -2 to -3. 

Between DMax("ex_date","tblDates") And 

If you have a lot of records, like in the tens of thousands, the DMax 
function could be rather slow. I would help if your date field is indexed.
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Re: List records with recent month and previous 2

Postby John Spencer » Sun, 25 Mar 2007 01:50:25 GMT

Field: YourDateField
Criteria: Between DateSerial(Year(Date()),Month(Date())-2,0) And 

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

Re: List records with recent month and previous 2

Postby Michael Gramelspacher » Sun, 25 Mar 2007 06:38:49 GMT

In article <65E2B6A1-09E9-435C-9D98-
maybe something like this would also work (Northwind Query):

PARAMETERS [Enter Date:] DateTime;
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID=
[Order Subtotals].OrderID
WHERE Orders.ShippedDate Is Not Null And datediff("m", 
0,Orders.ShippedDate) between DATEDIFF("m",0,[Enter Date:])-2 
And DATEDIFF("m",0,[Enter Date:])
ORDER BY Orders.ShippedDate;

Similar Threads:

1.Show previous months reading on new months record

Good afternoon,

I have been trying to figure out for the last three days now, how I can show 
the previous months fuel readings on the next months record.  I don't know if 
I've been looking at it too long or what but I've hit a brick wall in trying 
to get this to work. My tables are:

FuelId - PK
Location (10 different locations in all)

DipId - PK
FuelId - FK

I've tried subqueries, DLookups and anything else I could think of, but I'm 
really unclear how I can have my July 31st readings show up when I enter the 
August 31st readings for the same location. 

Could someone please give me some quidance on this before I commit myself?

Any help would be greatly appreciated.

Thanks kindly

2.Sum of Most Recent to Most Recent-12 months

I have been searching around for a week now and I still can't wrap my head 
around this problem, so any help is greatly appreciated!

I have an attendence database, where employees get points when they aren't 
at work. I am trying to make a query/report that will find the most recent 
occurance of a point, and then sum the points for the employee from that most 
recent occurance to 12 months prior.

My table looks something like:
employee           pointdate            pointvalue

Example Data Set:
John               12/05/05           1
John               10/18/05           1
John               08/20/04           1

Desired Result: John's current points = 2

Can anything give me a starting point on how I should approach this? Thanks 
in advance!

3.Previous 12 months not corrent month.

4.deducting the previous month from the current month

I have a table with the following data

month       reading
Jan             100
Feb             200
mar             300

I need to take the value from a current month and deduct the previous month.
This needs to be done for all of the records within the field

5.Display records from previous month

I am trying to create a report that uses records from the previous month. The 
approach I am attempting is to base the report on a query that has been built 
to return the records for the previous month. The field name containing the 
date is Due Date. In the query, I have used DateAdd("m",-1,Date()) as my 
criteria under the "Due Date" field, but it returns nothing, even though 
there are two records that should be displayed. So, my questions are:

1) Am I using an incorrect formula? I have tried a few combinations of 
DateSerial functions, to no success. What would the correct formula be?
2) Am I taking the wrong approach? Should I build that filter directly into 
the report? 



6. Filter for Previous Month's Records

7. Select records from previous Month?

8. Selecting Records from a previous Month

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 15 guest