triming via sql statement?

[djc]

is there an sql function I can use to return only the first 20 characters of
a field? Basically I am looking for an SQL equivalent to the VBA Left()
function.

anyone?

 

[GreySky]

Left([field], 20)

David

 

[Mike Painter]

is there an sql function I can use to return only the first 20 characters of
a field? Basically I am looking for an SQL equivalent to the VBA Left()
function.

anyone?

In Access you would use Left or Mid.
If linked to an MSFT SQL database then you use the equivalent of Mid and the
name eludes me. You'll find it in the SQL on line books.

 

[Wendy P.]

Hey, you're in luck...I just asked this question last week
and it worked for me!

Use the Left function:

Example:

Left([Table.Field], 50)

or
if all else fails you can go into your database and create
a new query where you limit that field to 50.

Here's my working code...hope it helps!
(I used the new "trimmed" field name "shortdesc" in my query.)


SELECT Products.Category, Products.ProductType,
Products.ProductName, Left([Products.Description],225) AS
shortdesc
FROM Products
WHERE Products.Category LIKE '%Specials%'
ORDER BY Products.ProductType DESC , Products.ProductName;

 

[HSalim]

substring

characters

In Access you would use Left or Mid.
If linked to an MSFT SQL database then you use the equivalent of Mid and the
name eludes me. You'll find it in the SQL on line books.

 

[djc]

is it only available in T-SQL? Not access SQL?

If you are talking about MS-SQLServer, then there is a
Left() function in T-SQL of exactly the same syntax as VBA
Left().

Check BOL.

HTH
Van T. Dinh
MVP (Access)

 

[djc]

Thanks.

characters

In Access you would use Left or Mid.
If linked to an MSFT SQL database then you use the equivalent of Mid and the
name eludes me. You'll find it in the SQL on line books.

 

[djc]

thanks.

 

[djc]

Thanks.

Hey, you're in luck...I just asked this question last week
and it worked for me!

Use the Left function:

Example:

Left([Table.Field], 50)

or
if all else fails you can go into your database and create
a new query where you limit that field to 50.

Here's my working code...hope it helps!
(I used the new "trimmed" field name "shortdesc" in my query.)


SELECT Products.Category, Products.ProductType,
Products.ProductName, Left([Products.Description],225) AS
shortdesc
FROM Products
WHERE Products.Category LIKE '%Specials%'
ORDER BY Products.ProductType DESC , Products.ProductName;

-----Original Message-----
is there an sql function I can use to return only the first 20 characters of
a field? Basically I am looking for an SQL equivalent to the VBA Left()
function.

anyone?


.

 

[Van T. Dinh]

Sorry, misunderstood your question.

Yes, you can use the VBA Left() function in Access / JET SQL.

 

[Tom Ellison]

Dear DJC:

There is no such thing as "Access SQL". Access comes with your choice
of two database engines, Jet, and MSDE. Jet was formerly the only
choice, so some think of it as being the "native" engine for Access.

Fortunately, both Jet and MSDE use the same syntax for the Left()
function you want, so the same advice works for both. For other
functions, this may not hold true.

is it only available in T-SQL? Not access SQL?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[djc]

thanks.

Dear DJC:

There is no such thing as "Access SQL". Access comes with your choice
of two database engines, Jet, and MSDE. Jet was formerly the only
choice, so some think of it as being the "native" engine for Access.

Fortunately, both Jet and MSDE use the same syntax for the Left()
function you want, so the same advice works for both. For other
functions, this may not hold true.



Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts