Selecting one Column from a query

[Guest]

My Imported table includes the following columns. Each of the month's
column's contain financial data. The column header is a string and not in
date format. I created a query with the following colunms:

Store# AcctType Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Nov Dec

I need the user to be able to select one of the columns (in other words) any
of the months. So, If the user selects February, the query will show only
three columns. Store# AcctType Feb

Currently, I am manually deleted all of the months from my query with the
exception of the month needed. Then I run my crosstab query and all is
wonderful.

How do I automate this process for my users? (Code sample would be
appreciated!)
#1. To prompt the user to select a specific month.
#2. To have the query's outcome contain the month selected.

********** What do you think about using a form with Option buttons
associated with each Month and writing code: Select then Create CrossTab for
each Case? ***************

 

[Guest]

This will do the trick - obviously adapt it as you like, replacing myQuery
and myTable with relevant names (you'll need a more complex SQL statement if
you're doing a crosstab query, easiest just to copy and paste from a query's
SQL view). You'll need to tick Microsoft DAO Object Library in Tools,
References:

Sub choosemonth()
On Error Goto handler
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
DoCmd.SetWarnings False
DoCmd.DeleteObject acQuery, "myQuery"
DoCmd.SetWarnings True
Set db = CurrentDb
Set qdf = db.CreateQueryDef("myQuery")
qdf.SQL = "SELECT Store#, AcctType , " & InputBox("Type month") & " FROM
myTable;"
DoCmd.OpenQuery "myQuery"
Set qdf = Nothing
Set db = Nothing
Exit Sub
handler:
If Err.Number = 7874 Then
Resume Next
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub

 

[Guest]

AWSOME! Thank you .... Sharon

This will do the trick - obviously adapt it as you like, replacing myQuery
and myTable with relevant names (you'll need a more complex SQL statement if
you're doing a crosstab query, easiest just to copy and paste from a query's
SQL view). You'll need to tick Microsoft DAO Object Library in Tools,
References:

Sub choosemonth()
On Error Goto handler
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
DoCmd.SetWarnings False
DoCmd.DeleteObject acQuery, "myQuery"
DoCmd.SetWarnings True
Set db = CurrentDb
Set qdf = db.CreateQueryDef("myQuery")
qdf.SQL = "SELECT Store#, AcctType , " & InputBox("Type month") & " FROM
myTable;"
DoCmd.OpenQuery "myQuery"
Set qdf = Nothing
Set db = Nothing
Exit Sub
handler:
If Err.Number = 7874 Then
Resume Next
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub

 

[Guest]

Martin,

I tried to figure it out using dim ReportMo as Variant, however, I'm stuck
again. How can I take the "My Query" with the Selected Month (Jan or Feb or
Mar...) and turn it into a Crosstab query using whatever month I selected?


Sharon

 

[Guest]

Sharon,
You need to change what goes between the quote marks after qdf.SQL=. I just
included a simple select query but what you could do is run the macro for a
particular month, change the resulting query into a crosstab query in the
usual way (from design view). Then you can switch to SQL view and copy the
new SQL into the macro. It's got to have quotes around it and VBA doesn't
like all the hard returns so you've got to put it all on one line with a
space in front of each capitalised word. Then (like I did before), change
Feb into " & InputBox("Type month") & " (including all the quotes). This is
what asks the user for the month (you need the quotes and the ampersands to
tack the answer to the inputbox into the SQL). Watch out: SQL is very
unforgiving on punctuation in the wrong place.

Good luck!

MartinInLondonEngland

P.S. I realise there's a problem if a user miss-spells a month as an
unwanted paramater gets made. To avoid this, you may want to set the
inputbox to a variable earlier on and use some code (e.g. with If or Select
Case) to check they have spelled the month right.