Retrieve records based on 3 three periods, not using dates

[RFrechette]

I am trying to create a query that retrieves the records from the last 3
periods entered into the system.

I am gathering my data from a SQL database where I can not change the
formats of any of the fields.

Each record is entered with a Period Date called "read_period". (For
example, 200608, 200609, 200610, etc) These are actually text fields. They
are not necessarily just the month's data, 200609 could have data from
09/02/06 thru 10/05/06, so I can't just go by month.

I need to create a query (for a report) that will automatically give me data
for the last three periods entered in the system.

So if the last period entered was 200610, I would need 200608, 200609, and
200610. If the last period entered was 200611, I would need 200609, 200610,
and 200611.

I know I can easily do this by putting a criteria of Between [Enter Beg
Date] and [Enter End Date] for the user to specify the periods (that worked
for me). However, management would like the report to just grab the last
three periods automatically without any user input.

Is this possible and if so, could someone explain how I can accomplish this?

I would greatly appreciate it.

Thanks,

Rachel

 

[Jerry Whittle]

First thing to do is make them dates in the field of the query:

PeriodDate: CDate(Right([read_period],2) & "/1/" & Left([read_period],4))

Note: For the CDate function to work, it must be a valid date and not a null
value. If [read_period] is empty or something like 200613 or 20072, then
you'll have problems.

Then in the criteria put something like below. You may need to mess with the
-3 to get the results that you want.
Between CDate(Right(DMax("[read_period]","YourTableName"),2) & "/1/" &
Left(DMax("[read_period]","YourTableName"),4)) and DateAdd("m",-3,
CDate(Right(DMax("[read_period]","YourTableName"),2) & "/1/" &
Left(DMax("[read_period]","YourTableName"),4)))

 

[John Vinson]

I need to create a query (for a report) that will automatically give me data
for the last three periods entered in the system.

How about

SELECT TOP 3 *
FROM yourtable
ORDER BY read_period;


John W. Vinson[MVP]

 

[RFrechette]

Jerry,

It worked perfectly. I did have to play with the "-3" to get it to work. I
tried "-2" and it worked great for the last 3 month's data. It's awesome.

Thank you so much.

Rachel

First thing to do is make them dates in the field of the query:

PeriodDate: CDate(Right([read_period],2) & "/1/" & Left([read_period],4))

Note: For the CDate function to work, it must be a valid date and not a null
value. If [read_period] is empty or something like 200613 or 20072, then
you'll have problems.

Then in the criteria put something like below. You may need to mess with the
-3 to get the results that you want.
Between CDate(Right(DMax("[read_period]","YourTableName"),2) & "/1/" &
Left(DMax("[read_period]","YourTableName"),4)) and DateAdd("m",-3,
CDate(Right(DMax("[read_period]","YourTableName"),2) & "/1/" &
Left(DMax("[read_period]","YourTableName"),4)))

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to create a query that retrieves the records from the last 3
periods entered into the system.

I am gathering my data from a SQL database where I can not change the
formats of any of the fields.

Each record is entered with a Period Date called "read_period". (For
example, 200608, 200609, 200610, etc) These are actually text fields. They
are not necessarily just the month's data, 200609 could have data from
09/02/06 thru 10/05/06, so I can't just go by month.

I need to create a query (for a report) that will automatically give me data
for the last three periods entered in the system.

So if the last period entered was 200610, I would need 200608, 200609, and
200610. If the last period entered was 200611, I would need 200609, 200610,
and 200611.

I know I can easily do this by putting a criteria of Between [Enter Beg
Date] and [Enter End Date] for the user to specify the periods (that worked
for me). However, management would like the report to just grab the last
three periods automatically without any user input.

Is this possible and if so, could someone explain how I can accomplish this?

I would greatly appreciate it.

Thanks,

Rachel

 

[RFrechette]

I ended up using the response from Jerry.

But thank you very much for your response as well.

Rachel