DCount compare table.textfield to form.text field question

Can someone tell me what I'm doing incorrectly. I have a table named
TST_FR_CASE_RECORD that has a field named TYPIST_INIT_TXT. This field is
defined as a Data Type = Text field to IBM DB2, and it has a field size of 3
positions. Sometimes this field may have a full three positions of text in
it, some times it may have two positions of text, left justified.

I have a form that requires the user to select from a drop-down list any of
multiple
possible values for this field, (user initials), some may have 2 initials
only
for their id some have 3 initials. I wanted to use the DCount function to
locate all
records in this table for specific users, requiring them to select a value
from this
supplied list of initials. The comparison was failing on example 1 below,
with the
message that the following error box:

Microsoft Visual Basic
Run-time error 2001
You canceled the previous operation
CONTINUE - END - DEBUG - HELP were its message box options

Example 1 below:

If DCount("*", "TST_FR_CASE_RECORDS", "[TYPIST_INIT_TXT]= " & Me.
[Combo23]) 0 Then
MsgBox " Matching Records found "
DoCmd.RunMacro "Search_By_Typist"
Else
MsgBox "No Records To Show", vbOKOnly, "No Records"
Cancel = True
Me.Form!Combo23.SetFocus
End If

-------------------------------------------------------------------

When I clicked on END it takes me out without Debugging the ERROR, when
I clicked on DEBUG it took me to the line of code below:

If DCount("*", "TST_FR_CASE_RECORDS", "[TYPIST_INIT_TXT]= " &
Me.[Combo23]) 0 Then

Access was highlighting the line (above) in yellow as an error.

I struggled with this code until I ended up re-writting it with the
following code
(Example 2) which worked.


Example 2:

If DCount("*", "TST_FR_CASE_RECORDS", "TYPIST_INIT_TXT LIKE """ &
Me.Combo23 & "*""") 0 Then
MsgBox " Matching Records found "
DoCmd.RunMacro "Search_By_Typist"
Else
MsgBox "No Records To Show", vbOKOnly, "No Records"
Cancel = True
Me.Form!Combo23.SetFocus
End If

Maybe it would help if our MVP people could put a examples of code on
display at this website that would show people like me how to properly use
DCount ("*"
when the the field being tested was (TEXT field in a table, being compared
to a TEXT field or Combo box on a form that allows alpha only); a (TEXT
field in a table,
being compared to a text field or combo box that allows only numerical values.

Also, could someone show us how to compare a 3 Digit Text(Alpha only) field
to a form text field that allows only alpha characters, but say the user only
enterred the
following vaules.

Field has 'CB with a Space' in the field, but user entered a comparative
search
on the value of 'space CB' or ' CB' value. Is there a bit of code to remove
(if needed)
the preceding spaces so that CB is the only value to compare on.


Back to the original question. Is example 2 above the correct way to code for
comparisons of Text field in table against text field on a form, or is there
a better
way to do it. If so, could someone reflect how to do it correctly.

Thanks.
--
Robert Nusz
Sr. Programmer Analyst II

Comments inline.....

"RNUSZ@OKDPS" wrote:

Can someone tell me what I'm doing incorrectly. I have a table named
TST_FR_CASE_RECORD that has a field named TYPIST_INIT_TXT. This field is
defined as a Data Type = Text field to IBM DB2, and it has a field size of 3
positions. Sometimes this field may have a full three positions of text in
it, some times it may have two positions of text, left justified.

I have a form that requires the user to select from a drop-down list any of
multiple
possible values for this field, (user initials), some may have 2 initials
only
for their id some have 3 initials. I wanted to use the DCount function to
locate all
records in this table for specific users, requiring them to select a value
from this
supplied list of initials. The comparison was failing on example 1 below,
with the
message that the following error box:

Microsoft Visual Basic
Run-time error 2001
You canceled the previous operation
CONTINUE - END - DEBUG - HELP were its message box options

Example 1 below:

If DCount("*", "TST_FR_CASE_RECORDS", "[TYPIST_INIT_TXT]= " & Me.
[Combo23]) 0 Then
---snip---

Almost right - just two changes. Since Me.[Combo23] is text, it needs to be
enclosed in quotes (single or double) - a single quote after the equal sign
and an ampersand /double quote/single qoute/close parn/double quote after
Me.[Combo23].

Here is the corrected line:

If DCount("*", "TST_FR_CASE_RECORDS", "[TYPIST_INIT_TXT]= '" & Me.[Combo23]
& "')" 0 Then

Here is the last part expanded to show the single quotes:

"[TYPIST_INIT_TXT]= ' " & Me.[Combo23] & " ' ) " 0


---snip----
I struggled with this code until I ended up re-writting it with the
following code
(Example 2) which worked.


Example 2:

If DCount("*", "TST_FR_CASE_RECORDS", "TYPIST_INIT_TXT LIKE """ &
Me.Combo23 & "*""") 0 Then
---snip----

In this example there are the correct number of quotes and the last ampersand.

---snip----

Also, could someone show us how to compare a 3 Digit Text(Alpha only) field
to a form text field that allows only alpha characters, but say the user only
enterred the
following vaules.

Field has 'CB with a Space' in the field, but user entered a comparative
search
on the value of 'space CB' or ' CB' value. Is there a bit of code to remove
(if needed)
the preceding spaces so that CB is the only value to compare on.


To remove leading and/or trailing spaces use the Trim() function:


replace Me.[Combo23] with Trim(Me.[Combo23])



Back to the original question. Is example 2 above the correct way to code for
comparisons of Text field in table against text field on a form, or is there
a better
way to do it. If so, could someone reflect how to do it correctly.


The difference between example 1 and example 2 is that example 1 is an exact
match.

For example, if you had records where the initials were

"AB"
"ABC"
"ABD"

and Combo23 value was "AB", example 1 ("equals") would return 1 and example
2 ("like") would return 3.

Which one is better? I think it depends on what you are trying to
accomplish. Counting the number of users initials, I would use example 1
syntax ("equals").

Thanks.
--
Robert Nusz
Sr. Programmer Analyst II


HTH
---
SteveS
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)