Date conversion

[Guest]

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.

 

[Guest]

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?

 

[Joseph Meehan]

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

You don't want to "convert" the dates, you want to change the way they
are displayed.

You can define the display format in forms, queries and reports. Tables
don't seem to allow as much versatility, but then a table is nothing more
than a bucket of data and field descriptions and should not be used to
display or edit data.

 

[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]

 

[Guest]

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

 

[Guest]

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]