sysntax error, only in some versions

[chuck s.]

I get this msg, 'syntax error (missing operator) in query expression 'EID='
from the following code (expression in an 'on click'). The problem is, it
works fine in some environments, but not others.
When I run it on Vista 32 with Office 2007, I get the error - Office 2003, I
don't - same PC (DB is access 2000 version).
Vista 64 with Office 2003, no problem. It does appear to occur only in
2007, but not all the time. Other results (which makes me think it's
something in the code, because this is too random) -

· Client is using Office 2007, XP OS Service Pack 3=Syntax Error
· Same client, different computer, not sure of OS=No Error
· using Office 2007, Vista OS=Syntax Error
· using Office 2007, Vista OS=Syntax Error
· using Office 2007, XP OS Service Pack 2=No Error
also, XP SP3, Office 2007, no error.

Here's the code - Thanks in advance for any help.

Public Sub lstCurrent_Click()

On Error GoTo Err_Handler

If bDups Then
bDups = False
Exit Sub
End If
If Not IsBlank(IIf(Me.fraSearch = 1, Me.lstCurrent.Column(1),
Me.lstCurrent.Column(2))) Then
Me.RecordSource = "select * from tblEmployee where EID=" &
Me.lstCurrent.Column(0)
VisibleControls True

Call SetCertificationStatus
End If

Me.lstCurrent.SetFocus

Exit Sub
Err_Handler:
MsgBox Err.Description
End Sub

 

[JimBurke via AccessMonster.com]

I can't imagine that that error is due to a version - it's got to be data-
related. There must be times where Me.lstCurrent.Column(0) doesn't have a
value. You should never try to execute a SELECT statement that is using a
variable or control value without first making sure that the control/variable
has a value. I would add an If statement before that that makes sure it has a
value. The real problem is more than likely the rowsource - there must be
times where it is getting populated so that that column doesn't have a value.
Without knowing more about your app it's hard to say.

After looking at it a little more, you seem to checking either column(1) or
(2) for non-blank values, but not column(0). There must be times where (1) or
(2) is non-blank, but not (0). I would set a breakpoint there and do some
testing to see when that might be happening.

 

[JimBurke via AccessMonster.com]

I can't imagine that that error is due to a version - it's got to be data-
related. There must be times where Me.lstCurrent.Column(0) doesn't have a
value. You should never try to execute a SELECT statement that is using a
variable or control value without first making sure that the control/variable
has a value. I would add an If statement before that that makes sure it has a
value. The real problem is more than likely the rowsource - there must be
times where it is getting populated so that that column doesn't have a value.
Without knowing more about your app it's hard to say.

After looking at it a little more, you seem to checking either column(1) or
(2) for non-blank values, but not column(0). There must be times where (1) or
(2) is non-blank, but not (0). I would set a breakpoint there and do some
testing to see when that might be happening.

 

[chuck s.]

Thanks for your reply Jim - I am NOT a VB expert, and didn't write this - but
trying to debug it. It involves nothing more than clicking on a name in an
unbound listbox - and it doesn't matter which name. Like I said, I have a PC
with Vista Ultimate 32-bit, running both Office 03 and 07 - same exact DB -
works in the 03 and falls thru to the error msg in 07 - and it doesn't matter
which name I select.

 

[JimBurke via AccessMonster.com]

How does that list box get populated? Is it a query or a table or value list?
Somehow that column does not have data in some cases. If it's a table or a
query, I would check that out in both versions and see if there is a
difference in one version versus the other for whatever reason.

Thanks for your reply Jim - I am NOT a VB expert, and didn't write this - but
trying to debug it. It involves nothing more than clicking on a name in an
unbound listbox - and it doesn't matter which name. Like I said, I have a PC
with Vista Ultimate 32-bit, running both Office 03 and 07 - same exact DB -
works in the 03 and falls thru to the error msg in 07 - and it doesn't matter
which name I select.

I can't imagine that that error is due to a version - it's got to be data-
related. There must be times where Me.lstCurrent.Column(0) doesn't have a

[quoted text clipped - 51 lines]

 

[chuck s.]

I've checked the data in the table - EID is autonum. Here's the query. I
know what you're saying, but somehow it still doesn't make sense that it
works in some environs and not others. Thanks again for looking at this.

SELECT tblEmployee.EID, [tblEmployee].[ELastName] & ', ' &
[tblEmployee].[EFirstName] AS Employee, tblEmployee.ENum
FROM tblEmployee
WHERE (((tblEmployee.InActive)=False))
ORDER BY [tblEmployee].[ELastName] & ', ' & [tblEmployee].[EFirstName];