Query Question

[Ted]

I am using a simple query attached to a linked table. I
want to limit the query to a specific field. The field is
4 characters long but the values in the field are 1-4
characters in length and this requires me to enter all 4
character positions to get the results I wnat.

Example:

Values in the table are 1234, 39, 3.

To display the records associated with the 3 value, I have
to enter <space><space><space>3 in my criteria. How can I
get it to accept any number of characters (1-4) and still
find the correct records.

I want to enter 3 or 39 and get only those records without
having to enter the appropriate number of preceeding
<spaces>.

I hope I have expplained this sufficiently and thanks for
any help.

Ted

 

[tina]

sounds like the field in question is a text field. you
could try using Like in your criteria, as

Like "*3"
or

Like "*39"
etc.

 

[Ted]

Yes, it is a text field and I understand the 'Like" in
the criteria and that's close but not quite what I need.

The examples you provided will return all records that
have end in 3 or 39. That means I get get batch 3 and
batch 103 records. I only want batch 3 records.

I am using a text box to prompt the user for input, so if
they enter 39, I want them to get only batch 39, not all
batches that end in 39.

Thanks for you your help

Ted

 

[tina]

good point. how about creating a calculated field in your
query, as

FindFrom: Trim(FieldName)

then put the criteria in the FindFrom field, instead of
the "real" field.

 

[Dan Artuso]

Hi,
Just massage the users input:

Dim strCriteria As string

Select Case Len(yourTextBox)
Case 1
strCriteria = " " & yourTextBox
Case 2
strCriteria = " " & yourTextBox
Case 3
strCriteria = " " & yourTextBox
Case 4
strCriteria = yourTextBox
End Select