We help IT Professionals succeed at work.

Invalid use of ' (dot)

163 Views
Last Modified: 2018-12-04
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)
Comment
Watch Question

Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

Commented:
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, Independent Technology Professional
CERTIFIED EXPERT

Commented:
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.

Author

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
Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

Commented:
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.  :)

Author

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 Developer
CERTIFIED EXPERT

Commented:
Progress!  :)

Author

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 LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi,

Make sure you validate user input before filtering (you expect a date, ensure input is a date).
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Perhaps an expression/controlsource where you use tablename!fieldname in favour of tablename.fieldname - or vice versa.
Senior Application Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks so much Paul for walking me through this, I wouldn't have solved this without your advice.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.