Open a form with the record based on the date 1

[bigdars]

I am having trouble with this. I have a table that contains a column named "month_year". I have pre-filled the column with dates, ie 01/01/03, 02/01/03, 03/01/03...and so on. I have a form named "frmMetrics" that contains a field named "Period" that displays the date in a MMM YYYY format when the form is opened. The form will be used by some staff to input 8 different systme statistics every month. What I want to happen is that when the form is opened, the only record displayed should be the record with the date prior to this month. In other words, if the user opens the form in March, he will be presented with the February date record. He will not be able to enter in the March stats until April and so on.

Seems simple, but I don't know if I should populate the form by a query or if this needs to be done in a module or what the code would be to accomplish this. Any assistance is greatly appreciated.

 

[Rick39]

Month(YourDate)-1

 

[scriverb]

Here is SQL that does it for you. I used a table named tblMetrics. Just modify the query by changing the table name and adding more select fields from your actual table.

SELECT tblMetrics.Month_Year, tblMetrics.Stat_Field1, tblMetrics.Stat_Field2
FROM tblMetrics
WHERE (((Month([tblMetrics]![Month_Year]))=Month(Date())-1));

Let me know if you need more assistance. Bob Scriver

 

[scriverb]

Sorry, use the above SQL to create a query and use that as the RecordSource of your Form. Bob Scriver

 

[bigdars]

It works fine...but I am getting multiple years. I appreciate the assistance. I am working on adding the year but appreciate any further input. Thanks a bunch

 

[bigdars]

I got it! Thanks a bunch for the help!

SELECT tblMetrics.*
FROM tblMetrics
WHERE (((Month([tblMetrics]![Month_Year]))=(Month(Date())-1)) AND ((Year([tblMetrics]![Month_Year]))=Year(Date())));

 

[scriverb]

Yes, I was going to adjust the SQL for you but you did it already. Great. Bob Scriver