Feb 13 2007, 11:15 AM
I'm having a couple issues with exporting queries as delimited comma quoted files. The file after export is different than the preview box in the Export Text Wizard Box. The last field was blank and is left off but I need it to be ,"",. For one file there is a series of blank fields and it comes out , , , , instead of , "" , "" , "" ,. In another file the date is formatted to the Short Date (2/13/2007) and I've set it to be exported as 070213. It appears this way in the Export Text Wizard Box but when I open the file it shows up in the Short Date format. I don't understand why the actual output is different than the preview box. Thanks for the help.
Feb 13 2007, 12:50 PM
1) Assuming that the field is Null, not blank, set it to Nz([Fieldname], "")
2) Don't set the Format property of the field: use the Format function as a calculated field.
Feb 20 2007, 09:16 AM
Your suggestion worked for my one issus. That was great. I still don't understand about the date format. Why would the date format change from what the preview box showed? Thanks for the help.
Feb 20 2007, 09:48 AM
Using the Format propery of a field changes how the value stored in the field appears in Access, but does not change the actual value stored.
When you export the query, it doesn't worry about formats: it worries about values. You'll see the same phenomenon with numeric fields: you may have your query only show, say, 2 decimal points on the screen in Access, but when you export the query, your output will contain all of the decimal points stored for the number.
Using the Format function instead, though, actually changes the value: when you have
in a query, as far as Access is concerned, that field in the query is now a text string, not the underlying date value. (Using Format on the field will also handle the decimal point issue I mentioned above)
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here