Trouble exporting a parameter query to Excel 1

[Stadler09]

Hello. I am trying to export a select query that references a crosstab query to Excel. I have the VBA code that exports regular queries and I'm using that code to automate the exporting process.

I have a form that supplies a specific date range for the user and I want to query my crosstab query for data within that date range. I have set the criteria for the date range, and have supplied the appropriate parameters in the parameter settings for the query. Yet when I try to export, it gives me the following error...

Run-time error '3061':
Too few parameters. Expected 1.

I have no problem exporting select queries that reference crosstab queries, but when I set criteria that refers to the date range on the form, I get the error. Any ideas?

 

[dhookom]

Consider creating a table with one record and as many fields as your controls on the "form that supplies a specific date range for the user". Bind the controls to the fields in the new table. Then use the field values from this table in your crosstab rather than references to the form controls. After entering values into the bound controls, you must make sure the "record" is saved prior to running the query. Don't allow more than a single record in the new table.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[JonFer]

You can also bypass this problem by writing a function that returns the date value from the form. Use this function as the criteria in your crosstab. You can make the function more flexible by passing the control name to it - then one function can be used to return any form control value.

John

http://www.interautomateconsulting.com

 

[Stadler09]

Thanks for your input guys. I'd rather not create any more tables in this database. It's getting to be huge. John, Do you have an example of this function? All I want it to do is give me the date value that is on the form. I'm also not sure what you mean by passing a control name to the function. I'm assuming its the name of the text box that is housing my date value? Thanks a bunch.

Craig.

 

[JonFer]

Here is a simple function without error checking. In the query criteria you would put: GetFormValue("controlname")

Function GetFormValue(strControlName as String) as Variant

GetFormValue = Forms!frmYourForm.Controls(strControlName).Value

End Function


John

http://www.interautomateconsulting.com

 

[Stadler09]

John,
I tried to use that function in my database, but it didn't work. Here is the function I typed into a separate module...

Option Compare Database

Function GetFormValue(strControlName As String) As Variant

GetFormValue = Forms![frmMainMenu].Controls(strControlName).Value

End Function

When I try to use this function, I get the following error:
Run-time error '2465':
Microsoft Access can't find the field '10/1/2004' referred to in your expression.

10/1/2004 is the date I entered into the main menu text field. Any ideas?

 

[JonFer]

What is the name of the control on the form with the date? What is the form name? What did you put in the criteria of your query?


John

http://www.interautomateconsulting.com

 

[Stadler09]

the control on the form is named "txtFromDate". The form name is "frmMainMenu". In the criteria of the query I put: GetFormValue(Forms![frmMainMenu]![txtFromDate])

I simply used it just like I would any other function. I think this would be a great way to get around my issue if this works. Thanks.
Craig.

 

[JonFer]

You should just use the control name:

GetFormValue("txtFromDate")

In the function you have the form name:

GetFormValue = Forms![frmMainMenu].Controls(strControlName).Value


You were passing the value of the control to the function instead of its name and the function was then looking for a control called 10/1/04 on the form.

John

http://www.interautomateconsulting.com

 

[PHV]

Replace this:
GetFormValue(Forms![frmMainMenu]![txtFromDate])
By this:
GetFormValue('txtFromDate')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

PHV - Are you normally up at 3 am?

John

http://www.interautomateconsulting.com

 

[PHV]

John, I'm used to have insomnia from time to time...

 

[Stadler09]

I'll give that a shot when I get into work on Monday.

 

[JonFer]

Lazy

John

http://www.interautomateconsulting.com

 

[Stadler09]

ALRIGHT! It worked. You guys are the best. That will eliminate a lot of headaches for me in the future with a lot of the financial reporting I have to do. Thanks!

Craig.

 

[farmera]

I'm having the same problem but I have 2 parameters. How would you code the function to call both controls and use it in place of Forms!frmName!txtStartDate And Forms!frmName!txtEndDate?

 

[JonFer]

If I understand correctly, you'd use 2 separate calls to the same function in your query:

Where DateField Between GetFormValue("txtStartDate") and GetFormValue("txtEndDate")

John

http://www.interautomateconsulting.com

 

[farmera]

Thanks for the reply! I've tried it all morning but I've done something wrong.

I get an error:

"Wrong number or arguments used with function in query expression '((SampleDetails.KeepTest)=True) AND ((OrderDetails.CollectDate) Between GetFormValue("txtStartDate") AND GetFormValue("txtEndDate"))'.

Here is my SQL behind the query:

SELECT SampleDetails.KeepTest, OrderDetails.CollectDate, Orders.OrderID, OrderDetails.Site, Results.Test, Results.NumericResult, Results.Units
FROM ((Orders INNER JOIN SampleDetails ON Orders.OrderID = SampleDetails.OrderID) INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID) INNER JOIN Results ON (SampleDetails.Test = Results.Test) AND (SampleDetails.SampleNumber = Results.SampleNumber) AND (OrderDetails.SampleNumber = Results.SampleNumber) AND (Orders.OrderID = Results.OrderID)
WHERE ((SampleDetails.KeepTest)=True)
AND ((OrderDetails.CollectDate) Between GetFormValue("txtStartDate") And GetFormValue("txtEndDate"))
ORDER BY OrderDetails.CollectDate, OrderDetails.Site;

Here is my function:

Option Compare Database

Function GetFormValue(txtStartDate As String, txtEndDate As String) As Variant

GetFormValue = Forms!frmBCDESCustom.Controls(txtStartDate).Value
GetFormValue = Forms!frmBCDESCustom.Controls(txtEndDate).Value

End Function

Again, thank you for helping me out!

Angie in Ohio

 

[PHV]

Replace this:
Function GetFormValue(txtStartDate As String, txtEndDate As String) As Variant

GetFormValue = Forms!frmBCDESCustom.Controls(txtStartDate).Value
GetFormValue = Forms!frmBCDESCustom.Controls(txtEndDate).Value

End Function
By this:
Function GetFormValue(strControlName as String) as Variant
GetFormValue = Forms!frmBCDESCustom.Controls(strControlName).Value
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[farmera]

PH, YOU ROCK!

I mistook the strControlName as a filler for my actual control names.

Thanks so much!