VBA Search Code

[toleafs]

Hi, any help is greatly appreciated..... I have a dropdown field in a form and a search command button to search within a form. See code below

Private Sub CmdLetterSearch_Click()
Dim LSQLs As String
Dim LSearchStrings As String
Dim RecordCounts As String
Dim lRecCounts As Integer

If Len(cboLetter) = 0 Or IsNull(cboLetter) = True Then
MsgBox "You must enter a search string."


Else

LSearchStrings = cboLetter

'Filter results based on search string
LSQLs = "select * from qRECS"
LSQLs = LSQLs & " where strLetterIndentification = '*" & LSearchStrings & "*'"


Form_frmRECS.RecordSource = LSQLs

lblLetter.Caption = "Letter Identification Details: Filtered by: '" & LSearchStrings & "."

'Clear search string
cboLetter = ""

MsgBox "Results have been filtered. For Letter Identfication Number: " & LSearchStrings & " "

The issue is that the records that can be chosen from the dropdown field may contain symbols ( &, !, *) alonf with text and numbers. The code I have does not filter those out.

Thanks in advance for your help.

 

[igold]

Hi,

How about if instead of a *wildcard as your first criteria for "strLetterIdentification", your first criteria is that the Chr() of the cboLetter must be between Chr(65)-Chr(90).

 

[toleafs]

thanks igold..... i made the change as per your recommendation but now getting a Runtime error 3075, syntax error that highlight codeline: Form_frmRECS.RecordSource = LSQLs. Any thoughts?

 

[igold]

I am sorry I don't. I am not familiar with the error codes. Perhaps re-check of the correctness of the Chr() range that represent "A-Z".

Good luck.

 

[xenou]

thanks igold..... i made the change as per your recommendation but now getting a Runtime error 3075, syntax error that highlight codeline: Form_frmRECS.RecordSource = LSQLs. Any thoughts?

Exactly what changes did you make? Not clear how you implemented changes suggested.

 

[toleafs]

The following line: LSQLs = LSQLs & " WHERE strLetterIndentification Between Chr(65) And Chr(90) & LSearchStrings & " * ""

I know get Runtime error 13; type mismatch

thanks

 

[xenou]

When you reply, click go advanced, hit the Php button, and paste your code in the php code box.

In general, remember that what goes in the record source must be raw sql, so always print it out and look at it to make sure it's correct. It looks like you have a vba variable name in your sql text, which is not what you want.

 

[igold]

I don't write VBA for Access, but it seems to me that the logic is off. I think you would have to first make sure the Chr(cboLetter) falls into the correct range, then convert it back to a letter and then surround with wildcards and the rest of your criteria...

 

[toleafs]

thanks all for your help.... got it to work with a slight change the code line as follows: LSQLs = LSQLs & " where strLetterIndentification = '" & LSearchStrings & "'"

 

[igold]

Not sure if I helped or xenou helped, but either way I am glad you got it working...