Using a Form for Parameters

[Guest]

I have queries which have SUMREV01 SUMREV02, etc. as fields. I'm creating a
monthly report in excel which pulls this Revenue data from access using
msquery. I want my boss to be able to select which month she'd like the data
for on a form in Excel and it pull the correct data. As the Revenues from
different months are different fields instead of criteria...I'm not sure how
to do this or if it's possible. I know I may have to use VBA but I'm not
extremely familiar with it? Is there any easier way or what is the simplest
way of accomplishing this?

Thanks!

 

[NickHK]

You mean SUMREV01 refers to January, SUMREV02 refers to February etc.. ?
Whilst this seems a rather strange way to structure you DB, ....
You will have construct the SQL for the query table and .refresh.

Assuming you have a multiselect list box with the month:
Dim SQLText as string
Dim i as Long

With lstMonth
For i=0 to .ListCount-1
If .List(i).Selected=True Then
SQLText=SQLText & .List(i) & ", "
End If
Next
End With
'Remove trail ", "
SQLText=Left(SQLText,Len(SQLText)-2)
SQLText="SELECT " & SQLText & " FROM WhateverTable WHERE SomeCondition=True"
With qtData
.CommandType = xlCmdSQL
.CommandText =SQLText
.Refresh
End With

 

[Guest]

Yes...I hate this part of the structure of the db. The company is extremely
complex but I still cannot understand their reasoning for creating it this
way. Can you explain how exactly I should restructure my queries (there are
like 100 of them)? I am creating the report for my boss and she will just be
using excel...she couldn't go into access and know enough to change the
periods and the report is extensive so I can't change every cell. I will no
longer be here in two weeks so I wanted to make it as easy for her as
possible. What exactly is the VBA you gave me doing? Sorry...I've only worked
with very basic VBA...

Thanks so much for your help.

 

[NickHK]

It just builds an SQL from the items selected in a list box that contains
all the month of the year.
You then need to refresh your query table with the new query.

If you are having trouble with this code, it might be easier for you just to
get all the months data and hide what you don't want.

NickHK