mysterious Type mismatch in WHERE clause

[Richard Hollenbeck]

I can't figure out what is wrong. I commented out the WHERE clause and now
it works, though it shows all records because the WHERE clause is missing.
When I un-comment (') the WHERE clause I get a type mismatch. groupID is a
long integer and the listbox data is obviously a string. but when I tried
CLng([activities].[groupID]= etc... ) nothing was different. I still got
the same mismatch error.

Here's the code that still works, including my disabled WHERE clause:

Private Sub PopulateActivityList()
Dim dbActivities As DAO.Database, rsActivities As DAO.Recordset, StrSQL
As String

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight] AS [Weight] FROM [activities];" ' WHERE
[activities].[groupID] = '" & Me!groupID & "';"
Set dbActivities = CurrentDb()
Set rsActivities = dbActivities.OpenRecordset(StrSQL, dbOpenDynaset)

Set lstActivities.Recordset = rsActivities

Thanks,

Rich Hollenbeck

 

[Allen Browne]

Drop the extra quotes around Me!GroupID.
They are only needed if the field is a Text field (not a Number field).

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight] AS [Weight]
FROM [activities]
WHERE [activities].[groupID] = " & Me!groupID & ";"

 

[Richard Hollenbeck]

Allen, Thank you for your reply. I understand about the numeric fields not
needing the extra quotes. But here's what happens when I remove them:

Run-time error '3075':
Syntax error (missing operator) in query expression
'[activities].[groupID]='.

Since you already have the rest of this code, I'll just post the "corrected"
errant line of code:

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight]
AS [Weight]
FROM [activities]
WHERE [activities].[groupID] = " & Me!groupID & ";"

(this is all on one line but I broke it up for this post.)

This ought to be very simple, but for some reason it isn't. I suppose one
year from now, once I master the nuances of SQL, I will see that it really
is simple. It just doesn't seem so now. Thanks again for your help.

Rich Hollenbeck

Drop the extra quotes around Me!GroupID.
They are only needed if the field is a Text field (not a Number field).

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight] AS [Weight]
FROM [activities]
WHERE [activities].[groupID] = " & Me!groupID & ";"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I can't figure out what is wrong. I commented out the WHERE clause and now
it works, though it shows all records because the WHERE clause is missing.
When I un-comment (') the WHERE clause I get a type mismatch. groupID is
a
long integer and the listbox data is obviously a string. but when I tried
CLng([activities].[groupID]= etc... ) nothing was different. I still got
the same mismatch error.

Here's the code that still works, including my disabled WHERE clause:

Private Sub PopulateActivityList()
Dim dbActivities As DAO.Database, rsActivities As DAO.Recordset, StrSQL
As String

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight] AS [Weight] FROM [activities];" '
WHERE
[activities].[groupID] = '" & Me!groupID & "';"
Set dbActivities = CurrentDb()
Set rsActivities = dbActivities.OpenRecordset(StrSQL, dbOpenDynaset)

Set lstActivities.Recordset = rsActivities

 

[Allen Browne]

When it fails, open the Immediate window, and enter:
? strSQL
This should show you the contents of the string.
Anything strange?

For example:
- If Me!GroupID is null, the SQL string will be malformed.
- If Me!GroupID substitues a string value, the SQL won't make sense.

It is just remotely possible that the reference to Me!GroupID is corrupted
by Name AutoCorrect. Details:
http://members.iinet.net.au/~allenbrowne/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, Thank you for your reply. I understand about the numeric fields
not
needing the extra quotes. But here's what happens when I remove them:

Run-time error '3075':
Syntax error (missing operator) in query expression
'[activities].[groupID]='.

Since you already have the rest of this code, I'll just post the
"corrected"
errant line of code:

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight]
AS [Weight]
FROM [activities]
WHERE [activities].[groupID] = " & Me!groupID & ";"

(this is all on one line but I broke it up for this post.)

This ought to be very simple, but for some reason it isn't. I suppose one
year from now, once I master the nuances of SQL, I will see that it really
is simple. It just doesn't seem so now. Thanks again for your help.

Rich Hollenbeck

Drop the extra quotes around Me!GroupID.
They are only needed if the field is a Text field (not a Number field).

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight] AS [Weight]
FROM [activities]
WHERE [activities].[groupID] = " & Me!groupID & ";"

I can't figure out what is wrong. I commented out the WHERE clause and now
it works, though it shows all records because the WHERE clause is missing.
When I un-comment (') the WHERE clause I get a type mismatch. groupID is
a
long integer and the listbox data is obviously a string. but when I tried
CLng([activities].[groupID]= etc... ) nothing was different. I still got
the same mismatch error.

Here's the code that still works, including my disabled WHERE clause:

Private Sub PopulateActivityList()
Dim dbActivities As DAO.Database, rsActivities As DAO.Recordset, StrSQL
As String

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight] AS [Weight] FROM [activities];" '
WHERE
[activities].[groupID] = '" & Me!groupID & "';"
Set dbActivities = CurrentDb()
Set rsActivities = dbActivities.OpenRecordset(StrSQL, dbOpenDynaset)

Set lstActivities.Recordset = rsActivities

 

[Richard Hollenbeck]

Thanks. I went to that website you showed me then I turned off Name
AutoCorect. Still, my program doesn't see what's in the WHERE clause. So I
tried something else:

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight] AS [Weight] FROM [activities] WHERE
[activities].[groupID] = " & Me.groupID

This works and I didn't even need to mess around the semicolon! Thanks
again.

When it fails, open the Immediate window, and enter:
? strSQL
This should show you the contents of the string.
Anything strange?

For example:
- If Me!GroupID is null, the SQL string will be malformed.
- If Me!GroupID substitues a string value, the SQL won't make sense.

It is just remotely possible that the reference to Me!GroupID is corrupted
by Name AutoCorrect. Details:
http://members.iinet.net.au/~allenbrowne/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, Thank you for your reply. I understand about the numeric fields
not
needing the extra quotes. But here's what happens when I remove them:

Run-time error '3075':
Syntax error (missing operator) in query expression
'[activities].[groupID]='.

Since you already have the rest of this code, I'll just post the
"corrected"
errant line of code:

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight]
AS [Weight]
FROM [activities]
WHERE [activities].[groupID] = " & Me!groupID & ";"

(this is all on one line but I broke it up for this post.)

This ought to be very simple, but for some reason it isn't. I suppose one
year from now, once I master the nuances of SQL, I will see that it really
is simple. It just doesn't seem so now. Thanks again for your help.

Rich Hollenbeck

Drop the extra quotes around Me!GroupID.
They are only needed if the field is a Text field (not a Number field).

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight] AS [Weight]
FROM [activities]
WHERE [activities].[groupID] = " & Me!groupID & ";"


I can't figure out what is wrong. I commented out the WHERE clause

and
now

it works, though it shows all records because the WHERE clause is missing.
When I un-comment (') the WHERE clause I get a type mismatch.

groupID
is

a
long integer and the listbox data is obviously a string. but when I tried
CLng([activities].[groupID]= etc... ) nothing was different. I

still
got

the same mismatch error.

Here's the code that still works, including my disabled WHERE clause:

Private Sub PopulateActivityList()
Dim dbActivities As DAO.Database, rsActivities As DAO.Recordset, StrSQL
As String

StrSQL = "SELECT [activities].[activityID] as [ID],
[activities].[activityDescription] AS [Activity],
[activities].[targetActivityWeight] AS [Weight] FROM [activities];" '
WHERE
[activities].[groupID] = '" & Me!groupID & "';"
Set dbActivities = CurrentDb()
Set rsActivities = dbActivities.OpenRecordset(StrSQL, dbOpenDynaset)

Set lstActivities.Recordset = rsActivities