Avatar of Steve Gilbert
Steve Gilbert
 asked on

Invalid use of ' (dot)

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)
Windows 10Microsoft AccessMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Steve Gilbert

8/22/2022 - Mon
Paul Cook-Giles

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 B

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 Gilbert

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Paul Cook-Giles

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 Gilbert

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-Giles

Progress!  :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve Gilbert

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 Lambert


Make sure you validate user input before filtering (you expect a date, ensure input is a date).
Gustav Brock

Perhaps an expression/controlsource where you use tablename!fieldname in favour of tablename.fieldname - or vice versa.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Paul Cook-Giles

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Steve Gilbert

Thanks so much Paul for walking me through this, I wouldn't have solved this without your advice.