Hi All,
This is a followup to my post 613410 on faking Access reporting capabilities in Excel. Thanks to Rory, I’m using the import external data/MS Query feature and it works pretty well.
This is what I’ve done:
– I have a data source where I’ve given my records a range name of something like Risk_DB. In that source, there are a few cols/fields that are formatted with a date format. Still, we have some entries in those fields like ? or ?? (yes, 1 or 2 question marks – don’t ask me what the difference is, someone else decided these were significant and different than blank). We also have some entries in those date fields that look like “10/02/06 per Security team” without the quotes. And we even have some things that really are just dates!
– I saved the query as a separate file (my thinking is that the query could not exist in the source file if the output goes in a separate file but I’m not sure of this or what combinations of locations are permitted)
– I set up a template that basically took the original table of the data source (Risk_DB), copied the column headings, and formatted a bunch of rows below that with borders, put in a page footer for printout purposes. I made sure that the rows below the heading are formatted the same way as the data source: text fields in the original are text formatted in the template, date fields are date formatted, custom formats are custom formats with the same format. This way I can send this file to those needing to see it; they don’t see my source and they don’t see the query
When I get the external data, some of the date information does NOT “come across” properly. For example:
– ? in a cell formatted as a date in the source comes across ok to a cell formatted as a date
– ?? in a cell formatted as a date in the source comes across ok for one field but not for another field
– what looks like to be a valid date in a cell formatted as a date in the source comes across ok for one field formatted as a date but not for another field
– a field formatted as a date containing “text date” or “date text” comes across ok for one field but not another
Changing entries that were ?? to ? didn’t help improve my “importation” score.
I’m not totally sure what’s going on here and really can’t make any rhyme or reason of what’s happening, especially with the 3rd item above (plain dates to plain dates). Changing my date formatting to text formatting seems to do the job but I would have thought that should not be necessary.
Any thoughts on the above?
TIA
Fred
