"between" syntax error when searching

[pauljkeenan]

Hi Guys

Im getting unusual errors when tryint to search for records between two reference numbers. Here is the code for search

Private Sub cmdShowRefRecs_Click()

Dim strSQL1 As String, strOrder1 As String, strWhere1 As String

strSQL1 = "SELECT qrySearchVisaSub.Ref_No, qrySearchVisaSub.Name, qrySearchVisaSub.App_Date, qrySearchVisaSub.Issue_Date, qrySearchVisaSub.Visa_Type, qrySearchVisaSub.Fee, qrySearchVisaSub.Currency, qrySearchVisaSub.Del_Sanc, qrySearchVisaSub.Nationality, qrySearchVisaSub.DateOfBirth, qrySearchVisaSub.Passport_No, qrySearchVisaSub.Sticker_No " & _
"FROM qrySearchVisaSub"

strWhere1 = "WHERE"

strOrder1 = "ORDER BY qrySearchVisaSub.Ref_No;"

If Not IsNull(Me.fromRefNo) And Not IsNull(Me.toRefNo) Then
strWhere1 = strWhere1 & " (qrySearchVisaSub.Ref_No) between '*" & Me.fromRefNo & "*' and '*" & Me.toRefNo & "*' AND"
End If


'Remove the last AND from the SQL statment
strWhere1 = Mid(strWhere1, 1, Len(strWhere1) - 5)

'Pass the SQL to the RowSource of the listbox
Me!frmVisaSearchSub.Form.RecordSource = strSQL1 & " " & strWhere1 & "" & strOrder1
Me!frmVisaSearchSub.Form.Requery

End Sub

I think it must have something to do with the between statement itself, because when I moved the final "AND" out a space I got a syntax error, when I move it back in a space, I get "you cancelled the previous operation" error.

Is the syntax correct then for such a search?

Thanking you in advance

 

[kjv1611]

Have you tried removing the asterisks?

 

[pauljkeenan]

Yes I have, Ive tried most combinations at this stage but always the same runtime error 2001, you cancelled the previous operation.

Id like to meet the guy who writes the descriptions for error codes.

thanks for the tip though, something simple like that is probably the answer

 

[kjv1611]

What about this portion?

'Pass the SQL to the RowSource of the listbox
Me!frmVisaSearchSub.Form.RecordSource = strSQL1 & " " & strWhere1 & "" & strOrder1
Me!frmVisaSearchSub.Form.Requery

The part: ...strWhere1 & "" & strOrder1...

Maybe take out the & "" &
or either put a space in the ""

 

[pauljkeenan]

well this is where the debugger is pointing me to but I used the exact same statement for another search form and it worked fine.

I messed around with the quotes and whatnot but same ol same ol.

 

[Eupher]

Three suggestions:

1) Comment out your error handling so you can see exactly where the code breaks
2) Put a Debug.Print strSQL line in right after your final contatenation, so you can see what your final SQL statement really looks like once it's all put together
3) Are the RefNo's alphabetic or numeric? If numeric, omit the single quote delimiters surrounding the field references

HTH,

Ken S.

 

[Eupher]

p.s. if you've already done #1, disregard that and show us what line the break occurs on...

Ken S.

 

[pauljkeenan]

Thanks a million Ken,

the problem was the single quote delimiters, I thought I had removed them earlier when messing around with it but I guess not.

Cheers for that, youre a life-saver