Need solution to read data in both capital/small letter

[Ashfaque]

Hi,

In one of the column of a table of my SQL Server contains around 500 employee names. Some of them are written in capital letters and some are not. Some of them with first character capital and rest all small.

I am using FE as MS Access. When user search the record thru a normal textbox (behind which I put small bunch of code to get the desired data in sub-form) user must enter searching name in the textbox in the same fashion the actual data available in the table.

e.g. let us say the employee name is John

User who searching John’s record must enter first letter capital otherwise it will not search. Why like this if table in on server.

This doesn’t happen when table is local in access. What is the solution to this?

All the suggestions are welcome.

With kind regards,
Ashfaque

 

[lightray]

Can you post the code behind your search box? lightray

 

[Ashfaque]

Yeah,

here is the piece of code.

Textbox name is TxtPatient in which user is entering first letter of searching name.

Private Sub TxtPatient_Change()
P_UpDtList TxtPatient.Text, TxtFamName
Me.TxtFamName = Null
End Sub


Private Sub P_UpDtList(ByVal PName As Variant, _
ByVal FName As Variant)
Dim Cdn As String, Cdn1 As String, Cdn2 As String

Cdn1 = IIf(Len(PName) > 0, "P_Name Like '" & "*" & _
PName & "*'", "")

Cdn2 = IIf(Len(FName) > 0, "P_FamilyName Like '" & _
FName & "*'", "")
Cdn = Cdn1 & IIf(Len(Cdn1) > 0 And Len(Cdn2) > 0, _
" And ", "") & Cdn2


Following code represents number of records picked up
If Len(Cdn) > 0 Then
SF_Patient.Form.Filter = Cdn
Else
SF_Patient.Form.Filter = "2 = 2"
End If

If Len(Cdn) > 0 Then
TxtCount = "Total Patients Found In List = " & DCount("*", _
"T_PatientMaster", Cdn)
Else
TxtCount = "Total Patients Found In List = " & DCount("*", _
"T_PatientMaster")
End If
End Sub

Above code also included for another text box search. Please refer only first one which 'txtPatient'

Hopefully it it clarified from my side.

With kind regards,
Ashfaque

 

[lightray]

Hi, sorry for the delay. I think you would need to use the StrConv Function
where you can convert your input to other types, vbUpperCase; vbLowerCase; vbProperCase; etc, check it out in Access Help, then process the string.

I haven't used it before so don't know that much about it.