Seperating parts of data

[Guest]

Hi,

If I have a field with instructions in.. How can I query on a certain part
of that field.

example

Delivery before 9am
Delivery after 1pm
Delivery before 12pm

How can I extract out only the "Before" part of the data in the field.

Thanks,
Jez.

 

[John Spencer]

I would suggest that you would be better off having this bit of information
in a separate field.

If you have to live with the instructions field as it is, you need to give
us more information to help solve this problem. And even then, the accuracy
of the solution may not be what you desire. Since human beings can do
something like
"deliver b4 9 in the morning"
You and I can understand that but the program won't unless you do some very
complex coding.

What is the "before" part? Do you mean you want to return "before 9am" and
"before 12pm" and ignore anything before or after those exact strings? Does
the instruction field contain other information before or after the phrase
you want to extract.

 

[Graham R Seach]

Jez,

If the words "before" and "after" ALWAYS appear in the same spot, you can
use Mid() and Instr() functions within a query:
SELECT Mid(Field1,Instr(1,Field1," ")+1, Instr(Instr(1,Field1,"
")+1,Field1," ")-Instr(1,Field1," ")-1) As BeforeOrAfter FROM tblMyTable

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia

 

[Jeff Boyce]

It sounds like your field has more than one fact in it (i.e., a time, and a
"modifier" like "before", "at", "after"). Having more than one fact in a
field makes for a lot more work on your part (and Access').

You could consider using the InStr() function to "look" for terms and the
(?if I recall) Value() to find the number.

Or you could separate the facts into their own fields, making much less
work.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Tom Ellison]

Dear Jer:

Perhaps you already realize it, but if separate portions of a field have
definite meaning like this, then they belong in a separate column of the
table. Placing more than one piece of information in a single column is a
violation of basic rules for database design.

It is not unlikely this happens to all of us. If we give users a "comment"
or "description" column, they will begin filing away information that is
important to them, but which they failed to mention at any point during the
design of their system.

So, if you do find a way to extract this information, it may be time to add
a new column and move the "Delivery time" information there.

Now, just how you should identify and parse out the information you need
depends on the reliability of the "format" in which users have typed it into
the database. If everything is as rosy as your post shows, this will be a
snap.

You should search the column for "Delivery before " and take the following 1
or 2 digits followed by am or pm.

If the users EVER put two spaces between "Delivery" and "before", or if they
ever misspell "Delivery" or just use "Deliver" instead, then you have a lot
more work to do.

In order to find which rows in the table have "Delivery before " in them,
use InStr(). This will give the position of the "D" in "Delivery before" as
an integer. That will be zero for rows that do not contain "Delivery before
". To that value, add the length of "Delivery before " which is 16 (check
my math here) and, with luck, you will be on the first digit of the time.
You will want the next 3 or 4 characters.

Now to the coding itself:

SELECT IIf(ColumnName LIKE "*Delivery before ##[ap]m*", Mid(ColumnName,
InStr(ColumnName, "Delivery before ") + 16, 4),
IIf(ColumnName LIKE "*Delivery before #[ap]m*", Mid(ColumnName,
InStr(ColumnName, "Delivery before ") + 16, 3), "Error")) AS Extract
FROM Table1
WHERE InStr(ColumnName, "Delivery before ") > 0

In the above, change Table1 to the actual name of your table and ColumnName
to the actual name of your column. Change Extract to whatever you wish to
name the new column.

Does this work at all?

Now, you may have users that occasionally record a Delivery in some other
fashion, however slight the difference may be. You could make a career of
finding every instance where they have done this, let alone predicting every
future way they could screw it up. There's no substitute for keeping the
data in your database clean and well qualified.

Tom Ellison