Invalid use of ' (dot)

Hi,
MS Access 2013, Windows 10 set up
I'm trying to export a report to Excel by right-clicking in the body of the report & selecting "Export", "Excel" but I'm getting the error "There is an invalid use of the . (dot) or ! operator or invalid parentheses". I'm attaching a screen-shot of the error - can anyone help please?Invalid use of (dot)
Steve GilbertAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
I've never gotten that error, but if I did, my troubleshooting process would start by working through these questions:
Can you export any report to Excel?
   (if you can, what's the difference?)
Can you export to a different folder?
"                                                   filename?
"                                                   file extension (that is, "xlsx" rather than "xls"?)

Let us know what you learn...
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
Just a thought, but any chance your currency settings are different? Sometimes the separator between dollars and cents is a comma rather than a dot.
Steve GilbertAuthor Commented:
Paul - thanks for the response. Yes, I can export other reports successfully. I tried using the file name of a successful export as the file name for the problem report but got the same error. On the export dialogue box I'm not given the option for ".xlxs" files, just 2 options for "Excel 97-Excel 2003 *.xls" and "Excel 5.0/95 (*.xls) both of which I've tried but still get the error
Brian - my currency is Pounds Sterling and the separator is always "."
Thanks for your help & time, appreciate it
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Paul Cook-GilesSenior Application DeveloperCommented:
Hm.  Can you export the report's query?
If not, try rebuilding the query ( one table added/one column added at a time), and see where the export stops working; that might provide a clue as to the issue.
If you can export the complete set of query results, try rebuilding the report, adding one field at a time, and see where the export stops working.

Keep us apprised of your progress;  this is very interesting.  :)
Steve GilbertAuthor Commented:
Hi Paul, yes I can export the query OK - I'll try your suggestion of rebuilding the report & let you know. Thanks again for your help
Paul Cook-GilesSenior Application DeveloperCommented:
Progress!  :)
Steve GilbertAuthor Commented:
OK - the problem appears to be the date parameters that are entered on a pop-up form before the report is opened - they are shown in the heading of the report on my original question and removing these allows the export to run OK.  
The dates are used to filter the recordset, the SQL from the query in the report is as below:
SELECT Clients.First_Name, Clients.Lead_Name, Payments.payDate, Payments.payAmount, Bookings.bkgStatus, payment_catg.payment_catg_ID, payment_catg.payment_catg, quotations.qtFrom_Date
FROM quotations INNER JOIN ((Bookings INNER JOIN Clients ON Bookings.Client_ID = Clients.Client_ID) INNER JOIN (payment_catg INNER JOIN Payments ON payment_catg.payment_catg_ID = Payments.payment_catg_ID) ON Bookings.Booking_ID = Payments.Booking_ID) ON quotations.Quote_ID = Bookings.Quote_ID
WHERE (((Payments.payDate) Between [Forms]![frmDateSel]![frDate] And [Forms]![frmDateSel]![toDate]))
ORDER BY Payments.payDate;
Fabrice LambertConsultingCommented:
Hi,

Make sure you validate user input before filtering (you expect a date, ensure input is a date).
Gustav BrockCIOCommented:
Perhaps an expression/controlsource where you use tablename!fieldname in favour of tablename.fieldname - or vice versa.
Paul Cook-GilesSenior Application DeveloperCommented:
Excellent work identifying the cause of the failure to export.  :)  
If you want to preserve the functionality of displaying the date range on the report, you might consider letting the pop-up box populate a lookup table, and then have the query use the values from the lookup table as the criteria and as the data for the display on the report.

Glad you were able to narrow it down!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve GilbertAuthor Commented:
Thanks so much Paul for walking me through this, I wouldn't have solved this without your advice.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows 10

From novice to tech pro — start learning today.