Code to create query with calculated fields (1 Viewer)

[Mechele]

What is wrong the statement below:

Private Sub CmdFacilityMarketShare_Click()

Dim StrSQL As String
Dim QDF As QueryDef
Dim Market_Share As Double

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

Set QDF = CurrentDb.QueryDefs("Query2")
QDF.SQL = StrSQL
QDF.Close
RefreshDatabaseWindow
MakeQueryDef = True

End Sub

I'm getting a syntax error message..

[This message has been edited by Mechele (edited 04-30-2002).]

 

[Travis]

Add this line just before "Set QDF = CurrentDb.QueryDefs("Query2")"

Debug.Print StrSQL

Then copy and paste the SQL String from the "Imediate window" or "Debug Window" for those who have been around awhile

, into the SQL view of a new Query. The error should become obvious when you try and run the new query.