Import to Excel - Undefined Function in Expression

[Dave177]

Hi there,

I've got a form with a listbox and what I would like is to have a button that opens an excel template that imports data from an excel query. The query qryMyQuery uses column 0 of the listbox as its criteria.

I set up the spreadsheet with a database query so that each time it opens it is updated with the contents of the query. This worked if the criteria of the query was eg. "5" but when set to [MyListBox] the excel document would not open - error message: "Too few parameters - expected 1". I then used a function GetID("MyListBox") to try and solve this problem. However, now I get error message "Undefined Function in Expression".

Is there a way to get excel to recognise my function?

 

[dhookom]

I'm don't think you can use functions or objects that Excel doesn't recognize. There might be some alternatives such as creating a single record, single field table in Access and setting the field value to "5". Add this table to your query and set the criteria to:
tblNewTable.NewField

Another solution might be to filter your query (did you use msquery) with a value from your workbook.


Duane
Hook'D on Access
MS Access MVP

 

[Dave177]

Duane,
Thanks for the advice. Did what you said and it now works

 

[Dave177]

I don't understand what is going on now. I have a query [qryMyQuery1] which arranges the majority of the data I want to export to excel. However, I need just a bit more data from [qryMyQuery2] in order to create a query [qryMyQuery3] which should contain all of the data for the import.
I can go into Excel and set up a database query using [qryMyQuery1] - no problem - the data can be imported into excel fine. However when I try the same with [qryMyQuery3] (which is taking data from [qryMyQuery1] and [qryMyQuery2] it gives me the "Too Few Parameters" error message. I can't understand why as [qryMyQuery2] does not need have any forms etc as criteria... It is however a union query...
Do you know why I am getting the Parameter error when I'm not using any forms as criteria?

Dave

 

[dhookom]

Any chance you can share the SQL views?

Duane
Hook'D on Access
MS Access MVP

 

[Dave177]

Duane,
Thanks for your help but now sorted (after a day and a lot of headaches).

David