asked on

Ongoing recordset bits!

See attached. Note there is no actual data in the tables.

Click on frmExportPrepare.
Click "Export to Excel" on the frmExportPrepare.

This crashes with "Too Few Parameters" error on the following line.
Set rs = db.OpenRecordset("select distinct RepArea from qryExportPrepare01")

However, when I run the actual SQL it runs fine - with no error.

Help please!

Database1.accdb

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

See the change regarding the EVAL ... wrap your Form's reference in eval with double quotes.

SELECT Format([credit_date],"yyyy") AS YearRange, Format([credit_date],"mm") AS MonthRange, credits2005b.CLAIM_MONTH, credits2005b.CREDIT_DATE, credits2005b.CREDIT, credits2005b.CREDIT_DATE, Merchants.MERCHANT_ID, Merchants.MERCHANT_NAME, MerchantGroups.MERCHANT_GROUP_NAME, Merchants.TOWN, Users.USER_GROUP, UserGroups.USER_GROUP_NAME, credits2005b.USER_ID, Users.USER_NAME, Users.TOWN, Users.RepArea, credits2005b.REBATE, credits2005b.SALES_VALUE, credits2005b.DISCOUNTPercent, credits2005b.REBATEPercent
FROM UserGroups INNER JOIN (Users INNER JOIN ((MerchantGroups INNER JOIN Merchants ON MerchantGroups.MERCHANT_GROUP = Merchants.MERCHANT_GROUP) INNER JOIN credits2005b ON Merchants.MERCHANT_ID = credits2005b.MERCHANT_ID) ON Users.USER_ID = credits2005b.USER_ID) ON UserGroups.USER_GROUP = Users.USER_GROUP
WHERE (((Format([credit_date],"yyyy"))=Eval("[forms]![frmExportPrepare]![YearNumber]")) AND ((Format([credit_date],"mm"))=Eval("[forms]![frmExportPrepare]![MonthNumber]")));

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

see attached
Database1-MX01.zip

Patrick O'Dea

ASKER

Thanks mx,

I am getting there but..

I could not open the zipped file you returned.
I got the message that the version of Access could not be detected and that the database was read only.

Can you resend? (I am using A2010 but also have A2007)

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

sure ...stand by

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick O'Dea

ASKER

MX,

Got it open now and the error is gone.
However, when I run the VBA on my live confidential data there is no data found.
Presumably, because the SQL is not finding any.

Is there any way I can see the actual SQL being run. I could then create a query to test.

If not I can create a small amount of dummy data. (SLightler trickier than it sounds!)

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

sure ... see image. Set a Breakpoint at the places shown, click your export button. Code will break. Then in the Immediate window, you can show what the SQL is - I show two examples.

mx
Capture1.jpg

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Probably best to close this Q out if the original question was resolved, which I guess it is ? Open a new Q if still having issue with SQL on real data ....

thx.mx

Patrick O'Dea

ASKER

THanks MX,

I may well be back with another question.

I will investigate a few options first.

Thanks again

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

You are welcome.

mx