Field justification question - newbie

[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

 

[Guest]

Here are a couple things to try:
1) Use the trim function in your query - take a look at trim in Access help. It sounds like the data you are dealing with is text, and trim will strip out leading and trailing spaces.
2) Use one of the numeric conversion functions (CINT, CLNG, etc.) to convert the data to a numeric data type - the space issue will no longer be an problem.

If you still have a problem - post it here.

Scott Shearer MCSD, MCDBA
****msshearer @ @ @ hotmail.com****


----- Ted wrote: -----

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

 

[John Vinson]

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>.

How is the data being entered into the table? Do you need the leading
spaces? Ordinarily one would enter 3 just as 3, not as " 3"; and
it's easier to search if you do.

You can use a criterion of

WHERE fieldname LIKE "* " & [Enter value:] OR fieldname = [Enter
value:]

The LIKE clause will find those cases where there is at least one
leading space followed by the value, the = clause will find the
four-digit values with no leading blanks.

If you want to get rid of all the leading blanks in the table, run an
Update query updating the field to Trim([fieldname]).