Date conversion

[msanewbee]

I create a table from a .txt file. The file has a date in on of the fields.
The date does not have the leading 0 for the month or day. For example, it
would say 8/1/2006. I want to change it to 08/01/2006.

I also create a date field using =Date( ). It too does not put the leading
0s in.

There is not an input mask set up for mm/dd/yyyy where it will always keep
the the leading 0s. So I set up an input mask 00/00/0000;0;

But when I put this input mask on either of these date fields in my table
design, it doesn't do anything. Am I missing something? Is there a way to
make it so these fields will always be 00/00/0000??

Thanks, the newbee

 

[Al Camp]

msanewbee,
Date formats are diffrent than number and text formats...
Try a format of mm/dd/yyyy.
That should display 1/1/06 as 01/01/2006.

 

[msanewbee]

more help please......okay, I put mm/dd/yyyy in the input mask for my table
field that is defined as a text field and has the default value of Date().
It changes it to "mm"/"dd"/"yyyy". When I import my data is still comes in
as 8/4/2006. Then if I try to enter 08/04/2006 in that field, it tells me my
data is not appropriate for my mask. What am I doing wrong?

 

[Al Camp]

msanewbee'
I wrote...That's Format, not Input Mask.

 

[John Vinson]

more help please......okay, I put mm/dd/yyyy in the input mask for my table
field that is defined as a text field and has the default value of Date().
It changes it to "mm"/"dd"/"yyyy". When I import my data is still comes in
as 8/4/2006. Then if I try to enter 08/04/2006 in that field, it tells me my
data is not appropriate for my mask. What am I doing wrong?

Using a Text field.

If it's a Text field then *it is not a date*, and Access doesn't know
how to treat it as a date. It's a string of characters - an 8, and
then a slash, and then a 4 and so on.

If you change it to a Date/Time field, Access will convert it to a
number, a count of days since midnight December 30, 1899. This value
can be displayed in any number of different formats.

John W. Vinson[MVP]

 

[msanewbee]

Yea, that turned on a light!! I went back and changed one of my date fields
back to date/type data type w/a format of general date (the one w/the date
and time). That makes the sort on the date field work right. Thank you,
thank you!!

But now, I want a prompt in there, that will prompt the user to enter the
specific date. If I put the prompt in that field, they have to enter the
time also (at least that's how it looks to me). Do I need to string the date
to another field in my query? If I do that does access still recognize it as
a date field?

Is there a way to set up the field/prompt so when the user enters the date,
they could enter it with or without the leading zeros?

Thanks so much for the help

 

[msanewbee]

Hey, that works!!! It does help when you put it in the right place!!
Thank you!!

 

[John Vinson]

But now, I want a prompt in there, that will prompt the user to enter the
specific date. If I put the prompt in that field, they have to enter the
time also (at least that's how it looks to me). Do I need to string the date
to another field in my query? If I do that does access still recognize it as
a date field?

You can help the user by using a slightly more complex criterion:

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

This will prompt once for a date, and then search for any date/time
value on that date.

John W. Vinson[MVP]