Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Invalid procedure call issue in Access to Excel Query

Status
Not open for further replies.
maxxev

maxxev

Technical User
Jul 17, 2008
139
NL
Hi, i'm trying to repeat something i've done before and got working with no problem, this time however I can't get it to work.

I have a query I just want to pass from Access to Excel, it works fine in Microsoft query, but when I try to enter the query data into Excel I get the "invalid procedure call".

Any thoughts as to why?

Thank you

Code: 
SELECT Q_SKU_SLIFE_4_PLNG.Active, Q_SKU_SLIFE_4_PLNG.CUSTOMER, Q_SKU_SLIFE_4_PLNG.`Product Code`, Q_SKU_SLIFE_4_PLNG.`Storage Condition`, Q_SKU_SLIFE_4_PLNG.`Dating Notes`, Q_SKU_SLIFE_4_PLNG.Description, Q_SKU_SLIFE_4_PLNG.Shelflife, Q_SKU_SLIFE_4_PLNG.MinlifeintoDepot, Q_SKU_SLIFE_4_PLNG.MinOutloadLife, Q_SKU_SLIFE_4_PLNG.PAV, Q_SKU_SLIFE_4_PLNG.Unit, Q_SKU_SLIFE_4_PLNG.NUTS, Q_SKU_SLIFE_4_PLNG.SESAME, Q_SKU_SLIFE_4_PLNG.GLUTEN, Q_SKU_SLIFE_4_PLNG.`NON-VEGGIE`, Q_SKU_SLIFE_4_PLNG.`DFST Ambient Life`, Q_SKU_SLIFE_4_PLNG.`DFST Chilled Life`, Q_SKU_SLIFE_4_PLNG.InnerBarcode, Q_SKU_SLIFE_4_PLNG.OuterBarcode, Q_SKU_SLIFE_4_PLNG.OuterCaseCode
FROM `G:\Company\DataSheets\ALLERGEN DB.mdb`.Q_SKU_SLIFE_4_PLNG Q_SKU_SLIFE_4_PLNG
 
Sort by date Sort by votes
One of your problems:
[tt]
FROM `G:\Company\DataSheets\ALLERGEN DB.mdb`.Q_SKU_SLIFE_4_PLNG Q_SKU_SLIFE[/tt]

Looks like you are using an alias for your [blue]
`G:\Company\DataSheets\ALLERGEN DB.mdb`.Q_SKU_SLIFE_4_PLNG[/blue] source and call it [blue]Q_SKU_SLIFE[/blue], but you do not use this alias in your Select.

Have fun.

---- Andy
 
Upvote 0 Downvote
Nope, no alias, I think your monitor ha just chpped off the last of the text (all there in what I posted):
"FROM `G:\Company\DataSheets\ALLERGEN DB.mdb`.Q_SKU_SLIFE_4_PLNG Q_SKU_SLIFE_4_PLNG"

If it helps any I tried when the DB was in 2000 format, then I updated to 2007 format and it's made no difference (I have 2007 but the database was started some years ago in 2000).

Cheers


 
Upvote 0 Downvote
I would not use logical drive mapping. Specify the server instead!

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Here is the code off the one that does work if it helps.

I can't safely the server name as we will be chaning servers next month.

Code: 
SELECT `Sample info sheet`.`KC PD Code`, `Sample info sheet`.`Ingredient Type`, `Sample info sheet`.`Date sample arrived`, `Sample info sheet`.`Supplier Code`, `Sample info sheet`.`Product Name`, `Sample info sheet`.`storage description`, `Sample info sheet`.`Price Per Unit (Kg/L)`, `Sample info sheet`.Supplier, `Sample info sheet`.`Minimum Order Quantity`, `Sample info sheet`.`Lead Time`, `Sample info sheet`.`Shelf life`, `Sample info sheet`.`Spec received`, `Sample info sheet`.`Spec (link)`, `Sample info sheet`.`Additional Comments`, `Sample info sheet`.Concatenate
FROM `G:\Company\DataSheets\PD SAMPLE SHEETS\Sample Information DB.mdb`.`Sample info sheet` `Sample info sheet`
 
Upvote 0 Downvote
when I try to enter the query data into Excel
Click to expand...
By what process?

Please be specific & detailed in your explanation.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Sorry, by: "Data" tab, "from access", navigating to database and following wizard.

Previous working sheet (code as in post my post on 6 Sep 12 9:13)was setup in excel 2003, however I have replicated it in 2007 and it works fine.
 
Upvote 0 Downvote
Additional: I have tested a table and a very basic query from that Database and they work fine, so it is something to do with the makeup of hte query :(.
 
Upvote 0 Downvote
Additional 2.

My query is based on a crosstab and a linked table from another DB with a relationship.
Do either of those usually prevent output to excel?

Here is the queries code for reference, but obviously there is a query behind the crosstab, that is made up of 4 linked tables.

Code: 
SELECT AllergenStage1_SKU_CrossQry.Status AS Active, Mid([Full_Product_Code],InStr([Full_Product_Code],".")+1,InStr(4,[Full_Product_Code],".")-InStr([Full_Product_Code],".")-1) AS CUSTOMER, T_SKUDatasheet.Full_Product_Code AS [Product Code], T_SKUDatasheet.[STORAGE ON PACKING] AS [Storage Condition], T_SKUDatasheet.ShelflifeNotes AS [Dating Notes], T_SKUDatasheet.[UK Product Name] AS Description, T_SKUDatasheet.Shelflife, T_SKUDatasheet.MinlifeintoDepot, [MinlifeintoDepot]+1 AS MinOutloadLife, [MinlifeintoDepot]+1 & " - " & [Shelflife]-1 AS PAV, AllergenStage1_SKU_CrossQry.Unit, AllergenStage1_SKU_CrossQry.NUTS, AllergenStage1_SKU_CrossQry.SESAME, AllergenStage1_SKU_CrossQry.GLUTEN, AllergenStage1_SKU_CrossQry.[NON-VEGGIE], T_SKUDatasheet.[DFST Ambient Life], T_SKUDatasheet.[DFST Chilled Life], T_SKUDatasheet.InnerBarcode, T_SKUDatasheet.OuterBarcode, T_SKUDatasheet.OuterCaseCode
FROM T_SKUDatasheet LEFT JOIN AllergenStage1_SKU_CrossQry ON T_SKUDatasheet.Full_Product_Code = AllergenStage1_SKU_CrossQry.SkuCode;
 
Upvote 0 Downvote
Here are the two queries that you have posted. I find that looking at my code in an ordered way, helps me, and others, to understand what going on much better
Code: 
SELECT
  s.`KC PD Code`
, s.`Ingredient Type`
, s.`Date sample arrived`
, s.`Supplier Code`
, s.`Product Name`
, s.`storage description`
, s.`Price Per Unit (Kg/L)`
, s.Supplier
, s.`Minimum Order Quantity`
, s.`Lead Time`
, s.`Shelf life`
, s.`Spec received`
, s.`Spec (link)`
, s.`Additional Comments`
, s.Concatenate

FROM `G:\Company\DataSheets\PD SAMPLE SHEETS\Sample Information DB.mdb`.`Sample info sheet` s  


SELECT
  p.Active
, p.CUSTOMER
, p.`Product Code`
, p.`Storage Condition`
, p.`Dating Notes`
, p.Description
, p.Shelflife
, p.MinlifeintoDepot
, p.MinOutloadLife
, p.PAV
, p.Unit
, p.NUTS
, p.SESAME
, p.GLUTEN
, p.`NON-VEGGIE`
, p.`DFST Ambient Life`
, p.`DFST Chilled Life`
, p.InnerBarcode
, p.OuterBarcode
, p.OuterCaseCode

FROM `G:\Company\DataSheets\ALLERGEN DB.mdb`.Q_SKU_SLIFE_4_PLNG p

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
EDIT: so it's at least one of the calculated fields :(, can I use calculated fields if I query the query and then output that....?
 
Upvote 0 Downvote
MS Query knows nothing about your ACCESS aclculation!

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Of course, you may be able to perform a similar calculation in MS Query.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
The code that is tripping it up is this bit of SQL, the other 2 calculated fields are fine:
Code: 
CUSTOMER: Mid([Full_Product_Code]
,InStr([Full_Product_Code],".")+1,InStr(4,[Full_Product_Code],".")-InStr([Full_Product_Code],".")-1)
Can this be done some other way?
This bit of codes looks at our product codes and pulls out the customer reference.
Code format: F.CUS.CODE
Where CUS is the customer reference.
The above code pulls that bit out brilliantly, but obviously MS query doesn't like this!
 
Upvote 0 Downvote
change QUOTE to APOSTROPHIE.

MS Query does not like QUOTES.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Hi, thank you for the suggestion still works in Access with the commas, made no difference to the issues with MS query though :(.

Code: 
CUSTOMER: Mid([Full_Product_Code]
,InStr([Full_Product_Code],'.')+1,InStr(4,[Full_Product_Code],'.')-InStr([Full_Product_Code],'.')-1)
 
Upvote 0 Downvote
Post your SQL

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote

SELECT AllergenStage1_SKU_CrossQry.Status AS Active,
T_SKUDatasheet.Full_Product_Code AS [Product Code],
T_SKUDatasheet.[STORAGE ON PACKING] AS [Storage Condition],
T_SKUDatasheet.ShelflifeNotes AS [Dating Notes],
T_SKUDatasheet.[UK Product Name] AS Description,
T_SKUDatasheet.Shelflife,
T_SKUDatasheet.MinlifeintoDepot,
[MinlifeintoDepot]+1 AS MinOutloadLife,
[MinlifeintoDepot]+1 & ' - ' & [Shelflife]-1 AS PAV,
AllergenStage1_SKU_CrossQry.Unit,
AllergenStage1_SKU_CrossQry.NUTS,
AllergenStage1_SKU_CrossQry.SESAME,
AllergenStage1_SKU_CrossQry.GLUTEN,
AllergenStage1_SKU_CrossQry.[NON-VEGGIE],
T_SKUDatasheet.[DFST Ambient Life],
T_SKUDatasheet.[DFST Chilled Life],
T_SKUDatasheet.InnerBarcode,
T_SKUDatasheet.OuterBarcode,
T_SKUDatasheet.OuterCaseCode,
Mid([Full_Product_Code],InStr([Full_Product_Code],
'.')+1,InStr(4,[Full_Product_Code],
'.')-InStr([Full_Product_Code],'.')-1) AS CUSTOMER
FROM T_SKUDatasheet LEFT JOIN AllergenStage1_SKU_CrossQry ON T_SKUDatasheet.Full_Product_Code = AllergenStage1_SKU_CrossQry.SkuCode;
Click to expand...
 
Upvote 0 Downvote
PLEASE POST YOUR EXCEL MS QUERY SQL -- ALL OF IT. THE ENTIRE THING. START TO FINISH.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JK Dutta
  • Locked
  • Question
requet for form an access query for report
Replies
2
Views
2K
dhookom
dhookom
combo
  • Locked
  • Question
'Wrong property setting' message in query 1
Replies
2
Views
2K
combo
combo
lameid
  • Locked
  • Question
SELECT FROM Table in Another Access File
Replies
2
Views
2K
lameid
lameid
TheresAlwaysAWay
  • Locked
  • Question
Union query provides different reslt than when summing the combined queries individually 1
Replies
12
Views
593
TheresAlwaysAWay
TheresAlwaysAWay
btamsgn
  • Locked
  • Question
how to create a crosstab query 1
Replies
1
Views
2K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top