how do i put text values in date/time field in microsoft access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having trouble setting a date/time data type field to accept text values.

For example,
Currently the date/time can only put dates (5-29-2005)

But, I want to put text in there too

For example: if the date is not available I would like put "N/A" to indicate
that date is not available.
 
Answered in your OTHER post. Please do not post multiple times. You don't
need several different people answering your question only to find it
already answered in another group. That is very annoying for the person who
spends their time and effort trying to help you out.
 
Hi Lynn,
First, Thanks for your reply to my question. I changed it to Text field.
Then what do i do to make it accept dates. Such as

I want to just enter 5-29 and press enter and have it automatically fill in
as 5/29/2005.

Is there a way to do that?
 
Sorry for the confusion of posting it more than once. It did not give me a
confimation the first time i posted it, so i had post it again. So, I guess
both went though. Thanks for your help and time.
 
First, Thanks for your reply to my question. I changed it to Text
field.
Then what do i do to make it accept dates. Such as

I want to just enter 5-29 and press enter and have it automatically fill
in
as 5/29/2005.
Click to expand...

You can't do that directly in the table and/or a data entry form. You can,
however, cause it to DISPLAY that on a form or report by putting the
following in the control source of a text box.

=Format([YourTextDateField],"mm/dd/yyyy")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html
 
Don't we need the police?

or a one stop shop for newbie's to understand how to use the newsgroups
properly. As surely all (well me at least) we want to do is answer good
techi questions, rather than spend our time pointing out mistakes.

e.g.

if we all used the same paragraphs for wrong newsgroup or cross posting
things would look a bit more consistent and less time spent typing at the
keyboard.
 
Hi lynn!
Hope you had a good weekend. I've changed the control source to
=Format([YourTextDateField],"mm/dd/yyyy") . The format you taught me. I
went into the form and go under properties of the text box, and changed the
control source to that. In the form view, it displays as "error?". What am
i doing wrong?
I changed the data field to text in the table, then went into form design
view and changed the control soure to =Format([Date comp],"mm/dd/yyyy"). and
it diplays as error.
Date Comp = is the name of the text field. Thanks for your help and
understanding!

Lynn Trapp said:
First, Thanks for your reply to my question. I changed it to Text
field.
Then what do i do to make it accept dates. Such as

I want to just enter 5-29 and press enter and have it automatically fill
in
as 5/29/2005.
Click to expand...

You can't do that directly in the table and/or a data entry form. You can,
however, cause it to DISPLAY that on a form or report by putting the
following in the control source of a text box.

=Format([YourTextDateField],"mm/dd/yyyy")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


tina said:
Hi Lynn,
First, Thanks for your reply to my question. I changed it to Text
field.
Then what do i do to make it accept dates. Such as

I want to just enter 5-29 and press enter and have it automatically fill
in
as 5/29/2005.

Is there a way to do that?
Click to expand...
Click to expand...
 
Place an unbound text box on your form and give it a name similar to the one
below (Me.txtYourDateField) Then put the following code in the Current Event
of your form, making sure you name the fields appropriately:

Private Sub Form_Current()

Me.txtYourDateField = Format(Me.YourDateField, "mm/dd/yyyy")

End Sub

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


tina said:
Hi lynn!
Hope you had a good weekend. I've changed the control source to
=Format([YourTextDateField],"mm/dd/yyyy") . The format you taught me. I
went into the form and go under properties of the text box, and changed
the
control source to that. In the form view, it displays as "error?". What
am
i doing wrong?
I changed the data field to text in the table, then went into form design
view and changed the control soure to =Format([Date comp],"mm/dd/yyyy").
and
it diplays as error.
Date Comp = is the name of the text field. Thanks for your help and
understanding!

Lynn Trapp said:
First, Thanks for your reply to my question. I changed it to Text
field.
Then what do i do to make it accept dates. Such as

I want to just enter 5-29 and press enter and have it automatically
fill
in
as 5/29/2005.
Click to expand...

You can't do that directly in the table and/or a data entry form. You
can,
however, cause it to DISPLAY that on a form or report by putting the
following in the control source of a text box.

=Format([YourTextDateField],"mm/dd/yyyy")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


tina said:
Hi Lynn,
First, Thanks for your reply to my question. I changed it to Text
field.
Then what do i do to make it accept dates. Such as

I want to just enter 5-29 and press enter and have it automatically
fill
in
as 5/29/2005.

Is there a way to do that?

:

You can't do that if the datatype of your field is Date. You would
have
to
make your field a Text field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


I am having trouble setting a date/time data type field to accept
text
values.

For example,
Currently the date/time can only put dates (5-29-2005)

But, I want to put text in there too

For example: if the date is not available I would like put "N/A" to
indicate
that date is not available.
Click to expand...
Click to expand...
Click to expand...
 
Hi Lynn,
First thanks for your patience replies to my postings. I am still having
trouble entering the code you gave me. Where is the Current Event of the
form? I've searched and try entering it in validation rules and it does not
work. I am totally losted here, Is there anyway you can still help me by
giving my step by step instructions?

The text field is call "Date comp"

I am trying to make it able to accept text (mostly just"n/a") and accept
date values.

Thanks for your patience and time,
Tina Li

Lynn Trapp said:
Place an unbound text box on your form and give it a name similar to the one
below (Me.txtYourDateField) Then put the following code in the Current Event
of your form, making sure you name the fields appropriately:

Private Sub Form_Current()

Me.txtYourDateField = Format(Me.YourDateField, "mm/dd/yyyy")

End Sub

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


tina said:
Hi lynn!
Hope you had a good weekend. I've changed the control source to
=Format([YourTextDateField],"mm/dd/yyyy") . The format you taught me. I
went into the form and go under properties of the text box, and changed
the
control source to that. In the form view, it displays as "error?". What
am
i doing wrong?
I changed the data field to text in the table, then went into form design
view and changed the control soure to =Format([Date comp],"mm/dd/yyyy").
and
it diplays as error.
Date Comp = is the name of the text field. Thanks for your help and
understanding!

Lynn Trapp said:
First, Thanks for your reply to my question. I changed it to Text
field.
Then what do i do to make it accept dates. Such as

I want to just enter 5-29 and press enter and have it automatically
fill
in
as 5/29/2005.

You can't do that directly in the table and/or a data entry form. You
can,
however, cause it to DISPLAY that on a form or report by putting the
following in the control source of a text box.

=Format([YourTextDateField],"mm/dd/yyyy")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Hi Lynn,
First, Thanks for your reply to my question. I changed it to Text
field.
Then what do i do to make it accept dates. Such as

I want to just enter 5-29 and press enter and have it automatically
fill
in
as 5/29/2005.

Is there a way to do that?

:

You can't do that if the datatype of your field is Date. You would
have
to
make your field a Text field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


I am having trouble setting a date/time data type field to accept
text
values.

For example,
Currently the date/time can only put dates (5-29-2005)

But, I want to put text in there too

For example: if the date is not available I would like put "N/A" to
indicate
that date is not available.
Click to expand...
Click to expand...
Click to expand...
 
HI Lynn,
Thanks for your patience help and advises.. i've got it to work.. thanks a
LOT! have a great day!

Lynn Trapp said:
Place an unbound text box on your form and give it a name similar to the one
below (Me.txtYourDateField) Then put the following code in the Current Event
of your form, making sure you name the fields appropriately:

Private Sub Form_Current()

Me.txtYourDateField = Format(Me.YourDateField, "mm/dd/yyyy")

End Sub

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


tina said:
Hi lynn!
Hope you had a good weekend. I've changed the control source to
=Format([YourTextDateField],"mm/dd/yyyy") . The format you taught me. I
went into the form and go under properties of the text box, and changed
the
control source to that. In the form view, it displays as "error?". What
am
i doing wrong?
I changed the data field to text in the table, then went into form design
view and changed the control soure to =Format([Date comp],"mm/dd/yyyy").
and
it diplays as error.
Date Comp = is the name of the text field. Thanks for your help and
understanding!

Lynn Trapp said:
First, Thanks for your reply to my question. I changed it to Text
field.
Then what do i do to make it accept dates. Such as

I want to just enter 5-29 and press enter and have it automatically
fill
in
as 5/29/2005.

You can't do that directly in the table and/or a data entry form. You
can,
however, cause it to DISPLAY that on a form or report by putting the
following in the control source of a text box.

=Format([YourTextDateField],"mm/dd/yyyy")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Hi Lynn,
First, Thanks for your reply to my question. I changed it to Text
field.
Then what do i do to make it accept dates. Such as

I want to just enter 5-29 and press enter and have it automatically
fill
in
as 5/29/2005.

Is there a way to do that?

:

You can't do that if the datatype of your field is Date. You would
have
to
make your field a Text field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


I am having trouble setting a date/time data type field to accept
text
values.

For example,
Currently the date/time can only put dates (5-29-2005)

But, I want to put text in there too

For example: if the date is not available I would like put "N/A" to
indicate
that date is not available.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top