Using a listbox variable in a query.

[rb9999]

I tried searching for solutions to my problem but I really don't know what to search for. Here is what I need to do.

I have a listbox that contains friendly names of data elements. These elements refer to larger tables of data. For example, the listbox may say "Total Hours" but when you select total hours, my listbox will report TOT_HOURS. Total Hours is the friendly name, and TOT_HOURS is the name of the field in the database.

I want the user to be able to select one of the friendly names and then run a canned crosstab query using the element they chose as the VALUE in the crosstab. So if they choose "Total Hours" they will get a crosstab report of TOT_HOURS. If they choose "Paid Hours" then they will get the same crosstab report, but this time the VALUE field will be PD_HOURS.

I have the listbox working properly but I cannot figure out how to use the returned results of the listbox as a field in a query. I understand the QBE grid a little better than SQL. But I do have an SQL string created by getting the SQL code of the query with a data element hard coded in it and replacing the data element in the hardcoded SQL with a variable representing the chosen listbox field.

So I can produce a SQL statement of the data that I want, but I don't know how to run it. I found info in the help regarding the RunSQL command but that seems to just run a SQL statement and not open a datasheet. I need to see the crosstab table of the query.

Any info greatly appreciated.

 

[kmclane]

You can use the format [Forms]![youeformname]![yourlistboxname].value in the criteria field of the grid. You can run the query in a onupdate event of the listbox, or you can add a command button and run the query from it's onclick event.
Hope this helps, Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[rb9999]

I tried what you suggested and got the following error. "The Microsoft Jet Database engine does not recognize '[Forms]![frmForm]![lstElements].[Value]' as a valid field name or expression."

Your post mentions to put the statement in the criteria field, but in the instance above I put it in the FIELD field. I want the chosen element in the listbox to be the summed value field of the crosstab query. If I took your suggestion and put it in the criteria field then what goes in the FIELD field?

Lets assume the following. My form is called frmForm, my listbox is lstElements. When I type ?[Forms]![frmForm]![lstElements] in a debug window I get FT_HOURS which is the name of the element in the main database that I want to sum in a crosstab.

Am I missing something? I still don't get it. Thanks for taking the time to respond. If you can clear this up I would appreciate it.

 

[kmclane]

You said you had SQL with the data element hard coded. Substitute my code for that hard coded data element in the sql statement and it will use whatever the current value is when run.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[rb9999]

I know I must be appearing dense here but I really appreciate your taking the time to help me out.

I have a function that creates a string consisting of the actual SQL of the query. But if I try to run it using the DoCmd.RunSQL I get a run time error. I am guessing I may need a querydef or something (I know nothing about these). I want to be able to run a query using the sql code and have it bring up a datasheet of the results.

Thanks again for all your time you have devoted to responding to this.

 

[kmclane]

Here is one more thing you can try, if you cna make your crosstab query with the wizard, using one of your criteria, save it then view SQL, replace the data element with the code from above. I've never had a lot of luck getting DoCmd.Runquery to work, but I have found that I can setup a macro to run my query, then save it as code and then call the function from a button. Let me know if that works. If not I'll mail you and see if I can help further.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[lespaul]

I don't think you will be giving any company secrets away if you show us how you are building your SQL string (replacing any identifing variable names with generic ones).

strSQL = 'SELECT F1, F2, F3 FROM TABLENAME WHERE SOMEFIELD = ' & somevariable & ';'

Leslie

 

[rb9999]

I tried everything I can. I tried RunSQL and RunQuery to no avail.

I can print the variable and the SQL is correct but I cannot run it or bring up results.

 

[rb9999]

OK, I have retooled everything so I can give you specifics.

I have a table called Testtable which includes the following fields

TESTTABLE
BLDG - Two digit building number
WND_DT - Weekending Date
DOW_CD - Day of Week Code (1=Sunday)
DATAELEMENT - A particular statistic I want to sum, consists of number.

This is the actual SQL code I get when click the SQL button while looking at the QBE grid:

TRANSFORM Sum(Testtable.DATAELEMENT) AS SumOfDATAELEMENT
SELECT Testtable.BLDG
FROM Testtable
GROUP BY Testtable.BLDG
PIVOT Testtable.WND_DT;

So you can actually reproduce my environment with this info.

Now this is the code that I have in a module that reproduces the exact same code:

Function SQLStatement() As String

SQLStatement = "TRANSFORM Sum(Testtable.DATAELEMENT) AS SumOfDATAELEMENT SELECT Testtable.BLDG FROM Testtable GROUP BY Testtable.BLDG PIVOT Testtable.WND_DT"

End Function

The only difference I see here is that the QBE SQL had a ; at the end and my code doesn't. When running it, it doesn't make any difference.

When I run this in debug mode by typing DoCmd.RunSQL SQLStatement I get "Runtime error '2342'. A RunSQL action requires an argument consisting of an SQL statement."
My SQL is one long string whereas the SQL produced by the query seem to be broken into separate lines. I don't know if this makes a difference or not.

Hope this helps.

Any suggestions greatly appreciated.

 

[kmclane]

Ok, a couple of things, I'm not sure but I think you need to Dim SQLStatement AS String on a separate line under your function like this:

Function SQLStatement()
Dim SQLStatement AS String
SQLStatement = "TRANSFORM Sum(Testtable.[Forms]![yourformname]![yourlistbox].[value]) AS SumOfDATAELEMENT SELECT Testtable.BLDG FROM Testtable GROUP BY Testtable.BLDG PIVOT Testtable.WND_DT"

End Function

Try that and let me know if it works. I have been in a conference call, so didn't have time to test it.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[rb9999]

When I do that I get:

Compile Error
"Duplicate declaration in current scope

 

[kmclane]

Are you declaring it some where else?
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[rb9999]

Yes, it is declared in the function declaration:

Function SQLStatement() As String

It is the line above "SQLStatement=" above.

 

[lespaul]

When I run this in debug mode by typing DoCmd.RunSQL SQLStatement I get "Runtime error '2342'. A RunSQL action requires an argument consisting of an SQL statement."

you have a FUNCTION named SQLStatement AND a STRING named SQLStatement

The error message above would make me think that if I renamed ONE of them, it might know which one you are trying to pass in.



Leslie

 

[kmclane]

If you see the revised code I posted for you, I removed the "as string" from your function.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[PHV]

1) Try this for your function:
Function SQLStatement() AS String
SQLStatement = "TRANSFORM Sum(Testtable." & Forms![yourformname]![yourlistbox].Value & ") AS SumOfDATAELEMENT SELECT Testtable.BLDG FROM Testtable GROUP BY Testtable.BLDG PIVOT Testtable.WND_DT"
End Function
2) the DoCmd.RunSQL method is valid only for ACTION queries, so you may consider playing with querydef or recordset:
Set querydef = CurrentDB.CreateQueryDef("", SQLStatement())

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244