Access query with expression not showing up in Excel Get Data, workaround?

[pharmacologist]

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?

[CJ_London]

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.

[pharmacologist]

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.

query

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;

[CJ_London]

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)

[pharmacologist]

No, sorry, that was an attempt at another possible solution I was trying

[CJ_London]

so what is your expression/s you take out for it to work?

[pharmacologist]

Might have changed a bit from before as I'm still fussing with it.. this one doesn't show up:

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;

This one shows up after removing the two fields that are calculated:

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;

edited to make the queries more readable, but then it messed up the spacing. these queries do work, spacing problems are not in the original

[CJ_London]

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.

[pharmacologist]

Yeah I'd already tried that and it didn't work. Thanks though

[pharmacologist]

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..

[CJ_London]

perhaps - but I think it is more to do with you using functions not recognised by Excel.

[pharmacologist]

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?

[CJ_London]

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?

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.