Run-Time Error 3075 Error in Access 97

[ArcadeJr]

Good Morning All!

I am trying to run a report in where I am choosing from a List Box called AssetNumber from my form called Query_Menu. In that List box are a list of all the Asset Numbers that are on a table called Equipment.

In the Equipment Table, there is a field called Asset_Number, which at one time was a type Number, but I recently chaned it to type Text, due to that I needed the trailing "00" at the beginning of each Asset Number.

What I am trying to attempt is to be able to choose specific Asset Numbers from the list and have it appear on a report called Assets.

I was able to fix all the other reports but this one. I am getting the following error:

Run-time error '3075':
Syntax error (missing operator) in query expression '( Or(([Asset_Number]=004603 Or (([Asset_Number])=004604))'.

Here is the suspicious code that is giving me the trouble:

Private Sub AssetNumber_AfterUpdate()
Dim strSql As String
Dim intI As Integer
Dim strSelectedValues As String

strSql = "Select * from Equipment where ("
'Me.txtWhereClause = strSql

With Me.AssetNumber
For intI = 0 To .ListCount - 1
If .Selected(intI) Then
strSelectedValues = strSelectedValues & " Or " & "(([Asset_Number])= " & .ItemData(intI) & ")"
End If
Next intI
End With

'strSelectedValues = Trim(Right(strSelectedValues, Len(strSelectedValues) - 4)) - This one is taken out due to another Run-Time error #3464.

Me.txtWhereClause = strSql & strSelectedValues & ");"
End Sub


Any suggestions:

Jerry

 

[RoyVidar]

Text fields need for instance single quotes as delimiters:

[tt] strSelectedValues = strSelectedValues & " Or " & "(([Asset_Number])= '" & .ItemData(intI) & "')"[/tt]

Roy-Vidar

 

[ArcadeJr]

Still get the same error. And if you notive, I am missing the beginning part of the query "Select * from Equipment where ("

Also there should be no "OR" after that beginning part of the query (EX: '( Or(([Asset_Number]=004603 Or (([Asset_Number])=004604))'.)

Jerry

 

[PHV]

And what about something like this ?
strSql = "SELECT * FROM Equipment"
With Me!AssetNumber
For intI = 0 To .ListCount - 1
If .Selected(intI) Then
strSelectedValues = strSelectedValues & ",'" & .ItemData(intI) & "'"
End If
Next intI
End With
If strSelectedValues <> "" Then
strSelectedValues = " WHERE [Asset_Number] IN (" & Mid(strSelectedValues, 2) & ")"
End If
Me!txtWhereClause = strSql & strSelectedValues

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ArcadeJr]

That did the trick!

Thanks for your help!

Jerry