Access Filter

[FontanaS]

I have a pop up menu that asks the user to pick a month (there are two columns - "January, 1", "February, 2", etc).

When the user picks a month, I want to use that number value for a report based only on the transactions for the month that the user used.

Can you filter a report based on a variable that a user picks from a form?

Combo6 is the name of the combo box in the form where the user picks the month.

I tried to do somehting like (in the openreport event):

DoCmd.ApplyFilter , "TransDate = ReportDateFilter"

Where the ReportDateFilter is what I assign the number that the user picked, and the TransDate is the Now date of the transaction that is in the query where the report comes from.

When I run it, it keeps asking me to put the date in.
I was thinking that I need to have a column in the query that takes the month from the transdate so the month is seperate.

Any Suggestions?

 

[gcole]

I am an old FoxPro guru. FoxPro had great filtering options. Access has it but I have had little luck with it. I recommend using an SQL statement. It is fast and works very well. Below is a sample of an SQL with a variable passed. In this case I am sending the results to a table for storage and to be used for history.

'define the variable to pass if needed.
m_table = "P" + m_smid + Mid(Date$, 1, 2) + Mid(Date$, 4, 2) + Mid(Date$, 9, 2)

sql_def = "SELECT DISTINCTROW vpforecast.* INTO " & m_table & " FROM forecast INNER JOIN holdsm ON forecast.sm_id = holdsm.sm_id;"

DoCmd.SetWarnings (False) 'turn overwrite warnings off
DoCmd.RunSQL (sql_def)
DoCmd.SetWarnings (True)
DoCmd.Close

 

[SimonDavis]

I haven't tried the above - but the followng works:

On the afterupdate properties for the combobox on the form, put the following code:

DoCmd.OpenReport "reportname", acPreview

On the report open event, put the following code:

DoCmd.ApplyFilter , "transdate = forms!formname!combo6.value"

(As I'm writing this, the above line has wrapped. Type it all on one line)

Assumptions (sorry, wasn't clear):

the name of the report is 'reportname'
The name of the form (with the combobox) is 'formname'
The field in the report containing the month is 'transdate', which is picked up form the filter already.

Hope this also works.