SUM query, variable problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I'm still fooling around with queries, just for the fun of it! Now I noticed
a problem and I don't know what causes it. I have a table named Table1 with
fields Firstname (text) and Money (double). I have the following two subs:

Sub QryTable1()

Dim f As String
f = "Money"
Dim db As Database
Dim qryDef As QueryDef
Dim strSQL As String
strSQL = "SELECT Firstname, SUM(' " & f & " ') AS TotalSum FROM Table1 GROUP
BY Firstname;"
Set db = CurrentDb
Set qryDef = db.CreateQueryDef("MyQuery1", strSQL)

End Sub

Sub QryTable2()

Dim db As Database
Dim qryDef As QueryDef
Dim strSQL As String
strSQL = "SELECT Firstname, SUM(Money) AS TotalSum FROM Table1 GROUP BY
Firstname;"
Set db = CurrentDb
Set qryDef = db.CreateQueryDef("MyQuery2", strSQL)

End Sub

Both of the subs should do the same thing, the upper just has a variable in
the strSQL. When I run the subs they both run normally, I don't get any error
messages. But when I try to open the queries only the second one opens and
shows me the datasheet view. The first one gives me an error message: "Data
type mismatch in criteria expression." I click help and get the following
explanation: "The criteria expression in a Find method is attempting to
compare a field with a value whose data type does not match the field’s data
type."

If I open the queries in design view, there is a difference. In the first
query the second field says: TotalSum: Sum('Money'), and in the second query
it is: TotalSum: Money.

Does anyone know why this happens?

-Beginner-
 
Lol, a rookie mistake? works when:

strSQL = "SELECT Firstname, SUM(" & f & ") AS TotalSum FROM Table1 GROUP BY
Firstname;"

Well at least I was able to fix it myself! :D

-Beginner-
 
You are putting quotes around the word "Money" in the first query. Assuming
the "F" is supposed to be a field name you should not have any quotes. Also
if your field names ever contain spaces or other non-alphanumeric characters
you might want to consider inserting brackets around the field name in the
query string

Dim f As String
f = "Money"
Dim db As Database
Dim qryDef As QueryDef
Dim strSQL As String
strSQL = "SELECT Firstname, SUM([" & f & "]) AS TotalSum " & _
:FROM Table1 GROUP BY Firstname;"
Set db = CurrentDb
Set qryDef = db.CreateQueryDef("MyQuery1", strSQL)

End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Good tip about the brackets, I'll start to use them immediately! Thanks!
-Beginner-

John Spencer said:
You are putting quotes around the word "Money" in the first query. Assuming
the "F" is supposed to be a field name you should not have any quotes. Also
if your field names ever contain spaces or other non-alphanumeric characters
you might want to consider inserting brackets around the field name in the
query string

Dim f As String
f = "Money"
Dim db As Database
Dim qryDef As QueryDef
Dim strSQL As String
strSQL = "SELECT Firstname, SUM([" & f & "]) AS TotalSum " & _
:FROM Table1 GROUP BY Firstname;"
Set db = CurrentDb
Set qryDef = db.CreateQueryDef("MyQuery1", strSQL)

End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Beginner said:
Hi!

I'm still fooling around with queries, just for the fun of it! Now I
noticed
a problem and I don't know what causes it. I have a table named Table1
with
fields Firstname (text) and Money (double). I have the following two subs:

Sub QryTable1()

Dim f As String
f = "Money"
Dim db As Database
Dim qryDef As QueryDef
Dim strSQL As String
strSQL = "SELECT Firstname, SUM(' " & f & " ') AS TotalSum FROM Table1
GROUP
BY Firstname;"
Set db = CurrentDb
Set qryDef = db.CreateQueryDef("MyQuery1", strSQL)

End Sub

Sub QryTable2()

Dim db As Database
Dim qryDef As QueryDef
Dim strSQL As String
strSQL = "SELECT Firstname, SUM(Money) AS TotalSum FROM Table1 GROUP BY
Firstname;"
Set db = CurrentDb
Set qryDef = db.CreateQueryDef("MyQuery2", strSQL)

End Sub

Both of the subs should do the same thing, the upper just has a variable
in
the strSQL. When I run the subs they both run normally, I don't get any
error
messages. But when I try to open the queries only the second one opens and
shows me the datasheet view. The first one gives me an error message:
"Data
type mismatch in criteria expression." I click help and get the following
explanation: "The criteria expression in a Find method is attempting to
compare a field with a value whose data type does not match the field's
data
type."

If I open the queries in design view, there is a difference. In the first
query the second field says: TotalSum: Sum('Money'), and in the second
query
it is: TotalSum: Money.

Does anyone know why this happens?

-Beginner-
Click to expand...
Click to expand...
 
Back
Top