MSQuery only returning partial fields

[greengo204]

Not sure if ive posted this in the correct forum...Please advise.

I'm trying to return data from an ODBC source using MSQUERY to Excel 2003. There are two tables iv'e joined one containing Customer Information and the other containg Fixed Contract information. The tables are joined by Customer ID.

In MS Query the information looks correct:

A/C_Code A/C_Name Amount Start_DT Name

08ZZZ999 ZZZZ Storage 1000 01/01/2009 00:00:00 Flexible

But when i return the data to Excel only partial fields are returned:

A/C_Code A/C_Name Amount Start_DT Name

08ZZ ZZZZ St 1000 01/01/2009 00:00 Flex


Am i using MSQUERY in the correct way? this is the first time i have used this tool.

Could anyone suggest why this is happening?

Cheers

 

[xlbo]

Where do you see the partial results? in the cell or in the formula bar? Can you expand the cell width? If you do so does the rest of the information appear?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[greengo204]

partial reults are in both cell and formula fields. Cell spacing does not make a difference.

Cheers

 

[xlbo]

Can you go to the MSQuery screen and click on the SQL button then paste the results here? almost as if it is converting fields to a char rather than a varchar.

Could you also just give us a brief summary (step by step) of how you set up the query in the 1st place (from the Tools>ExternalData>Get External Data) step?



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[greengo204]

SQL:

SELECT po_cmpny.cmpny_cd, po_cmpny.cmpny_nm, po_fi_cnrct.duration, po_fi_cnrct.cnrct_nm, po_fi_cnrct.cnrct_cd, po_fi_cnrct.cnrct_amnt, po_fi_cnrct.start_period, po_fi_cnrct.rollover_amnt, po_fi_cnrct.start_dt
FROM popesdba.po_cmpny po_cmpny, popesdba.po_fi_cnrct po_fi_cnrct
WHERE po_cmpny.cmpny_id = po_fi_cnrct.cmpny_id

data>import external data>new database query:

Then add the above columns, then edit the query in MSQUERY where i drop po_cmpny.cmpny_id onto po_fi_cnrct.cmpny_id.

The information at this point in MSQUERY is correct, its once i return the data to Excel that i get the partial fields.

Linked by cmpny_id which is unique


I've used similar queries in Crystal Reports 8.5 so know the sql should be working.
Im trying to bypass using the export option in Crystal and instead use a query that brings it straight in Excel 2003

Cheers

 

[SkipVought]

Hi,

Where do these heading come from?
[tt]
A/C_Code A/C_Name Amount Start_DT Name
[/tt]
You have NINE fields in your SQL, yet they do not correspond to your headings in any way!
[tt]
po_cmpny.cmpny_cd
, po_cmpny.cmpny_nm
, po_fi_cnrct.duration
, po_fi_cnrct.cnrct_nm
, po_fi_cnrct.cnrct_cd
, po_fi_cnrct.cnrct_amnt
, po_fi_cnrct.start_period
, po_fi_cnrct.rollover_amnt
, po_fi_cnrct.start_dt
[/tt]
You also state...

partial reults are in both cell and formula fields.

SQL does not return formulas.

I am confused by your lack of coherence.

Are you using the same ODBC driver in MS Query that you used in CR?

Do you have any Conditional Formatting in the range where you return the query resultset?

Please focus and answer ALL the questions, CLEARLY, CONCISELY & COMPLETELY.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[greengo204]

SkipVought:

appologies for the lack of coherance & consistency.

whether i returned 1 field from both tables or whether i returned multiple fileds from both tables. I returned the same issue. For purposes of my forum post i thought id use a smaller sample.

To be clear use the below SQL Query:

SELECT po_cmpny.cmpny_cd, po_cmpny.cmpny_nm, po_fi_cnrct.cnrct_cd, po_fi_cnrct.cnrct_nm, po_fi_cnrct.cnrct_amnt, po_fi_cnrct.start_dt
FROM popesdba.po_cmpny po_cmpny, popesdba.po_fi_cnrct po_fi_cnrct
WHERE po_cmpny.cmpny_id = po_fi_cnrct.cmpny_id

Crystal XI (Not version 8.5 as preveously stated)SQL:

SELECT po_cmpny.cmpny_cd, po_cmpny.cmpny_nm, po_fi_cnrct.cnrct_cd, po_fi_cnrct.cnrct_nm, po_fi_cnrct.cnrct_amnt, po_fi_cnrct.start_dt
FROM popesopesdba.po_fi_cnrct po_fi_cnrct, popesopesdba.po_cmpny po_cmpny
WHERE (po_fi_cnrct.cmpny_id=po_cmpny.cmpny_id)

Same drivers & ODBC connection used in both (INFORMIX 3.80 32 BIT)and there is no formating at all within the workbook.


Quote:
partial reults are in both cell and formula fields.

i should have been more clear i meant formula bar in response to Xlbo question.

Cheers

 

[SkipVought]

Insert a new sheet and add your query this new sheet.

If that does not help, open a new workbook and add the query.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[greengo204]

SkipVought:

Tried both suggetions unsuccessfully.

Thanks

 

[SkipVought]

Btw, I have never encoutered this issue.

I have seen either TEXT or NUMERIC data not displayed because the query manager test the first 8 rows to determine what kind of data is in the column.

I have seen, with XLODBC, about 15 years ago, large blocks of data missing in the resultset, but not in recent versions or MS Query.

You might try, Detect & Repair in the HELP menu or a full reload of Excel.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

This certainly seems very strange - was hoping there would be some functions in the query that could be blamed for the truncated text

Can you copy the SQL you have used and create a new MS Query - rather than using the GUI to generate the output, go straight to the SQL tab and paste it directly in there - see if that makes any difference when returning the results to excel....

Only other thought I have is to try and interrogate the properties of the Query Table with some VBA - if you are comfortable with a little VBA, it may be worth putting the following into a module and running it (with the sheet on which your data is returned, active):

Sub View_Properties

with activesheet.querytables(1)
msgbox .preserveformatting
msgbox .preservecolumninfo
msgbox .commandtext
msgbox .fetchedrowoverflow
msgbox .posttext
end with

End Sub

Let us know the results please

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[greengo204]

xlbo:

as suggested i have manually pasted the SQL code into the SQL Tab in msquery, with the same results.

I added the VBA module to check the properties,

False
False
SQL Code (Correct)
False
Run time error '1004' "not a web query"

I also tried changing preserveformatting & preservecolumninfo to TRUE, and returned the same results.

I am unaware of what the "msgbox .fetchedrowoverflow" checks

Just to give more information here some more detail on the results im experiencing.

In MSQuery:
cmpny_cd cmpny_nm cnrct_cd cnrct_nm cnrct_amnt
04TES626 Test 171204 AXS23400 #Test Flexi access 26000.0
start_dt
2004-12-01

In Excel 2003:
cmpny_cd cmpny_nm cnrct_cd cnrct_nm cnrct_amnt start_dt
04TE Test AXS2 #Test Flexi 26000 01/12/2004

2 paterns are consistent:

the cmpny_cd & cnrct_cd always returns the first 4 characters from the database, but i have been unable to find a pattern for cmpny_nm & cnrct_nm.


Datebase field types:

cmpny_cd string
cmpny_nm string
cnrct_cd string
cnrct_nm string
cnrct_amnt currency
start_dt date

any help would be appreciated.

cheers

 

[xlbo]

Hmmm - I was hoping that preserveformatting or preservecolumn info would be "TRUE" in the 1st instance as that might've caused the issue you are seeing

What kind of database is it that you are querying? Are there any further definitions to the fields other than just string....SQL Server for instance has char and varchar data types for strings (I'm clutching at straws here though I must admit - if the field type was an issue you shuld see that in MS Query as well)

Only other option I can thnk of for now would be to simply retrieve 1 field from the database:

SELECT po_cmpny.cmpny_cd
FROM po_cmpny

and see if that has similar issues with the data returned...potentially try a similar thing from a different database and see if that has the same issues (would suggest string fields as dates and numbers seem to be ok from your examples)

If those queries are showing similar issues, it might be worth trying to uninstall and then reinstall MS Query as there is nothing wrong with what you are doing or how you are doing it.

In terms of a solution, this could well be doable using ADO in VBA - if you are interested in that kind of solution, please post in the VBA forum (Forum707) and I or one of the other members should be able to help you out

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cheerio]

This is a long shot. If there were control characters (like line feeds) in the backend data then just possibly MSQuery and Excel might see them differently.

Seems unlikely but in the absence of other ideas ...

 

[greengo204]

xlbo:

tried using:

SELECT po_cmpny.cmpny_cd
FROM po_cmpny

to no prevail.

tried using a different database (Dimensions Access) and returned correct results to excel.

SELECT AA_CUSTOMER_PERIOD_BALANCE_VIEW.CUSTOMER
FROM ICC.dbo.AA_CUSTOMER_PERIOD_BALANCE_VIEW AA_CUSTOMER_PERIOD_BALANCE_VIEW

Think im gonna give this approach a miss and attempt using VBA and ADODB, im sure ill be posting on Forum707 soon enough.

Cheers for your help

 

[xlbo]

Will check the VBA forum but what kind of database is it that you are querying?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Yes, what database?

I am watching this with great intreest, although I have nothing more that I can add toward an MS Query solution.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[greengo204]

My knowledge of the actual server is quite limited as my only exposure is for reporting i.e. Crystal Reports, Excel. Would it be correct to assume that my ODBC driver would indicate what server i am connecting to?

ODBC Driver: INFORMIX 3.80 32 BIT

If not ill will contact our IT dept.

Thanks.

 

[xlbo]

Yup - Informix database...not one that I've had any dealings with in the past...still...shouldn't mean you get different results in MS Query compared to Excel....see you in the VBA forum!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[greengo204]

Found a VBA solution which works well, will be developing futher.

Adapted from Ozgrid

http://www.ozgrid.com/forum/showthread.php?t=83016

Sub Add_Results_Of_ADO_Recordset()
'This was set up using Microsoft ActiveX Data Components version 2.8

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range


Const stADO As String = "DSN=Distribution1;Uid=USERNAME;Pwd=PASSWORD;"

Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)

With wsSheet
Set rnStart = .Range("A1")
End With

stSQL = "SELECT po_cmpny.cmpny_cd, po_cmpny.cmpny_nm, po_fi_cnrct.cnrct_cd, po_fi_cnrct.cnrct_nm, po_fi_cnrct.cnrct_amnt, po_fi_cnrct.start_dt FROM popesdba.po_cmpny po_cmpny, popesdba.po_fi_cnrct po_fi_cnrct WHERE po_cmpny.cmpny_id = po_fi_cnrct.cmpny_id"

Set cnt = New ADODB.Connection

With cnt
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With

'Here we add the Recordset to the sheet from A1
rnStart.CopyFromRecordset rst

'Cleaning up.
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub