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.

 

[Micron]

Between Chr(65) And Chr(90)
This is trying to evaluate if a string is between capital A and capital Z. Between can only be used for numbers and dates (& maybe strings that can evaluate to numbers, such as if 100 is stored as text). To use the between operator for this, the numerical range for the letters would have to be evaluated, not the strings A and Z. So
Between Asc("A") And Asc("Z") or a properly concatenated expression. However, you cannot use wildcards in numeric searches.

 

[xenou]

Between works on text. Maybe its used incorrectly in such contexts too much, but it can work.

 

[Micron]

Maybe M$ is guilty of omission...
https://support.office.com/en-us/article/Table-of-operators-e1bc04d5-8b76-429f-a252-e9223117d6bd
Between val1 And val2
Determines whether a numeric or date value is found within a range.
I have been lied to before...

 

[xenou]

Yes, you were lied to.
But you have to be careful with text, more so than with numbers.