Syntax error - data format

[Guest]

I'm trying to make a form in which the user enters a parameter to search a
table with. The results would be displayed in a separate window. I'm a
total novice at coding, so I really don't know what I'm doing. I made a
combo box on my form, and the code is as follows;

Private Sub Combo2_AfterUpdate()

On Error GoTo ErrHandler

DoCmd.OpenForm "Selected", , , "ID # = " & Me!Combo2

Exit Sub

ErrHandler:

MsgBox "Error in Combo2_AfterUpdate( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub


When I run the form, the box appears and everything seems fine. But when I
select from the pulldown menu, a box pops up and tells me that there is a
"Syntax error in date in query expression 'ID # = 0102-55'. I want to define
the ID # as text, but Access thinks it's a date (mind, it looks like a date
but it's not supposed to be one). I'm at a loss of what to do. Any
suggestions?

Thanks,
Casa

 

[Douglas J Steele]

It's because of the space in your field name. I'd suggest renaming the field
so that it doesn't contain the # symbol: that can come back to bite you.

If you cannot rename the field, try

DoCmd.OpenForm "Selected", , , "[ID #] = " & Me!Combo2

 

[Guest]

Yes, the brackets helped. Thanks!

It's because of the space in your field name. I'd suggest renaming the field
so that it doesn't contain the # symbol: that can come back to bite you.

If you cannot rename the field, try

DoCmd.OpenForm "Selected", , , "[ID #] = " & Me!Combo2


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to make a form in which the user enters a parameter to search a
table with. The results would be displayed in a separate window. I'm a
total novice at coding, so I really don't know what I'm doing. I made a
combo box on my form, and the code is as follows;

Private Sub Combo2_AfterUpdate()

On Error GoTo ErrHandler

DoCmd.OpenForm "Selected", , , "ID # = " & Me!Combo2

Exit Sub

ErrHandler:

MsgBox "Error in Combo2_AfterUpdate( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub


When I run the form, the box appears and everything seems fine. But when I
select from the pulldown menu, a box pops up and tells me that there is a
"Syntax error in date in query expression 'ID # = 0102-55'. I want to define
the ID # as text, but Access thinks it's a date (mind, it looks like a date
but it's not supposed to be one). I'm at a loss of what to do. Any
suggestions?

Thanks,
Casa