Input Mask - Date and Time

B

Beagle

Hello,

I need to create an Input Mask for the following:

Current Data shows: 102320070445
Desired result is: 10/23/2007 04:45

Not sure how to use the "add" input mask feature. What needs to go in the
following input mask fields when using the Customize Input Mask Wizard:

Description:
Input Mask:
Placeholder
Sample Data:
Mask Type:

Thanks,

Beagle
 
J

Jerry Whittle

An input mask restricts how data is entered into the database when someone is
typing it in. A format is how it's displayed. Do you want people to type in
something like 102320070445 into the field or do you what "102320070445" to
show up as 10/23/2007 04:45 ?

The next questionis what data type is the field in question? Probably not a
date/time field, therefore it could be number or text. If number the same
date minus a month would look like 92320070445 and have one fewer character
displayed as numbers don't have leading zeros.

I'm going to assume, and we all know what that means, that 102320070445 is
stored in a text field and you want it to look like a date. Unfortunately
none of the stardard date formats will work. Therefore I think it best to use
the Left and Mid functions to extract the parts of a data and make it look
like a date. In as query it would look something like:

TheDate: Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) & "/" &
Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2)

If you want to treat the above results like an actual date, you could wrap
it in the CDate function.
 
B

Beagle

Thanks for clarifying what an Input mask is.

Your assumption is correct.

I wanted to stay away from mid and left but that does seem to be the best
solution.

Though one question. What do you mean "wrap it in the CDate function"? Not
familar with this.

Thanks for your time

Jerry Whittle said:
An input mask restricts how data is entered into the database when someone is
typing it in. A format is how it's displayed. Do you want people to type in
something like 102320070445 into the field or do you what "102320070445" to
show up as 10/23/2007 04:45 ?

The next questionis what data type is the field in question? Probably not a
date/time field, therefore it could be number or text. If number the same
date minus a month would look like 92320070445 and have one fewer character
displayed as numbers don't have leading zeros.

I'm going to assume, and we all know what that means, that 102320070445 is
stored in a text field and you want it to look like a date. Unfortunately
none of the stardard date formats will work. Therefore I think it best to use
the Left and Mid functions to extract the parts of a data and make it look
like a date. In as query it would look something like:

TheDate: Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) & "/" &
Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2)

If you want to treat the above results like an actual date, you could wrap
it in the CDate function.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Beagle said:
Hello,

I need to create an Input Mask for the following:

Current Data shows: 102320070445
Desired result is: 10/23/2007 04:45

Not sure how to use the "add" input mask feature. What needs to go in the
following input mask fields when using the Customize Input Mask Wizard:

Description:
Input Mask:
Placeholder
Sample Data:
Mask Type:

Thanks,

Beagle
 
J

Jerry Whittle

The Left, Right, and other stuff below just creates a text string that looks
like a date. You couldn't compare it to another date or add/subtract days to
it for example. However the CDate function will take a text string and covert
it to an actual date IF it can be done. No 30 February 2007 for example.

Using the statement below, you could wrap it in CDate like so:

TheDate: CDate(Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) &
"/" & Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2))
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Beagle said:
Thanks for clarifying what an Input mask is.

Your assumption is correct.

I wanted to stay away from mid and left but that does seem to be the best
solution.

Though one question. What do you mean "wrap it in the CDate function"? Not
familar with this.

Thanks for your time

Jerry Whittle said:
An input mask restricts how data is entered into the database when someone is
typing it in. A format is how it's displayed. Do you want people to type in
something like 102320070445 into the field or do you what "102320070445" to
show up as 10/23/2007 04:45 ?

The next questionis what data type is the field in question? Probably not a
date/time field, therefore it could be number or text. If number the same
date minus a month would look like 92320070445 and have one fewer character
displayed as numbers don't have leading zeros.

I'm going to assume, and we all know what that means, that 102320070445 is
stored in a text field and you want it to look like a date. Unfortunately
none of the stardard date formats will work. Therefore I think it best to use
the Left and Mid functions to extract the parts of a data and make it look
like a date. In as query it would look something like:

TheDate: Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) & "/" &
Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2)

If you want to treat the above results like an actual date, you could wrap
it in the CDate function.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Beagle said:
Hello,

I need to create an Input Mask for the following:

Current Data shows: 102320070445
Desired result is: 10/23/2007 04:45

Not sure how to use the "add" input mask feature. What needs to go in the
following input mask fields when using the Customize Input Mask Wizard:

Description:
Input Mask:
Placeholder
Sample Data:
Mask Type:

Thanks,

Beagle
 
D

Douglas J. Steele

Actually, you should be able to use the Format function.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Beagle said:
Thanks for clarifying what an Input mask is.

Your assumption is correct.

I wanted to stay away from mid and left but that does seem to be the best
solution.

Though one question. What do you mean "wrap it in the CDate function"?
Not
familar with this.

Thanks for your time

Jerry Whittle said:
An input mask restricts how data is entered into the database when
someone is
typing it in. A format is how it's displayed. Do you want people to type
in
something like 102320070445 into the field or do you what "102320070445"
to
show up as 10/23/2007 04:45 ?

The next questionis what data type is the field in question? Probably not
a
date/time field, therefore it could be number or text. If number the same
date minus a month would look like 92320070445 and have one fewer
character
displayed as numbers don't have leading zeros.

I'm going to assume, and we all know what that means, that 102320070445
is
stored in a text field and you want it to look like a date. Unfortunately
none of the stardard date formats will work. Therefore I think it best to
use
the Left and Mid functions to extract the parts of a data and make it
look
like a date. In as query it would look something like:

TheDate: Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) & "/" &
Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2)

If you want to treat the above results like an actual date, you could
wrap
it in the CDate function.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Beagle said:
Hello,

I need to create an Input Mask for the following:

Current Data shows: 102320070445
Desired result is: 10/23/2007 04:45

Not sure how to use the "add" input mask feature. What needs to go in
the
following input mask fields when using the Customize Input Mask Wizard:

Description:
Input Mask:
Placeholder
Sample Data:
Mask Type:

Thanks,

Beagle
 
D

Douglas J. Steele

Oops: sent too soon.

You should be able to use the Format function:

?Format("102320070445", "00/00/0000 00:00")
10/23/2007 04:45

What Jerry meant by "wrap it in the CDate function" is put the result of
formatting it properly as the argument for the CDate function so that the
result is an actual Date value, not a string.

That means you could use
TheDate: CDate(Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) &
"/" & Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2))

or

TheDate: CDate(Format([YourDateField], "00/00/0000 00:00"))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Beagle said:
Thanks for clarifying what an Input mask is.

Your assumption is correct.

I wanted to stay away from mid and left but that does seem to be the best
solution.

Though one question. What do you mean "wrap it in the CDate function"?
Not
familar with this.

Thanks for your time

Jerry Whittle said:
An input mask restricts how data is entered into the database when
someone is
typing it in. A format is how it's displayed. Do you want people to type
in
something like 102320070445 into the field or do you what "102320070445"
to
show up as 10/23/2007 04:45 ?

The next questionis what data type is the field in question? Probably not
a
date/time field, therefore it could be number or text. If number the same
date minus a month would look like 92320070445 and have one fewer
character
displayed as numbers don't have leading zeros.

I'm going to assume, and we all know what that means, that 102320070445
is
stored in a text field and you want it to look like a date. Unfortunately
none of the stardard date formats will work. Therefore I think it best to
use
the Left and Mid functions to extract the parts of a data and make it
look
like a date. In as query it would look something like:

TheDate: Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) & "/" &
Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2)

If you want to treat the above results like an actual date, you could
wrap
it in the CDate function.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Beagle said:
Hello,

I need to create an Input Mask for the following:

Current Data shows: 102320070445
Desired result is: 10/23/2007 04:45

Not sure how to use the "add" input mask feature. What needs to go in
the
following input mask fields when using the Customize Input Mask Wizard:

Description:
Input Mask:
Placeholder
Sample Data:
Mask Type:

Thanks,

Beagle
 
B

Beagle

Thanks but I'm not sure where to put

?Format("102320070445", "00/00/0000 00:00")


Beagle

Douglas J. Steele said:
Oops: sent too soon.

You should be able to use the Format function:

?Format("102320070445", "00/00/0000 00:00")
10/23/2007 04:45

What Jerry meant by "wrap it in the CDate function" is put the result of
formatting it properly as the argument for the CDate function so that the
result is an actual Date value, not a string.

That means you could use
TheDate: CDate(Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) &
"/" & Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2))

or

TheDate: CDate(Format([YourDateField], "00/00/0000 00:00"))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Beagle said:
Thanks for clarifying what an Input mask is.

Your assumption is correct.

I wanted to stay away from mid and left but that does seem to be the best
solution.

Though one question. What do you mean "wrap it in the CDate function"?
Not
familar with this.

Thanks for your time

Jerry Whittle said:
An input mask restricts how data is entered into the database when
someone is
typing it in. A format is how it's displayed. Do you want people to type
in
something like 102320070445 into the field or do you what "102320070445"
to
show up as 10/23/2007 04:45 ?

The next questionis what data type is the field in question? Probably not
a
date/time field, therefore it could be number or text. If number the same
date minus a month would look like 92320070445 and have one fewer
character
displayed as numbers don't have leading zeros.

I'm going to assume, and we all know what that means, that 102320070445
is
stored in a text field and you want it to look like a date. Unfortunately
none of the stardard date formats will work. Therefore I think it best to
use
the Left and Mid functions to extract the parts of a data and make it
look
like a date. In as query it would look something like:

TheDate: Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) & "/" &
Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2)

If you want to treat the above results like an actual date, you could
wrap
it in the CDate function.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hello,

I need to create an Input Mask for the following:

Current Data shows: 102320070445
Desired result is: 10/23/2007 04:45

Not sure how to use the "add" input mask feature. What needs to go in
the
following input mask fields when using the Customize Input Mask Wizard:

Description:
Input Mask:
Placeholder
Sample Data:
Mask Type:

Thanks,

Beagle
 
D

Douglas J. Steele

Sorry, all that was was a copy from the Immediate window, showing how the
function worked.

My comments were strictly aimed at Jerry's latter points.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Beagle said:
Thanks but I'm not sure where to put

?Format("102320070445", "00/00/0000 00:00")


Beagle

Douglas J. Steele said:
Oops: sent too soon.

You should be able to use the Format function:

?Format("102320070445", "00/00/0000 00:00")
10/23/2007 04:45

What Jerry meant by "wrap it in the CDate function" is put the result of
formatting it properly as the argument for the CDate function so that the
result is an actual Date value, not a string.

That means you could use
TheDate: CDate(Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) &
"/" & Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2))

or

TheDate: CDate(Format([YourDateField], "00/00/0000 00:00"))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Beagle said:
Thanks for clarifying what an Input mask is.

Your assumption is correct.

I wanted to stay away from mid and left but that does seem to be the
best
solution.

Though one question. What do you mean "wrap it in the CDate function"?
Not
familar with this.

Thanks for your time

:

An input mask restricts how data is entered into the database when
someone is
typing it in. A format is how it's displayed. Do you want people to
type
in
something like 102320070445 into the field or do you what
"102320070445"
to
show up as 10/23/2007 04:45 ?

The next questionis what data type is the field in question? Probably
not
a
date/time field, therefore it could be number or text. If number the
same
date minus a month would look like 92320070445 and have one fewer
character
displayed as numbers don't have leading zeros.

I'm going to assume, and we all know what that means, that
102320070445
is
stored in a text field and you want it to look like a date.
Unfortunately
none of the stardard date formats will work. Therefore I think it best
to
use
the Left and Mid functions to extract the parts of a data and make it
look
like a date. In as query it would look something like:

TheDate: Left([YourDateField],2) & "/" & Mid([YourDateField],3,2) &
"/" &
Mid([YourDateField],5,4) & " " & Mid([YourDateField],9,2) & ":" &
Mid([YourDateField],11,2)

If you want to treat the above results like an actual date, you could
wrap
it in the CDate function.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hello,

I need to create an Input Mask for the following:

Current Data shows: 102320070445
Desired result is: 10/23/2007 04:45

Not sure how to use the "add" input mask feature. What needs to go
in
the
following input mask fields when using the Customize Input Mask
Wizard:

Description:
Input Mask:
Placeholder
Sample Data:
Mask Type:

Thanks,

Beagle
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top