SQL Statement to Create Query (1 Viewer)

[Mechele]

What is wrong with the following?

Private Sub CmdFacilityMarketShare_Click()

Dim qdf as QueryDef
Dim StrSQL as String
Dim Query1 As QueryDef

Set qdf = CurrentDb.CreateQueryDef ("Query1)

StrSQL = "SELECT Query1.TxtCounty AS County, Query1.[Zip Code], Query1.[Post Office Name] AS City," _
& "Query1.LngIntHospID AS [Hospital ID], Sum(Query1.LngIntPatientCount) AS [Patient Count]," _
& "Sum(Query1.LngIntLOS) AS LOS, SUM(Query1.LngIntTotalCharges) AS [Total Charges]" _
& "FROM Query1 GROUP BY Query1.TxtCounty, Query1.[Zip Code], Query1.[Post Office Name]," _
& "Query1.LngIntHospID;"

qdf.SQL = StrSQL
qdf.close
RefreshDatabaseWindow
End Sub

 

[David R]

Set qdf = CurrentDb.CreateQueryDef ("Query1)
needs another set of quotes...

But if your SQL is static, why are you overwriting it here?

[This message has been edited by David R (edited 04-26-2002).]

 

[Mechele]

Forgive me for sounding really stupid here. But, what do you mean by overwriting the SQL statement?

What I am trying to achieve is to create a form where the user can create queries from a set of list/combo boxes. Once they make their choices, the SQL statements should create a query. Then, a report will be created bound to the query that was created.

 

[Mechele]

Okay, Okay!!!! I'm really not as stupid as I'm currently sounding. But, I now know what you're telling me..Forgive me, but I'm under alot of pressure here!!! I'm doing several projects at the same time!!!!

 

[David R]

I think your syntax is way off. Go back to the CreateQueryDef example and compare it to what you have again. You're currently:
Setting Query1 as the (blank) QueryDef qdf.
Building SQL off of that blank Query.
Saving that SQL as the QueryDef qdf.
Refreshing the database window by means of a function I've never seen before and can't find in Access help.

Additionally, I don't see where in your SQL building you reference the combo boxes from your form, so I don't know what you're building it from.

Are your listboxes single-select or multi-select? I can probably give you a code snippet that will create a query from them if I have a bit more information.

 

[glene77is]

I am new to Access, and chose a simple method. I invite comments.

I have done an app with 8 parameterized queries, implemented like this :
(1) The form has textboxes and comboboxes to assign values to variables.
(2) I commandbutton call a Report which is data bound to a query.
(3) the query expects these several variables.
This runs OK and is similar to what I've done with FoxPro for 20 years. I am making use of several 'parameterized queries'.
So, please comment about the advantages of Michele's general method.

 

[David R]

I wasn't speaking of "advantages" per se, but in Access, as in most programs, there are several ways to skin the cat depending on what you are doing.

To get parameters for a query, you can just include [Enter data:] parameters and it will do a series of parameter popups until you give it all the data it wants, then run the report/query/etc. This works but gets old if you've got several parameters. So...

You can build a small unbound form and put your 'questions' on there with text boxes, calendar controls, or whatever. Then your query refers to them with Forms!FormName.txtAnswer1, etc... But that only works if each question can have one answer. So...

Multi-select listboxes allow you to choose one or several things from a list. They don't store this anywhere, but you can loop through the list to build a SQL statement that will search for those one-or-many things.

Your 'parameterized' queries sound a lot like the second method here. There are others I'm sure, like reading variables from global variables or .INI files, but they're rather advanced for our purposes here.