When I import an Excel file into Access, how do I make Access keep the date in the MM/DD/YYYY format and also not put a time stamp onto it? (And where does the time stamp come from? Does Access just make it up?)
Okay, I found where the time stamps are coming from. The date column in Access is apparently formatted a date. If I delete the slashes I get the time stamp. For example, 05/22/2007 becomes 5222007 11:49:59 AM. If I copy the date and Paste Special, Value, then I get 39224.49304. So that’s where the time stamp is coming from.
Anyway, I still need to know how to make Access put the date into MM/DD/YY format.
Hey, Johnny!
This is going to be difficult. Access has a “datetime” type field (rather than two different types like Excel does), so it always automatically adds a time (usually 00:00:00) to any date.
Unfortunately, importing from Excel doesn’t allow you to choose your own field type.
Changing the field type in Excel does the same thing as your “Paste Special”, it gives you the Excel serial value of the date.
I usually just ignore the time, it doesn’t cause problems sitting in the cell, you can export/manipulate the data without it, etc.
Is there a reason that the time part of the field is causing you problems?
As per above, you have a conceptual issue. A date/time field has to be stored with a time even if it is just zero. It isn’t just a text string. This is very beneficial when you want to manipulate dates like calculating days between them. You can’t get rid of the time stamps if the field type remains a date but you can format it as a date only when you display it on a form or report. It is in your best interest to leave the back end alone and format at the front end.
Hi Johnny!
If you need Access to display it in mm/dd/yy format, that’s easy. There are several ways, some better than others depending on how you’re looking at the data. For a Report or a Form, I suggest using the Format property of the control that’s showing the date field and setting it to “Short Date” (one of the built-in options).
If you’re looking at the data in Query view, things can be a bit more tricky, but it CAN be done. In this case, you want to modify your query so that it formats the date for you, because queries do not have any formatting options built in. The Format() function will work just fine. Assuming your field is named Date (which is a bad idea, by the way, but we’ll ignore that for now), the following would work for what you want:
Format([Date], "mm/dd/yy")
So what you would do is put that statement in the query in one of the columns and it would appear as a formatted date when you run the query. Be warned, though: Format() returns a text value, which can cause confusion because it will not be considered the same as a date value, even though they’re the same date. Strange, I know, but them’s the breaks. Excel does the same thing.
Finally, if you’re looking at the data in Table view… don’t. There’s no way to change the format of a field in that view. The Format property of fields when you’re looking at a table in Design mode simply tells Access what format to apply to CONTROLS that are built based on that field, it doesn’t do anything when you look at the data itself through the table. Confusing, sure, and kinda irritating, but you’re not supposed to be looking at data this way anyway. 
If this doesn’t make any sense, feel free to PM me and I can help you sort it out.
(Access/Excel developer for… um… about ten years now, eep!)
I ultimately have tom come up with a file that contains the date in MMYY format. Here’s my code:
WS-DOLS-A-IN = '0000000000'
SUB1 = 9
SUB1B = 9
DO WHILE SUB1 GE 0
IF DOLS-BYTE-IN1 NUMERIC
DOLS-BYTE-IN1B = DOLS-BYTE-IN1
SUB1 = SUB1 - 1
SUB1B = SUB1B - 1
ELSE
SUB1 = SUB1 - 1
END-IF
END-DO
MOVE WS-DOLS-A-IN TO DOLS
DISPLAY DOLS
CDR-DOLS-MM = DOLS-MM
CDR-DOLS-YY = DOLS-YY
Basically I’m putting zeroes in the date (DOLS) working storage. Then I’m reading the field from right to left. If there’s a number there (not a space or a slash) it writes the byte to IN1B and goes to the next byte. If it’s not numeric, it doesn’t write out the byte and goes to the next one to the left.
The problem arises when there’s a short date. For example, 6/8/2007. The time stamp intrudes into the field I’ve defined as the date. (Easytrieve uses fixed fields. You need to tell it the starting position and how long it is.) After importing the Excel file into Access, defining the field positions, and exporting as .txt this particular date would be 6/8/2007 1. When it goes through the Easytrieve it comes out (in MMYY format) as 0671.
I’m sure I can code my way around it in Easytrieve, but it would be nice if the input were cleaner!
Ah, this makes things clearer. Good news: use the Format() function I described above, and you will get fixed-length output regardless of input l
length. The function will pad the values with zeroes where needed to make it match the mm/dd/yy format.
ETA: if you need differing output, the Format() function will accept just about any text string as a format expression – for example, “mm/yy” would give you month and year in two-digit format. Check out Access’s online Visual Basic help for clarification on the format codes used by this function, if you need.
Sofaspud: I don’t seem to have a ‘Query view’ or a ‘Table view’. I do have a ‘Datasheet view’ and ‘Design view’ though. I went to Design view and changed the date to Text. When I went back to Datasheet view it still had the times in it. It seems I have to do something with ‘Query view’ so that I can specify the date format (it’s not called ‘Date’, BTW), only I don’t see ‘Query view’ in the View menu.
I tried to PM you, but you’re not accepting. My email is in my profile.
Thanks!
Funky. I’m set to accept, but hey. Email sent!
Hm. Hasn’t come through yet. Email server must be on its lunch break.
Sofaspud: Thanks for your help.