Breaking data in a table without code

P

Paul

Hi,

I need some urgent help. I want to be able to delete some
data in a field using a query. I know this is a popular
ask on this forum, but I cannot use vb script and wondered
if there was another way round it. I understand some of
the statements in VB and will attempt to do it with good
instructions.

I want to be able to split the following:

05/02/2004 P/pca 3.2

In this example I only want to keep the date element from
the table and delete the rest. I would do this manually,
but there are around 3000 records in the file!!
ps the date is always the first piece of data in every
record.

Thank you kindly
 
J

Jeff Boyce

Paul

Is there ALWAYS (100%) a space after the date? If so, take a look at using
the Left() and InStr() functions to tell Access to find the string up to the
(first) space. If you can build a query that returns just this portion, you
can convert that query to an update query.

I'd urge you to save a backup copy before attempting this modification.
 
P

Paul

Hi Jeff,

Thanks for replying to my post.I have half an idea where
you are coming from. Where would I enter this and where
and how?
 
J

Jeff Boyce

Paul

Take a look in Access HELP at the exact syntax for Left() and InStr()
functions. You would create a new "field" in a query (top row of the query
grid). You would give it a name and enter the expression in this "cell".
This will return the value you are after. You may also need to use the
CDate() (convert to a date) function.

Here's a sample (your syntax may vary):

MyNewDate: CDate(Left([YourFieldName],InStr([YourFieldName]," ")-1))

When you run a query with this as a "field", and your table holding
[YourFieldName], the query looks for the first space in the field, backs up
1, takes all the characters to that point, then converts to a date. Note
all the places here that this can break -- this is why I mentioned "100%
Certain". If you don't have a space, if what's there isn't truly a date,
this fails... and it checks each row!
 
Top