DCount and LIKE Comparison

[Guest]

I have a form that prompts user for selection of user ID and Name that will
then search database for records that contain the users initials. The form
requires user to select the user information from drop-down Combo list (Field
name Combo29). Combo29 field has two parts, User-Init and User-Name, as
columns 0 and 1 of combo29. On selection and change, form then executes "ON
CLICK " and searchs database for matching records, code follows:


Private Sub Combo29_Click()
Me.unbtxt_User_Init = Me.Combo29.Column(0)
Me.unbtxt_User_Name = Me.Combo29.Column(1)
MsgBox " Typist_Init_Text = " & Combo29.Column(0)

If DCount("*", "DPS_FR_CASE_RECORDS", "TYPIST_INIT_TXT LIKE """ &
Me.Combo29 & "*""") > 0 Then
MsgBox " Matching Case Records Found "
DoCmd.runMacro "FRM-Search-By-Typist"
Else
MsgBox "No Records To Show", vbOKOnly, "No Records"
Cancel = True
Me.Form!Combo29.SetFocus
End If
End Sub

Problem is, what use to work no longer works. Even though the table
DPS_FR_CASE_RECORDS have records that field TYPIST_INIT_TXT has user initials
of BD in the field, the program does not locate matching records, and fails
to display due to no records found.

Can someone help me with fixing this issue, it worked at one time.
TYPIST_INIT_TXT is defined as text field, 3 bytes. and I use the LIKE to do
like comparisons to assist in location of say value of BD.

Any clues on resolution would be greatly appreciated.

Thanks in advance

 

[Guest]

I don't think it will make a different by try this syntax

If DCount("*", "DPS_FR_CASE_RECORDS", "TYPIST_INIT_TXT LIKE '" &
Combo29.Column(0) & "*'") then

I tried yours and it gave me the right result.

 

[Guest]

Ofer,

I do appreciate the assistance, but I could not get it to work until I
recoded the statement as follows:

If DCount("*", "DPS_FR_CASE_RECORDS", "TYPIST_INIT_TXT LIKE """ &
FindUser & "%""") > 0 Then
MsgBox " Matching Case Records Found "
DoCmd.runMacro "FRM-Search-By-Typist"

This allowed me to use a drop down list supplying names, as well as wildcard
the search. I guess the difference in JET table useage (this was a Mainframe
DB2 Table, that was being quiered) requires the use of the % sign. Any way,
the above works well for coding a wildcard search and DCount results set....

Thanks to the people who assist people like me, I learn a little more each
time I need to use this site.

Thanks again.