Help on a Query

  • Thread starter Thread starter Lou London
  • Start date Start date
L

Lou London

In an Access 97 data base, I have a field named Div-Deltic. It
contains data as shown

bet*qrgfd*aq
qvcd*acs*avc
ac*awec*aaet

There are thousands of records.
I need to create a query to be able to specify the first set of
characters up to but not including the first asterisk. The asterisk
can be in either the third, fourth or fifth position. The resultant
datasheet would then display the entire value in the field, for all
records that satisfy the criteria as well as the other fields
specified in the query.
I am sure this question is archived somewhere in Google, but I could
find it. Sorry if this is a duplicate question.
 
To extract the characters to the left of the first * character:

Left([Div-Deltic], InStr([Div-Deltic], "*") - 1)
 
In an Access 97 data base, I have a field named Div-Deltic. It
contains data as shown

bet*qrgfd*aq
qvcd*acs*avc
ac*awec*aaet

There are thousands of records.
I need to create a query to be able to specify the first set of
characters up to but not including the first asterisk. The asterisk
can be in either the third, fourth or fifth position. The resultant
datasheet would then display the entire value in the field, for all
records that satisfy the criteria as well as the other fields
specified in the query.
I am sure this question is archived somewhere in Google, but I could
find it. Sorry if this is a duplicate question.
Click to expand...

You can use the builtin string functions to do this: in a vacant Field
cell in the query type

Newfieldname: Left([Div-Deltic], InStr([Div-Deltic], "*") - 1)
 
Thanks gentlemen, this works great. One more question on this:
How could I set it up such that I could enter the first grouping of
characters, and only display the records that correspond to those
characters.
For example, I would like to enter bet as a parameter and disply all
the records that begin with bet*.
Your help is much appreciated.

John Vinson said:
In an Access 97 data base, I have a field named Div-Deltic. It
contains data as shown

bet*qrgfd*aq
qvcd*acs*avc
ac*awec*aaet

There are thousands of records.
I need to create a query to be able to specify the first set of
characters up to but not including the first asterisk. The asterisk
can be in either the third, fourth or fifth position. The resultant
datasheet would then display the entire value in the field, for all
records that satisfy the criteria as well as the other fields
specified in the query.
I am sure this question is archived somewhere in Google, but I could
find it. Sorry if this is a duplicate question.
Click to expand...

You can use the builtin string functions to do this: in a vacant Field
cell in the query type

Newfieldname: Left([Div-Deltic], InStr([Div-Deltic], "*") - 1)
Click to expand...
 
Thanks gentlemen, this works great. One more question on this:
How could I set it up such that I could enter the first grouping of
characters, and only display the records that correspond to those
characters.
For example, I would like to enter bet as a parameter and disply all
the records that begin with bet*.
Click to expand...

LIKE [Enter term:] & "*"
 
Back
Top