external data and query

[seth]

i don't think this is possible but i would ask anyway
if i have a field as part of an external table that i want to trim the
record, is it possible through a query?
for example, if a record was 25 characters in length and i only wanted the
sheet to have the first 10 characters, is that possible by taking it
directly from the external table?
as it is, i can import everything to sheet1, then have sheet2 use the left
function to return the first 10 characters of a cell from sheet1, but i want
to try to eliminate that extra step if possible and just use the left
function (or something similar) directly from the external data

 

[Don Guillett]

AFAIK, you can't do this. Just strip out or use a macro to trim.

 

[seth]

i saw that; but from what i've read the trim function will remove blank
spaces
it isn't blank spaces i want to remove

 

[Don Guillett]

I didn't mean it literally. I meant to trim the text with left,mid,right
functions.

 

[Debra Dalgleish]

If you're using MS Query to import the data, you can edit the query.
Select a cell in the imported data table
On the External Data toolbar, click the Edit Query button
Add a new field, using the LEFT function to return the first ten
characters, e.g. LEFT(MyTable.OrgName,3)
Return the data to Excel

 

[seth]

ok i have been trying to use that...
so let's say i have a size field in the inventory master table
i would use a syntax of LEFT("Inventory Master".SIZE,3) ?
if so, where would i put it? if i put size as criteria field and use the
left function statement as the value, no data appears

 

[Debra Dalgleish]

If you can see the fields in MS Query, you should be able to add it as a
new field. Don't put it in the criteria row.

If you can't see the fields, you can click the SQL button on the MS
Query toolbar, and add the new field to the SQL statement. For example:

SELECT `Invoice Details`.InvID, `Invoice Details`.InvDate,
LEFT( `Invoice Details`.Item,10)
FROM `C:\MyDatabase`.`Invoice Details` `Invoice Details`

 

[seth]

ok, tried using the left function at the end of the select statement and it
is putting the entire syntax of the function in every row of that new
column. so making progress here, it's just repeating the text of the
function, instead of simply running it.

i did put a ' on either side of the function, if i don't i get "expected
lexical element not found"
so the last command of the select statement says 'LEFT("Inventory Master
File".DESCRIPTION_1,10)'

 

[Debra Dalgleish]

Where is your data coming from?
Can you post the entire SELECT statement here?

 

[seth]

SELECT "Inventory Master File".DESCRIPTION_1 AS 'SIZE', "Inventory Master
File".DESCRIPTION_2 AS 'LOT #', "Inventory Master File".ACTUAL_COST AS
'PRICE', "Inventory Master File".UM_STOCK AS 'LB',
BEGINNING_BALANCE+MTD_RECEIPTS-MTD_WITHDRAWALS+MTD_ADJUSTMENTS AS 'TOTAL
LBS',
(BEGINNING_BALANCE+MTD_RECEIPTS+MTD_ADJUSTMENTS-MTD_WITHDRAWALS)/SUB_CONVERSION_FACTOR
AS 'TUBS', '@', "Inventory Master File".SUB_CONVERSION_FACTOR AS 'PER
EACH'
FROM "Inventory Master File" "Inventory Master File"
WHERE (BEGINNING_BALANCE+MTD_RECEIPTS-MTD_WITHDRAWALS+MTD_ADJUSTMENTS>0) AND
("Inventory Master File".DEPT_CODE='00')

 

[Debra Dalgleish]

What happens if you change the definition of SIZE, e.g.:

SELECT LEFT("Inventory Master File".DESCRIPTION_1,10) AS 'SIZE',

 

[seth]

Expected lexical element not found: <identifier>

What happens if you change the definition of SIZE, e.g.:

SELECT LEFT("Inventory Master File".DESCRIPTION_1,10) AS 'SIZE',

 

[Debra Dalgleish]

Where is the data stored?

Expected lexical element not found: <identifier>

 

[seth]

it's our new corporate everything application built on a providex database
system
www.providex.ca

 

[Debra Dalgleish]

I'm not familiar with that program. Perhaps someone at Providex can tell
you why you're getting the error.

 

[onedaywhen]

LEFT(MyTable.OrgName,3)

Please clarify something for me. You suggested the OP use the LEFT
function. Is, then, the LEFT function native to MS Query's
implementation of SQL?

AFAIK MS Query will try to interpret (or convert) the SQL as its own
proprietary SQL, distinctive due to its use of the full table name as
an alias and wrapping all metadata element names in Chr$(96)
characters. If it MS Query does not recognize the syntax as being its
own, it does no interpretation and simply passes the SQL to the
database server for execution.

It seems to me that the OP is using non-MS Query syntax e.g. the use of
Chr$(34) (double quote) to wrap the table name that contains Chr$(32)
(space) characters. For the same reason, I don't think the DBMS product
is Jet/MS Access (i.e. uses square brackets for element names), which
would probably rule out the MS-proprietary LEFT being native syntax for
the database server.

So, do you suggest using LEFT because you know it is natively used by
MS Query or is because the example you are testing on is a Jet database
<g>?

FWIW the standard SQL equivalent is the SUBSTRING function, which is
more likely to work for a given DBMS (but not Jet - lousy
implementation of the ANSI standard <g>) e.g.

SELECT SUBSTRING(DESCRIPTION_1,1,10) AS 'SIZE'
FROM "Inventory Master File"

Jamie.

--

 

[seth]

ok i am trying to use the substring function like you suggested however, how
do i separate it for the next item?
if i use that syntax at the start of the sql statement, it says it can't
import the next table (description_2). if i omit the from statement when
selecting the substring and leave it at the end after adding the other
columns, it says "lexical element not found: FROM" so if i put it after the
substring (see below) it gets past that but then doesn't accept the next
part (description_2 column)

SELECT SUBSTRING(DESCRIPTION_1,1,10) AS 'SIZE'
FROM "Inventory Master File", "Inventory Master File".DESCRIPTION_2 AS 'LOT
#'

 

[onedaywhen]

ok i am trying to use the substring function like you suggested however, how
do i separate it for the next item?

SELECT SUBSTRING(DESCRIPTION_1,1,10) AS 'SIZE'
FROM "Inventory Master File", "Inventory Master File".DESCRIPTION_2 AS 'LOT
#'

I think you want

SELECT
SUBSTRING(DESCRIPTION_1,1,10) AS 'SIZE',
DESCRIPTION_2 AS 'LOT #'
FROM "Inventory Master File"

Jamie.

--