Problems with export

[bbshriver]

I'm trying to export from a report (though I'd rather export directly from a query, but that looks like a lot more work) into Excel. This worked fine a few days ago, but now every time I try I get

"Microsoft Office Access can't find the field 'I' referred to in your expression."

though the I looks more like a vertical line, I don't have a plain vertical line on my keyboard..



Any thoughts?

[slave138]

It sounds like the pipe character (it's on the same key as \ on most keyboards). Beyond that, I've seen a few things saying it can be caused by by having the Auto-Correct option turned on.

[bbshriver]

Indeed, it is the | key (why does it look like 2 dots on the key, but types as a line?)

Anyway, I tried disabling auto-correct and it still does it. Any other clues?

[slave138]

Are you using a Saved Export or just running one directly?

[bbshriver]

I tried creating a button to export on a form... and that gave me the error.

Then I just tried the right-click on the report and export.. same error.

I created a Macro to export query results to a file, so I've got a workaround (that's what I wanted to do anyway), but I'd still like to get this resolved.

[slave138]

I haven't used Access 2003 and it's been a few years since I touched 2002 so I might not be much help. Isn't there an export option in the menus? If so, does it give you an error, too?

[bbshriver]

yes, it does

[slave138]

It could be corrupted, something in the report is trying to refer to an object or value which is no longer there (or has a different name), etc...

If you want to post it with some sample data I might be able to find the problem but this error can be pretty vague.

[pkstormy]

Here's what I do: https://www.accessforums.net/code-re...mple-7571.html

which has made my life 1000 times easier!! ** I just very simply design a new query in the db and that's it! ** The user then automatically sees the new query in the popup form (which lists all the queries in the db) where they then have the option of viewing the query results (in a popup form) and then either printing the query results or exporting the data to an excel file. The code to put a query into a form is actually quite easy (only a couple lines of code - ie. no need to design a new form/subform for every query!) My 'export' form then has all the nice features of exporting the query as a dbf, excel, or txt format and let's the user select the filename, click the 'browse' button to graphically select a folder, and add a date automatically to the filename if they want to when it's exporting. For me, I just then import my 'excel type form' and 'exporting' form (in the example posting) from one application to the next. I no longer spend ANY time designing complex reports or exporting routines!! (and if any types of totals need to be shown, I just simply design a query which has the totals.)

*****************
This 'snippet' of coding in the example has saved me many, many development hours and I can now get a very nice complete type reporting system operational in any of the applications I design within just a few minutes (again, I just import the forms from the example in the posting.) - I'm done! Then any new queries I develop are automatically reflected in the reporting system. I guarantee you this will make your life much, much easier and cut out over 75% of your report/export designing.
*****************

Regarding the | error. This usually indicates a bad expression in the query itself (which could lead back to a reference problem if the expression is something like 'Format(xxx...), a duplicated field name in the query, or a bad name of the query or filename (or folder name) you're exporting to (as a rule of thumb, I constantly harp on my users to never use spaces in any names, whether it be a filename, foldername, fieldname, tablename, etc...)

Other than a corrupt db, I might also check for bad data. A date type field which has a date value of 11/33 for one record could be problematic and cause an issue like this. Or the user entered an odd type of character when entering the data (ie. they accidently held down a few keys when entering to produce some kind ~ or other weird character in the data.)

Note: If it is bad data, what will happen is that the error will eventually go away when that specific data is no longer reported/exported on. You'll then think the issue is resolved when it "really" isn't. You can troubleshoot for bad data by limiting the criteria in the query to see if it then works ok.