query with displaying data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a cell that the length varies and I need to display from the end of
the data up to the space if the 1st position of a cell is "I or N".

Ex: I NN0NN
N NNNNNN
Y YN000N

I would need to display the first 2 cells but not the 3rd.
 
I created a table (Table 1) with your three examples, the field I called
"Data_stuff"

First issue - filtering records that start with an I or N: That Data Stuff
Column has to have a criteria that says

Left([Data_Stuff],1) In ("I","N")

You could say <> to "Y", but I don;t know if you have more possibilities.

Second issue - Isolating the data after the I or N: I'm assuming that there
will ALWAYS be a space separating your flag from the actual data, so what you
need to do is use the InStr function to find the location of the space. The
following expression does this:

InStr(1,[Data_Stuff]," ")

This expression has to be sued to determine how many characters from the
RIGHT of your field you want to keep. To do this subtract the result from
the InStr function from the length of the field. This will leave you with a
leading space, so I put the whole thing in the trim function. The resulting
expression is:

Trim(Right([Data_Stuff],Len([Data_Stuff])-InStr(1,[Data_Stuff]," ")))

The final query I get is this:

SELECT Trim(Right([Data_Stuff],Len([Data_Stuff])-InStr(1,[Data_Stuff]," ")))
AS Displayed_Data
FROM Table1
WHERE ((Left([Data_Stuff],1) In ("I","N")));

HTH.
 
I have a cell that the length varies and I need to display from the end of
the data up to the space if the 1st position of a cell is "I or N".

Ex: I NN0NN
N NNNNNN
Y YN000N

I would need to display the first 2 cells but not the 3rd.
Click to expand...

You say "cell" - are you using Excel (which uses cells) or Access
(which calls them fields)?

If it's Access, use a Query with a criterion of

LIKE "I*" OR LIKE "N*"

to match only the first character of the value.

Note that storing multiple values in one field is a violation of
relational principles and WILL make your life more difficult!

John W. Vinson[MVP]
 
I did this and it only is displaying the first value not the ending values
which I also need. (ie I NN0NN).
 
I found one more issue that maybe you can maybe tweek this query. Can I also
say that if the 1st is = I or N and the subsequent values after the space are
<>N then I can display the full row of data.
 
Back
Top