It seems having an expression in my access query prevents me from using it with Get Data in Excel. Is there a workaround for this?
It seems having an expression in my access query prevents me from using it with Get Data in Excel. Is there a workaround for this?
I presume you mean the excel ribbon functionality (Data>From Access) so maybe, maybe not. No information to go on. Would help if you clarified the detail - the actual error message and sql to the query would be a good start.
There is no error message. The query runs fine in Access. It does not show up as a query in Excel in the Data>From Access when I load the database. Removing the expressions allows it to appear. Creating separate queries with the expressions and then including them in another query does not work.
queryCode:SELECT Inventory.SampleID, Sum(voltaken([Inventory].[SampleID],[Plates].[Reagents],[Inventory].[Molarity])) AS Used, Chemicals.Abbreviation, Chemicals.MW, Chemicals.Radionuclide, Inventory.[Volume Prepared], Nz([BoxID]) AS Expr1, Boxes.Label, [Shelf] & "." & [Rack] & "." & [Tray] & "." & [Position] AS Address, Inventory.[Reference Date], Inventory.[Date Prepared], Inventory.Retired, Chemicals.Compound, Chemicals.ChemIDFROM Plates, Chemicals INNER JOIN ((calcinvused INNER JOIN Inventory ON calcinvused.SampleID = Inventory.SampleID) INNER JOIN Boxes ON Inventory.Box = Boxes.BoxID) ON Chemicals.ChemID = Inventory.ChemID GROUP BY Inventory.SampleID, Chemicals.Abbreviation, Chemicals.MW, Chemicals.Radionuclide, Inventory.[Volume Prepared], Nz([BoxID]), Boxes.Label, [Shelf] & "." & [Rack] & "." & [Tray] & "." & [Position], Inventory.[Reference Date], Inventory.[Date Prepared], Inventory.Retired, Chemicals.Compound, Chemicals.ChemID;
are you referring to the nz function? nz is a vba function specific to access but not recognised in excel vba
in this instance do you need it anyway? If to supply 0 then change to
iif(isnull(BoxID),0,BoxID)
No, sorry, that was an attempt at another possible solution I was trying
so what is your expression/s you take out for it to work?
Might have changed a bit from before as I'm still fussing with it.. this one doesn't show up:
This one shows up after removing the two fields that are calculated:Code:SELECT inventory.sampleid, chemicals.abbreviation, chemicals.mw, chemicals.radionuclide, inventory.[Volume Prepared], boxes.label, inventory.[Reference Date], inventory.[Date Prepared], inventory.retired, chemicals.compound, chemicals.chemid, Sum(Voltaken([Inventory].[SampleID],[Plates].[Reagents],[Inventory].[Molarity])) AS used, [Shelf]&"."&[Rack]&"."&[Tray]&"."&[Position] AS addressfromplates, chemicals INNER JOIN ((calcinvused INNER JOIN inventory ON calcinvused.sampleid=inventory.sampleid) INNER JOIN boxes ON inventory.box=boxes.boxid) ON chemicals.chemid=inventory.chemid GROUP BY inventory.sampleid, chemicals.abbreviation, chemicals.mw, chemicals.radionuclide, inventory.[Volume Prepared], boxes.label, inventory.[Reference Date], inventory.[Date Prepared], inventory.retired, chemicals.compound, chemicals.chemid, [Shelf]&"."&[Rack]&"."&[Tray]&"."&[Position], boxes.boxid;
edited to make the queries more readable, but then it messed up the spacing. these queries do work, spacing problems are not in the originalCode:SELECT inventory.sampleid, chemicals.abbreviation, chemicals.mw, chemicals.radionuclide, inventory.[Volume Prepared], boxes.label, inventory.[Reference Date], inventory.[Date Prepared], inventory.retired, chemicals.compound, chemicals.chemidfromplates, chemicals INNER JOIN (inventory INNER JOIN boxes ON inventory.box=boxes.boxid) ON chemicals.chemid=inventory.chemid GROUP BY inventory.sampleid, chemicals.abbreviation, chemicals.mw, chemicals.radionuclide, inventory.[Volume Prepared], boxes.label, inventory.[Reference Date], inventory.[Date Prepared], inventory.retired, chemicals.compound, chemicals.chemid, boxes.boxid;
so voltaken is a UDF (user defined function) - again that won't be recognised in Excel because it is not in excel. I've never tried it but all I can suggest is copy the function to excel as a public function. It might work, but very much depends on what it actually does.
Yeah I'd already tried that and it didn't work. Thanks though
Searching for access query UDF excel is now getting me results that are more in the right direction and it seems like it's not possible to pull these queries in due to built-in security limitations..
perhaps - but I think it is more to do with you using functions not recognised by Excel.
Yeah I don't know that's why I'm here. For now I'm just creating a table from the query in access and pulling that to Excel but I'll have to be updating it unless there's a way to automate that?
please clarify what that means - automate what? generation of a table? if so, from where? at what point? using what?but I'll have to be updating it unless there's a way to automate that?
Your questions are vague and require many posts to try to determine what you mean? Please describe clearly what you are trying to do and why you are doing it rather than focussing on how you are doing it.