Retrieve Report by Month

[sharonbl]

Hi, may I know how do I retrieve report by month?
I have an unique id for every roll, eg, 20020809/85, 0809 will be 9th of august.
How do I prompt user to enter the month and to churn out data according to the month that the user had entered?

Its been a long time since I touched access.

 

[Alexandre]

It is generally not a very good idea to build your primary key; But since this is what you have got, let's see how to deal with it.
You have to identify a constant scheme in your built IDs that you will use as a starting point for string processing.

Let's say that the slash ("/") is always present and the month always corresponds to the two digits immediatly before it.
The you would
1. Have the user input the month number (ex: unbound combo based on a value list: "01";"January";"02";"February";...)
2. From a 'Launch report' command button, use that value to retrieve the corresponding ID(s) through some string processing and use that criteria as a filter for your report:
DoCmd.OpenReport "ReportName",,,"mid([YourIDField]", instr(1, [YourIDField],"/")-2,2) = '" & Me.txtMonth & "'"

Note that I did not test the above syntax. It may require some adjustment

 

[sharonbl]

thanks a lot...i'll go try it out......i realli do appreciate that.....

 

[sxu-mic]

similar prob

I have a similar problem. I am trying to retrieve reports by month. The query the report is based on have the months stored in numeric format. How can the user input either "Jan" or "January" and have the query retrieve values of [Month]=1 (Where [Month] is the field containing numeric values for each month)?

Also, I am getting a #Error when I try to use this field: =MonthName([Month]).

Thanks!

 

[Newman]

First thing, the word Month is a function and you should not use it as a field.
Let say you call it [TheMonth]...
Use one of these:
Month([Themonth],true) or Month([Themonth],false)
One gives you Jan the other one gives you January.
I don't remember which one is which.
Newman

 

[sxu-mic]

My bad. The field name I used is actually [Corresponding Month]. I thought saying [Month] would make it easier but I guess it just confuses people.




Newman I am not sure I understand you, so let me clarify myself first. When running a report, I want the user to input a month in alpha format (Jan, January, etc), but in the query I have the months info in numeric format in the [Corresponding Month] field. How do I get Access to convert Jan into 1?

I have a table that lists the numeric forms of each month in one column and the alph forms of each month in another. Is joining this table with the query the only way to convert an alpha month name to a numeric month name? I am hoping it would be an easier way because you can use the MonthName function to convert the other way.

 

[Newman]

SORRY!

My mistake, I got it all wrong on my reply.
For the first part, you should'nt use Month as a field. For the second part, Forget what I said.
Don't use drug, see what happens when you do?
I'll be back with a valid answer soon.
Sorry!

 

[Newman]

You could use a dropdown list of two columns. For the first one show the month in letters and for the second one show it in number. Make the link with the second column, but put 0(zero) as the column length for it.
Hopping that this is error proof.
Then again, I'm realy sorry about that mistake.
Newman

 

[sxu-mic]

No prob re the misunderstanding.

Ooh, ooh, now you have me really excited. I originally did want to use a drop-down menu for input value. Can you use a drop down menu for user-input values in a report? I mean, I am certain you can, I was just afraid it's too complicated. If it's not too complicated, can you explain? Thanks for your help!

 

[Newman]

Since the drug effects are still on , I'll let someone else tell you exactly how to do it.
...
But, I think that it's by calling a form from the query. In that form, just put the dropdown list.
The form will then ask for the month in letters and transfer it's numeric value to the report.
...
Then again, don't take my word for granted. I might be wrong.
Believe it or not I do mistake sometimes
CU
Newman

 

[Rich]

How do you decide which year the user wants to display?

 

[sxu-mic]

In July 2002 we're looking at data from July 2001 to July 2002. So the report for July 2002 already include info from 2001. Therefore, the report itself is only for the current year. That is why I don't ask for year.