Having Month Displayed in report from query

[Cwittmaack]

I have a query that pulls the records by month and year this worked great
Query is set up as
Field: Month: Format([ErD],"mm/yyyy")
Criteria: [Enter Month and Year (mm/yyyy)]

Like i said this works great. But I need the Month that is pulled to display in the report header along side the report header text without the user needing to enter the Month again.
Example report header: January Things you need to know.

the person pulled records for January

Thanks for any help on this.

 

[vbaInet]

I suppose get it from the field.

 

[Cwittmaack]

Pull from the Query like =[Qry_OpShtMontly]![Month]
or pull from table.
I'm wanting to pull the month that they enter from the query execution and display it with the header text.

when i test this it wants me to enter the month again.

 

[vbaInet]

Your report is filtered by the date entered into "Month" field. Whatever is entered into the parameter box is what is used as the criteria for the "Month" field which is also in the record source of your report. So get that value entered from the "Month" field.

Field: Month: Format([ErD],"mm/yyyy")

 

[Cwittmaack]

OK, bear with me please i'v never pulled information from a query input to display just part of it in an additional location in a report. I have =[Qry_OpShtMontly]![Month] the Query name and field name used. Do i put ,Format[hMonth],(MonthName),"mm") which hMonth is the name of the text box. I have tried many combos each wanted me to enter the month again or i get an error. still trying to learn

 

[vbaInet]

Just:

Month([ErD])

 

[Cwittmaack]

This report can be run just before 12 midnight or just after, I'm combining 3 databases into one, they had a report for each month, wish i think that only one report should work just need the month they input to place in the header text area just before the static text. Example: January Things you need to know The month will change as they change the month input. I hope this will explain what i'm trying to do a little better. Thank you for your help so far.

 

[vbaInet]

I suppose you're having difficulty visualising what I've advised over again.

A user enters a "mm/yyyy" into the date field for the records to be filtered. The dates of the Erd field will ONLY correspond to the month and year that was input. So get the month of the Erd field by using the Month() function.

Does that make better sense?

 

[Cwittmaack]

Got it =MonthName(Month([ErD])) Thank you so much for your help, a lot easier than i would have expected, I guess i was trying to make it harder than it really was. thank you again for teaching me something new.

 

[vbaInet]

Yeah you were thinking to hard

Although, if you truly want to capture the value entered then you want to use a dedicated form (with a textbox) for that. With your current approach if you entered a month that has no related records the report will open blank and the MonthName() will be blank too. So if you're not concerned about this then it's fine as it is.

 

[Cwittmaack]

Our company is a 24 hr 7 day a week, there will always be data, but i will look into that "Use form to pull from query and if no data, display no records found and if records found display report" am i thinking on the correct path?.

 

[vbaInet]

Ok, don't worry about that then. It's very unlikely that there won't be data in a particular month.

Almost! It's actually:
1. Use form for inputting "mm/yyyy"
2. Perform a check with the criteria entered to see if at least one matching record. You can use DCount() function for this.
3. If there is proceed with opening the report
4. Otherwise, Msgbox "No records found!"

 

[Cwittmaack]

vbaInet

I'll take a look at that when i get this last report created. tks.