MS Query in Excel Left() 1

[mflancour]

I'm in excel 2003 querying external data with microsoft query. All I want to do is the following:
Select left(table.field, 2) from table

I can't seem to find any variation of this that works to let me have a column of data that is either left, mid or right characters.

 

[SkipVought]

Hi,

I don't understand your question. Your query is OK.

Please explain what is happening and what you want.

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[mflancour]

The problem is this gives me an error. It's as if microsoft query does not support the Left() function. Is there another function that does this? or is my syntax incorrect?

 

[SkipVought]

What type of database are you querying?

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[SkipVought]

For instance, if you are querying Oracle or DB2, then use the Substring function.

Select Substr(table.field, 1, 2) from table

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[mflancour]

I'm not at work now so I can't test this,but it looks like exactly what I needed. thanks.

 

[mflancour]

Finally got arround to testing this and it turns out it does not actualy work Has anyone used Microsoft query withing excel to get only a portion of a string?

 

[SkipVought]

Use Left, Mid or Right.

Please post the code you are using within the context of the entire query.

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[mflancour]

Hey, Thanks for the responce, but please read the beginning of the post too. I've already tried Left() right, mid, and even substr(). None of these work withing Microsoft query for excel 2003 in any context that I have tried so far. If you could open excel and go to DATA-import external data-New database query and try a few of these you should see the problem I am running into.

This is the exact code I am using with the various functions I mentioned and none work even in this basic query:
Select left(table.field, 2) from table

 

[stubnski]

Do as Skip suggests and post the all code you are using and database your accessing (Access, Oracle...)




Stubnski

Humans are good students. God created misery, we created computers.

 

[xlbo]

WHERE have you tried these functions ??? in the design view of MSQuery ?? in the SQL view ?? are you writing code ?? Please answer the clarification questions you are asked if you want help...

 

[mflancour]

I have tried both the gui and the sql editor and neither seem to work. The data source is a flat file called cisam using it's own custom made odbc driver. After reading the above post I'm starting to think the issue is comming from teh data source. Is this possible? It seems strange to me since this function is really in MS Query.

 

[SkipVought]

SQL syntax DOES often vary with the data source.

As previously stated, string functions used in MS Query but referencing Oracle or DB2 databases, use the Substr function, while SQL referencing MS Access or MS Excel use the Left, Mid and Right functions.

You might give the ststring function a try.

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}

 

[mflancour]

What is the syntaxt for ststring? ie. ststring(field,1,1)

You know what the strange thing is? Left, mid, and right all work if I use Access to query the same data. In this instance I need to use excel though.