Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Truncating Values In Access SQL

Status
Not open for further replies.

gharabed

Programmer
Joined
Sep 7, 2001
Messages
251
Location
US
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.
 

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
 


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

 
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
 
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


 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top