Query to delete characters to the right of a decimal point

L

Leslie M

I need to write a query to help me clean up some letter codes in my database.

The codes could have any of the following formats...

XY001.01
AP002
SDFJ003.01
JUOI005

I would like the query to return everything to the left of the decimal
point, so the data returned would look like this...

XY001
AP002
SDFJ003
JUOI005

Thanks in advance for your help.
 
J

Jeff Boyce

But didn't you give an example that has NO period in it?

One way to approach this would be to use an IIF() statement that checks for
a ".", perhaps something like:

NewField:
IIF(InStr([YourCode],".")>0,Left([YourCode],InStr([YourCode],".")-1),[YourCode])

Put this as a new field in a query that includes the table in which this
field is found. Actual syntax may vary.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

Jerry Whittle

First use the InStr function to find the period. Then use the Left Function
to trim off the period and stuff after it. Change TheField to the actual
field name.

NoDecimals: IIf(InStr([TheField],".") >0,
Left([TheField],InStr([TheField],".")-1) , ([TheField])

As the Left function will cause an error if it can't find a period, use an
IIf statement to fix only those with a period. Otherwise use the original
field.
 
J

John Spencer

The Following should work for you

Left(
Code: 
 , Instr(1,[Code] & ".",".")-1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
L

Leslie M

Thanks Jeff. It worked like a charm!
--
Leslie M


Jeff Boyce said:
But didn't you give an example that has NO period in it?

One way to approach this would be to use an IIF() statement that checks for
a ".", perhaps something like:

NewField:
IIF(InStr([YourCode],".")>0,Left([YourCode],InStr([YourCode],".")-1),[YourCode])

Put this as a new field in a query that includes the table in which this
field is found. Actual syntax may vary.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Leslie M said:
I need to write a query to help me clean up some letter codes in my
database.

The codes could have any of the following formats...

XY001.01
AP002
SDFJ003.01
JUOI005

I would like the query to return everything to the left of the decimal
point, so the data returned would look like this...

XY001
AP002
SDFJ003
JUOI005

Thanks in advance for your help.
Click to expand...
Click to expand...
 
Top