Truncate data into another field

[Guest]

I have the following examples of data in a field named "Category" whose
datatype is text:

Category
SLCB
ALLOT ADV
4216 - 8825
4563 - 8835
4521 - 8825
4562 - 8860
4216 - 8835

If I want to extract data based on last 4 digits of numerical data how do I
go about it. Is is possible to truncate the last 4 digits of numerical data
into a new field in a query.

Thanks for your urgent support

Al

 

[Wayne Morgan]

Yes, create a calculated field in the query.

Example Field row entry for Design Grid:
LastFour:CLng(Right([TableName].[FieldName], 4))

 

[Guest]

Thanks for your prompt response

The syntax gives me what I was expecting except that for non numerical data
it returns #Error.

Is there any way in which that can be avoided and possibly return all f the
non numeric data

Al

Yes, create a calculated field in the query.

Example Field row entry for Design Grid:
LastFour:CLng(Right([TableName].[FieldName], 4))

--
Wayne Morgan
MS Access MVP

I have the following examples of data in a field named "Category" whose
datatype is text:

Category
SLCB
ALLOT ADV
4216 - 8825
4563 - 8835
4521 - 8825
4562 - 8860
4216 - 8835

If I want to extract data based on last 4 digits of numerical data how do
I
go about it. Is is possible to truncate the last 4 digits of numerical
data
into a new field in a query.

Thanks for your urgent support

Al

 

[Guest]

Also, when I enter in for example 8835 in the new field created against
criteria so that all records with category 8835 would come up, I get the
error msg "Data type mismatch in criteria expression"

Can you also help on this one as well

Al

Thanks for your prompt response

The syntax gives me what I was expecting except that for non numerical data
it returns #Error.

Is there any way in which that can be avoided and possibly return all f the
non numeric data

Al

Yes, create a calculated field in the query.

Example Field row entry for Design Grid:
LastFour:CLng(Right([TableName].[FieldName], 4))

--
Wayne Morgan
MS Access MVP

I have the following examples of data in a field named "Category" whose
datatype is text:

Category
SLCB
ALLOT ADV
4216 - 8825
4563 - 8835
4521 - 8825
4562 - 8860
4216 - 8835

If I want to extract data based on last 4 digits of numerical data how do
I
go about it. Is is possible to truncate the last 4 digits of numerical
data
into a new field in a query.

Thanks for your urgent support

Al

 

[Wayne Morgan]

The Right function will return a string. You're original message led me to
believe that there were only going to be numbers. In order to sort correctly
on the number, if you wanted to sort, I changed it to a numerical data type.
Removing the CLng should do what you are wanting. The data type mismatch
will happen if you try to link this data to data of a different type. If so,
convert it using a conversion function (or remove a conversion function if
there is one there) to change one of the two items to be the same data type
as the other. If the data type is a string, you may need to enter the
criteria as "8835" (with the quotes).

CLng removed:
LastFour:Right([TableName].[FieldName], 4)