use of eval function with parameter in a query from form control text box

[mlrucci]

Does anyone know if you can use eval function with wildcards? Trying to use eval function with parameter in a query from form control text box. If I do not use the eval function, I get Error 3061 too few parameters. Expected 1



Like "*" & [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavMgt].[Form]![txtFilterName] & "*

I have tried variations of above and always getting an error or no result. Here are some variations:
Eval ("Like "*" & [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavMgt].[Form]![txtFilterName] & "*"")
Eval ("(Like "*" & [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavMgt].[Form]![txtFilterName] & "*")")
Eval ("(Like '*' & [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavMgt].[Form]![txtFilterName] & '*')")

Without success. Is it possible? What I am trying to do is use a txt box to allow the end user to type in what they want to see as there are 50,000 records. I will then export this to excel. I have code that I am using to export to excel with modifications to the excel worksheet. The only way it will work is if I use an eval function before the parameter. Ideas?

[Gicu]

I never had to use the Eval() function (and navigation forms that you seem to have), I assume you are building the SQL statement in VBA, not an actual saved query. Can you please post the full code you have right now?

Cheers,

[CJ_London]

why do you need to use the eval function? just use as a criteria - if you are getting an error then you are typing it wrong - it should be

Like '*' & [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavMgt].[Form]![txtFilterName] & '*'

if in doubt use the code builder to navigate to the required textbox - I suspect it should be [Forms]![frmNavMain].[Form]![frmNavMgt].[Form]![txtFilterName]. Right click on the criteria line and select Builder.

Using Eval, your code needs proper use of quotation marks (using your forms string)

Eval ("Like '*" & [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavMgt].[Form]![txtFilterName] & "*'")

either way you need to include the field

Eval ([myField] & " Like '*" & [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavMgt].[Form]![txtFilterName] & "*'")=True

Note that using the initial * means indexing cannot be used so your query will be slow. Depends on the requirement but I don't include it in my criteria and train users to use it when required

[mlrucci]

Thank you for responding. There are many factors into my thought process and am limited on my knowledge. I have included a stripped-down version of my db. In the end, I need the end user to have the ability to look up what they need from the controls on the forms and export only those items. In the real db, there are over 50,000 records, need the ability to use combo/free text when appropriate to reduce what they need. Input would be very helpful!


ExportExcel.zip

[June7]

Export what for what? A report to PDF? A table/query to Excel?

Do you want to allow users to build filter criteria? Do you want to them able to select what table/query and fields to include?

[mlrucci]

I need to export to excel from the query. The query is based on the parameter and tables to display only the information needed. I was using the transferspreadsheet but didn't allow the flexibility of changing the format of the spreadsheet. I didn't like the font nor the columns not automatically sizing.

[June7]

You want to use Excel automation? Review http://accessmvp.com/KDSnell/EXCEL_Export.htm

[mlrucci]

As I can appreciate the above link, I do not see that it gives me the flexibility to adjust the column width, change the font and color the headers, or am I mssing something? Would like to have the ability to automate this process so it can be uniformly sent to the client with a better visual sheet. What is exported using the transferspreadsheet isn't what I am looking for. Hence my problem. I have used the transferspreadheet and works with the selected parameters, but do not like the "look" of the end product. Can you modify the look of the spreadsheet using the transferspreadsheet?

[Welshgasman]

As I can appreciate the above link, I do not see that it gives me the flexibility to adjust the column width, change the font and color the headers, or am I mssing something? Would like to have the ability to automate this process so it can be uniformly sent to the client with a better visual sheet. What is exported using the transferspreadsheet isn't what I am looking for. Hence my problem. I have used the transferspreadheet and works with the selected parameters, but do not like the "look" of the end product. Can you modify the look of the spreadsheet using the transferspreadsheet?

You can do whatever you want with automation,as long as excel accepts the commands.
You can record a macro in excel for all your pretty bits, then copy that code and amend for the excel object as viewed via Access

[CJ_London]

Can you modify the look of the spreadsheet using the transferspreadsheet?

no - it transfers the underlying data. So if you have a value of 345.678 but formatted so the user sees 1 dp (345.7) , what will be exported to excel is 345.678.

You need to do your excel formatting in excel - using automation as already advised

perhaps take a look at this link as well https://stackoverflow.com/questions/...rom-access-vba

[pbaldy]

To your original question, this should work:

Like "*" & Eval('[Forms]![frmNavMain]![frmNavMain].[Form]![frmNavMgt].[Form]![txtFilterName]') & "*"

I've also used Eval when opening a recordset on a query with form parameters. You can also do it using a querydef and resolving the parameters, but using Eval is a quick and easy solution as well.

[CJ_London]

I've suggested that this in post#3 is

[Forms]![frmNavMain]![frmNavMain]

is not right

But OP seems to have moved on to a completely different subject

[pbaldy]

You're probably right, I just copied from the OP. I was focused on how Eval could be incorporated. It does look funky, but I've never used navigation forms.

[Gicu]

@mlrucci: please have look at the modified code fo rthe Export2Excel sub on the Component Fields subform; that is how I always used the Eval in code to run parameter queries in VBA (Paul eluded to the same approach in post #11).

Cheers,

[pbaldy]

To clarify, I was saying that Eval could be used directly in a saved query, not in code (though it obviously can be used there as well). It can solve the "Too few parameters..." error caused by having form references in a saved query and trying to open a recordset on that query.