Truncating Values In Access SQL

[gharabed]

I feel embarrassed asking this but is there a truncate function I can use in an sql statement when writing a query? I know there is a "round" function. When I try to use TRUNC or TRUNCATE I get an error.

 

[tunsarod]

Assuming a table called NAMES exists with a field called Lastname, the following SQL was constructed using the Query Design Grid:

SELECT NAMES.LastName, Left([Lastname],5) AS Expr1
FROM NAMES;

If used in VBA code remove the semicolon.

According to VBA help Ltrim, Rtrim and Trim also work to remove spaces but in my copy of SAMS Teach Yourself SQL it suggests that LTRIM and RTRIM can be used to remove occurances of any string cominbation. I haven't got that to work in an Access SQL statement.

Regards

Rod

 

[tunsarod]

This example was constructed using the Query Design Grid. It assumes the existence of a Table called NAMES with a Field called Lastname.

SELECT LANDLORDS.LastName, Left([Lastname],5) AS Expr1
FROM LANDLORDS;

To used in VBA Code remove the semicolon and place in Quotes:

"SELECT LANDLORDS.LastName, Left([Lastname],5) AS Expr1
FROM LANDLORDS"

Try Ltrim, Rtrim and Trim to remove spaces.

My SAMS Teach Yourself SQL says that LTRIM can be used to remove any leading string value from a string but I haven't made it work in VBA.

Regards
Rod

 

[gharabed]

Sorry I didn't make the original post more clear. I want to truncate an numeric value. So if I have a value of say 10.7 I want to use some function that will return the value "10". Or, if I have the value 10.78 and I want to truncate to the "tenths" position I want the function to return "10.7". I can't believe there isn't a function that does this.

Greg

 

[tunsarod]

I see. You need to use the INT function.

i.e Use Int(10.7) to get 10

Use 10.7 - Int(10.7) to get 0.7

Rod

 

[johpje]

As for tenths you can ofcourse use

Int(value * 10) / 10

If you use this more than once, I suggest you put it in a function.

Regards,
Johpje

 

[Craig0201]

iif(right(Left(String,len(string)-1),1)=".",Left(String,len(string)-2),Left(String,len(string)-1))

will give

10.7 > 10
10.78 >10.7
1 > ""


Craig