Breaking data in a table without code

  • Thread starter Thread starter Paul
  • Start date Start date
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
 
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.
 
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?
 
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!
 
Back
Top