Truncating Results

[Guest]

I am running a query that grabs information from a field in a table.

That field can have numerical value or character value.

When it is numerical, it looks like 0000034567

I want to truncate the numerical to just the last 5 digits.

If it is character based, it will be like M12-BR4810, in which case I do not
want it truncated at all.

Anyone know how I would do this in a query for the results?

 

[Guest]

First point of order: The field does not contain numerical values. Rather it
contains numerical characters which are text. Off my high horse and on to
your question....

TheField: IIf(IsNumeric([FieldName]) = False, [FieldName] , Val([FieldName]))

Note: The above will cause an error if there are any null fields.

 

[John Spencer]

If you want to trim off the leading zeroes
Field: Truncated: IIF(IsNumeric([YourField]), Val([YourField]),[YourField])

If you want exactly the last five numeric characters then
Field: Truncated: IIF(IsNumeric([YourField]),
Right([YourField],5),[YourField])

First returns 1234 from 000001234
Second returns 01234

First returns 653214 from 0000653214
Second returns 53214
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Where in the query to I place this formula?

 

[John Spencer]

If you are working in the design view, then in a new column in the field row
enter
Field: Truncated: IIF(IsNumeric([YourField]), Val([YourField]),[YourField])

Obviously you will need to replace YourField with the name of your field.
The word "Truncated" can be any name you wish to give the result.

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

 

[Guest]

I get this error.

Extra ) in query expression 'shipping.[IIf(IsNumeric([po number]),Val([po
number),[ponumber])]'.

This is what i have in for the formula,

Truncated: IIf(IsNumeric([po number]),Val([po number]),[po number])

if i select the table i get an error, if i select no table it seems to save
and when you select datasheet, only this shows.

BARB00001

The value for

00000024567 does not show at all.

Any suggestions?

If you are working in the design view, then in a new column in the field row
enter
Field: Truncated: IIF(IsNumeric([YourField]), Val([YourField]),[YourField])

Obviously you will need to replace YourField with the name of your field.
The word "Truncated" can be any name you wish to give the result.

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

Where in the query to I place this formula?

 

[John Spencer]

What you have posted indicates that you have
Shipping.[IIf(IsNumeric([po number]),Val([po number),[ponumber])]'

Is Shipping the name of a table that has Po Number in it?

Try
IIF(IsNumeric(Shipping.[Po Number]),Val([Shipping].[Po Number]),[Po Number])

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

I get this error.

Extra ) in query expression 'shipping.[IIf(IsNumeric([po number]),Val([po
number),[ponumber])]'.

This is what i have in for the formula,

Truncated: IIf(IsNumeric([po number]),Val([po number]),[po number])

if i select the table i get an error, if i select no table it seems to save
and when you select datasheet, only this shows.

BARB00001

The value for

00000024567 does not show at all.

Any suggestions?

If you are working in the design view, then in a new column in the field row
enter
Field: Truncated: IIF(IsNumeric([YourField]), Val([YourField]),[YourField])

Obviously you will need to replace YourField with the name of your field.
The word "Truncated" can be any name you wish to give the result.

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

Where in the query to I place this formula?

 

[Guest]

It is still not working.

Could the fact that the field is a text field? This is nothing I can
change, it is how the other program writes to the database, it writes it as
text instead of numbers.

It will only display things that have text.

BARB0001 is not truncated(nor should it be) and shows that value

but

00000012345 is not displayed at all.

Anything else I should try?

 

[John Spencer]

That should work with a text field without any problems - as a matter of
fact I planned on it being text since no other field type can hold values
such as BARB0001.

IIF(IsNumeric(Shipping.[Po Number]),Val([Shipping].[Po Number]),[Po Number])

Try posting the ENTIRE query that you are using. Perhaps we can see the
trouble then.
(Menu View: SQL, copy, & paste into your posting)

The only thing I can remotely think of is that you have quote marks
somewhere in there, so the expression looks like
IIF(IsNumeric(Shipping.[Po Number]),Val("[Shipping].[Po Number]"),[Po
Number])

If that is the case you should be seeing zero returned for the Po Number.
And if that is the case, the expression is wrong.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

SELECT DISTINCT IIf(IsNumeric([Shipping].[po number]),Val([Shipping].[po
number]),[po number]) AS Expr1
FROM constants, ((shipping INNER JOIN po_auxfil00 ON shipping.[po number] =
po_auxfil00.AUX_PO_NO) INNER JOIN po_pohdrf00 ON po_auxfil00.AUX_PO_NO =
po_pohdrf00.PO_HDR_PO_NO) INNER JOIN po_polinf00 ON po_auxfil00.AUX_PO_NO =
po_polinf00.PO_LIN_PO_NO;

 

[Guest]

I see the error now, its in the tables relationship.

It has worked now, thanks for the help.