triming via sql statement?

  • Thread starter Thread starter djc
  • Start date Start date
D

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?
 
djc said:
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?
Click to expand...

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.
 
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;
 
substring

Mike Painter said:
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.
Click to expand...
 
is it only available in T-SQL? Not access SQL?

Van T. Dinh said:
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)
Click to expand...
 
Thanks.

Mike Painter said:
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.
Click to expand...
 
Thanks.

Wendy P. said:
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?


.
Click to expand...
Click to expand...
 
Sorry, misunderstood your question.

Yes, you can use the VBA Left() function in Access / JET SQL.
 
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?
Click to expand...

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

Tom Ellison said:
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
Click to expand...
 
Back
Top