syntax error

[tizwaz]

I have the following code on a search button on one of my forms. It runs ok if I leave the 1st search box (filenumber) blank but if I put something in there eg 8 I get a run time error 3075 syntax error in the expression '[filenumber] like "*8*'

Can anyone see what I am doing wrong?

 

[JerryKlmns]

Although you haven' t post your expression in full, is this correct '[filenumber] like [red]"[/red]*8*'
or should be changed to '[filenumber] like [red]"[/red]*8*[red]"[/red]'

 

[tizwaz]

sorry forgot to post code
The error is the 1st one

Private Sub Command8_Click()
Dim rcd As DAo.Recordset, db As Database
Dim gstrwherefile As String
gstrwherefile = ""

If Not IsNothing(Me!txtFileNumber) Then

gstrwherefile = "[filenumber] like " & Chr$(34) & "*" & Me!txtFileNumber
If Right$(Me!txtFileNumber, 1) = "*" Then
gstrwherefile = gstrwherefile & Chr$(34)
Else
gstrwherefile = gstrwherefile & "*"


End If
End If

If Not IsNothing(Me!txtFileTitle) Then
If IsNothing(gstrwherefile) Then
gstrwherefile = "[FileTitle] like " & Chr$(34) & "*" & Me!txtFileTitle

Else

gstrwherefile = gstrwherefile & " AND [FileTitle] like " & Chr$(34) & "*" & Me!txtFileTitle

End If

If Right$(Me!txtFileTitle, 1) = "*" Then
gstrwherefile = gstrwherefile & Chr$(34)

Else

gstrwherefile = gstrwherefile & "*" & Chr$(34)

End If
End If

If Not IsNothing(Me!txtFilePosition) Then
If IsNothing(gstrwherefile) Then
gstrwherefile = "[FilePosition] like " & Chr$(34) & "*" & Me!txtFilePosition

Else

gstrwherefile = gstrwherefile & " AND [FilePosition] like " & Chr$(34) & "*" & Me!txtFilePosition

End If

If Right$(Me!txtFilePosition, 1) = "*" Then
gstrwherefile = gstrwherefile & Chr$(34)
Else
gstrwherefile = gstrwherefile & "*" & Chr$(34)

End If
End If

If Not IsNothing(Me!txtDate1) Then
If IsNothing(gstrwherefile) Then
gstrwherefile = "[FileDate] >= " & "#" & Format(Me!txtDate1, "dd/mm/yyyy") & "#"
Else
gstrwherefile = gstrwherefile & " AND [FileDate] >= " & "#" & Format(Me!txtDate1, "dd/mm/yyyy") & "#"
End If
End If

If Not IsNothing(Me!txtDate2) Then
If IsNothing(gstrwherefile) Then
gstrwherefile = "[filedate] <= " & "#" & Format(Me!txtDate2, "dd/mm/yyyy") & "#"
Else
gstrwherefile = gstrwherefile & " AND [filedate] <= " & "#" & Format(Me!txtDate2, "dd/mm/yyyy") & "#"

End If

End If

DoCmd.OpenForm ("frmSearchResults")
DoCmd.ApplyFilter , gstrwherefile
DoCmd.Close acForm, "frmSearch"
Exit Sub
End Sub

 

[RoyVidar]

What datatype is filenumber?

If it's numeric, try:

[tt]gstrwherefile = "[filenumber] = " & Me!txtFileNumber[/tt]

i e without text delimiters and like operator

Roy-Vidar

 

[PHV]

And for text values, no need of all this If ... Else ... End If, eg :
gstrwherefile = "Nz([FilePosition], '') Like '*" & Me!txtFilePosition & "*'"

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

 

[HarleyQuinn]

And in the very first IF statement you have missed off the Chr$(34)

If Not IsNothing(Me!txtFileNumber) Then

gstrwherefile = "[filenumber] like " & Chr$(34) & "*" & Me!txtFileNumber
If Right$(Me!txtFileNumber, 1) = "*" Then
gstrwherefile = gstrwherefile & Chr$(34)
Else
gstrwherefile = gstrwherefile & "*"[red] & Chr$(34)[/red]

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[tizwaz]

Thanks all - its working fine now