Query Results in Forms

[tas6959]

I am pulling in query results into MS Access from excel. The excel query is an Analysis for Office query tied to SAP. I built a query in access to pull in select fields I need from that linked excel spreadsheet. The query includes Item, Item Description and Sales date. I am pulling this data into a form. The issue I have is we are looking at rolling 12 month data. Every month the 12 months data shifts one month. When I pull the dates into the form, each field is date specific. If the most current month/year is Jul-22 and then next month the most current month is now Aug-22 which was not there when the form was built how do I make it so the form shows each of the 12 months in the query as it shifts one month every month?

[Gicu]

Show us please a screen shot of the form?

[Micron]

Use a calculation (expression) to subtract 12 months from either the current date or some provided date - whichever best suits your need. This should calculate a date 12 months prior to today
dateadd("m",-12,date)

Perhaps take a look at DateAdd function?
EDIT - forgot to say your post isn't clear. 12 months prior to today, or from the 1st of the month that is 12 months ago? Probably the latter. Perhaps post your form sql.

[tas6959]

The last date is 7/22...how do you make sure 8/22 will populate once the 12 month query is updated.

[tas6959]

The months are not based on today. The query is updated once a month ends. This means that sometime in the first week after a month ends reporting is updated (let's say August just ended so the first week of September is the query update). The new query date range will be Sep-21 to Aug-22 (the prior query date range would have been Aug-21 to Jul-22).

[Gicu]

That's what I thought, you are hardcoding the labels on the form .

Replace the labels in the form's with text boxes and use something like Format(dMax("[YourDateField]","qryYourQuery"),"MMM YY") to get the latest, Format(DateAdd("m",-1,dMax("[YourDateField]","qryYourQuery")),"MMM YY") for the second to last, .... (or you can strat with dMin for the first one and keep adding positive 1 for each subsequent ones).

Cheers,

[tas6959]

Perfect...thx!!!

[Gicu]