Date Expansion

[Hendrix10]

I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like: "Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")" to
get 01/01/2007 but instead I got 01/01/2107. What should I have done
to get "01/01/2007"? And, why did I get 2107 as a result?

 

[Douglas J. Steele]

Try:

Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

 

[Hendrix10]

I tried what this and I received an error message stating "Data type
mismatch in criteria expression"

Try:

Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like: "Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")" to
get 01/01/2007 but instead I got 01/01/2107. What should I have done
to get "01/01/2007"? And, why did I get 2107 as a result?- Hide quoted text -

- Show quoted text -

 

[Douglas J. Steele]

What's the data type of [date]? (I hope that's not the actual name of the
field, btw: Date is a reserved word, and should never be used for your own
purposes)

Try the following to determine what specifically is failing:

Format([date],"00\/00\/00")
CDate(Format([date],"00\/00\/00"))
Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried what this and I received an error message stating "Data type
mismatch in criteria expression"

Try:

Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like: "Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")" to
get 01/01/2007 but instead I got 01/01/2107. What should I have done
to get "01/01/2007"? And, why did I get 2107 as a result?- Hide quoted
text -

- Show quoted text -

 

[Hendrix10]

Ok, I will try them. I brought it in as a text field because whenever
I try to bring a field in as date/time there is an import error and
the column is blank.

And, I just changed the name to date for simplicity.

What's the data type of [date]? (I hope that's not the actual name of the
field, btw: Date is a reserved word, and should never be used for your own
purposes)

Try the following to determine what specifically is failing:

Format([date],"00\/00\/00")
CDate(Format([date],"00\/00\/00"))
Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I tried what this and I received an error message stating "Data type
mismatch in criteria expression"

Try:
Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like: "Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")" to
get 01/01/2007 but instead I got 01/01/2107. What should I have done
to get "01/01/2007"? And, why did I get 2107 as a result?- Hide quoted
text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Hendrix10]

I tried that and I recieve a "Data type mismatch in criteria
expression" error message.

Try:

Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like: "Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")" to
get 01/01/2007 but instead I got 01/01/2107. What should I have done
to get "01/01/2007"? And, why did I get 2107 as a result?- Hide quoted text -

- Show quoted text -

 

[Hendrix10]

I tried the 3 optionss I was given and the 1st one failed and the
other two I recieved syntax errors.

I tried that and I recieve a "Data type mismatch in criteria
expression" error message.

Try:

Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

news:[email protected]...

I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like: "Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")" to
get 01/01/2007 but instead I got 01/01/2107. What should I have done
to get "01/01/2007"? And, why did I get 2107 as a result?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[George Nicholson]

From previous posts, I'm pretty sure that what the OP has stored is not a
date value (where 39083 is actually being stored to represent Jan 1, 2007),
but a text value representing a date ("010107").

In which case, I don't think he can do what he wants without adding the
century values to the original string or converting it to an actual date.

What's the data type of [date]? (I hope that's not the actual name of the
field, btw: Date is a reserved word, and should never be used for your own
purposes)

Try the following to determine what specifically is failing:

Format([date],"00\/00\/00")
CDate(Format([date],"00\/00\/00"))
Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried what this and I received an error message stating "Data type
mismatch in criteria expression"

Try:

Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")





I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like: "Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")" to
get 01/01/2007 but instead I got 01/01/2107. What should I have done
to get "01/01/2007"? And, why did I get 2107 as a result?- Hide quoted
text -

- Show quoted text -

 

[Hendrix10]

what is the century value? And, how can I change it to an actual date?

From previous posts, I'm pretty sure that what the OP has stored is not a
date value (where 39083 is actually being stored to represent Jan 1, 2007),
but a text value representing a date ("010107").

In which case, I don't think he can do what he wants without adding the
century values to the original string or converting it to an actual date.

What's the data type of [date]? (I hope that's not the actual name of the
field, btw: Date is a reserved word, and should never be used for your own
purposes)

Try the following to determine what specifically is failing:

Format([date],"00\/00\/00")
CDate(Format([date],"00\/00\/00"))
Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

I tried what this and I received an error message stating "Data type
mismatch in criteria expression"
On Oct 19, 10:26 am, "Douglas J. Steele"
Try:
Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like: "Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")" to
get 01/01/2007 but instead I got 01/01/2107. What should I have done
to get "01/01/2007"? And, why did I get 2107 as a result?- Hide quoted
text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Douglas J. Steele]

Don't think so. From the Immediate Window:

?Format(CDate(Format("101907", "00\/00\/00")), "mm/dd/yyyy")
10-19-2007

Now, you're stuck with whatever's set in Regional Settings for how it's
going to interpret 2 digit dates. For example, on mine, it's set to
interpret 2 digits years as being between 1930 and 2029, so:

?Format(CDate(Format("101929", "00\/00\/00")), "mm/dd/yyyy")
10-19-2029
?Format(CDate(Format("101930", "00\/00\/00")), "mm/dd/yyyy")
10-19-1930


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

From previous posts, I'm pretty sure that what the OP has stored is not a
date value (where 39083 is actually being stored to represent Jan 1,
2007), but a text value representing a date ("010107").

In which case, I don't think he can do what he wants without adding the
century values to the original string or converting it to an actual date.

What's the data type of [date]? (I hope that's not the actual name of the
field, btw: Date is a reserved word, and should never be used for your
own purposes)

Try the following to determine what specifically is failing:

Format([date],"00\/00\/00")
CDate(Format([date],"00\/00\/00"))
Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried what this and I received an error message stating "Data type
mismatch in criteria expression"

On Oct 19, 10:26 am, "Douglas J. Steele"
Try:

Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")





I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like: "Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")" to
get 01/01/2007 but instead I got 01/01/2107. What should I have done
to get "01/01/2007"? And, why did I get 2107 as a result?- Hide
quoted text -

- Show quoted text -

 

[Hendrix10]

Is there somewhere I can change my regional settings?

Don't think so. From the Immediate Window:

?Format(CDate(Format("101907", "00\/00\/00")), "mm/dd/yyyy")
10-19-2007

Now, you're stuck with whatever's set in Regional Settings for how it's
going to interpret 2 digit dates. For example, on mine, it's set to
interpret 2 digits years as being between 1930 and 2029, so:

?Format(CDate(Format("101929", "00\/00\/00")), "mm/dd/yyyy")
10-19-2029
?Format(CDate(Format("101930", "00\/00\/00")), "mm/dd/yyyy")
10-19-1930

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

From previous posts, I'm pretty sure that what the OP has stored is not a
date value (where 39083 is actually being stored to represent Jan 1,
2007), but a text value representing a date ("010107").

In which case, I don't think he can do what he wants without adding the
century values to the original string or converting it to an actual date.

What's the data type of [date]? (I hope that's not the actual name of the
field, btw: Date is a reserved word, and should never be used for your
own purposes)
Try the following to determine what specifically is failing:
Format([date],"00\/00\/00")
CDate(Format([date],"00\/00\/00"))
Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
I tried what this and I received an error message stating "Data type
mismatch in criteria expression"
On Oct 19, 10:26 am, "Douglas J. Steele"
Try:
Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like: "Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")" to
get 01/01/2007 but instead I got 01/01/2107. What should I have done
to get "01/01/2007"? And, why did I get 2107 as a result?- Hide
quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Douglas J. Steele]

In the Control Panel (but be aware that it'll impact all applications on the
machine)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Is there somewhere I can change my regional settings?

Don't think so. From the Immediate Window:

?Format(CDate(Format("101907", "00\/00\/00")), "mm/dd/yyyy")
10-19-2007

Now, you're stuck with whatever's set in Regional Settings for how it's
going to interpret 2 digit dates. For example, on mine, it's set to
interpret 2 digits years as being between 1930 and 2029, so:

?Format(CDate(Format("101929", "00\/00\/00")), "mm/dd/yyyy")
10-19-2029
?Format(CDate(Format("101930", "00\/00\/00")), "mm/dd/yyyy")
10-19-1930

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

From previous posts, I'm pretty sure that what the OP has stored is not
a
date value (where 39083 is actually being stored to represent Jan 1,
2007), but a text value representing a date ("010107").

In which case, I don't think he can do what he wants without adding the
century values to the original string or converting it to an actual
date.

message
What's the data type of [date]? (I hope that's not the actual name of
the
field, btw: Date is a reserved word, and should never be used for your
own purposes)

Try the following to determine what specifically is failing:

Format([date],"00\/00\/00")
CDate(Format([date],"00\/00\/00"))
Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

I tried what this and I received an error message stating "Data type
mismatch in criteria expression"

On Oct 19, 10:26 am, "Douglas J. Steele"
Try:

Format(CDate(Format([date],"00\/00\/00")), "mm/dd/yyyy")

news:[email protected]...

I have a field ("010107") that has been formated as a date field
("01/01/07") but I would like to know if I can change it to
01/01/2007. Currently my format looks like:
"Format([date],"00\/00\/
00"). I tried to change it to "Format([last chk],"00\/00\/2000")"
to
get 01/01/2007 but instead I got 01/01/2107. What should I have
done
to get "01/01/2007"? And, why did I get 2107 as a result?- Hide
quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -