How to format a date.

  • Thread starter Thread starter Julian Milano
  • Start date Start date
J

Julian Milano

I have a database which has dates in all sorts of formats, 2-digit,
4-digit..... days, months & years. I want to format the dates from a query
in a conforming way, but don't know how to do it. Here's my expression:


CaseStats: IIf(IsNull([DateClosed]),"[ CASE OPEN ]",[DateClosed])

It looks for a null date field and replaces the nulls with "[ CASE OPEN ]".
But how do I format the [DateClosed] field to be in the format "dd/mm/yyyy"
irrespective of the actual data's format?
 
Sorry, I figgered it out:

IIf(IsNull([DateClosed]),"[ CASE
OPEN ]",Format([DateClosed],"dd-mmm-yyyy")) AS CaseStats
 
I have a related question:

I want to have the following output for each condition:

CASE OPEN
[04/12/2004]

Ie. I want to put the square brackets around the dates only, using my
"format" line below. How do I do this? I've tried, but the brackets don't
come out since I think they are like a reserved character in Access!?

--


Julian Milano

Julian Milano said:
Sorry, I figgered it out:

IIf(IsNull([DateClosed]),"[ CASE
OPEN ]",Format([DateClosed],"dd-mmm-yyyy")) AS CaseStats

--


Julian Milano

Julian Milano said:
I have a database which has dates in all sorts of formats, 2-digit,
4-digit..... days, months & years. I want to format the dates from a query
in a conforming way, but don't know how to do it. Here's my expression:


CaseStats: IIf(IsNull([DateClosed]),"[ CASE OPEN ]",[DateClosed])

It looks for a null date field and replaces the nulls with "[ CASE OPEN ]".
But how do I format the [DateClosed] field to be in the format "dd/mm/yyyy"
irrespective of the actual data's format?
Click to expand...
Click to expand...
 
Use a back-slash character to indicate that the character that follows is to
be output as a literal character rather than a formatting code. For example
....

? format$(date(),"\[dd/mm/yyyy\]")
[10/05/2004]

Note the back-slash characters in front of the "[" and "]" characters.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Julian Milano said:
I have a related question:

I want to have the following output for each condition:

CASE OPEN
[04/12/2004]

Ie. I want to put the square brackets around the dates only, using my
"format" line below. How do I do this? I've tried, but the brackets don't
come out since I think they are like a reserved character in Access!?

--


Julian Milano

Julian Milano said:
Sorry, I figgered it out:

IIf(IsNull([DateClosed]),"[ CASE
OPEN ]",Format([DateClosed],"dd-mmm-yyyy")) AS CaseStats

--


Julian Milano

Julian Milano said:
I have a database which has dates in all sorts of formats, 2-digit,
4-digit..... days, months & years. I want to format the dates from a query
in a conforming way, but don't know how to do it. Here's my expression:


CaseStats: IIf(IsNull([DateClosed]),"[ CASE OPEN ]",[DateClosed])

It looks for a null date field and replaces the nulls with "[ CASE OPEN ]".
But how do I format the [DateClosed] field to be in the format "dd/mm/yyyy"
irrespective of the actual data's format?
Click to expand...
Click to expand...
Click to expand...
 
Thanks- it worked!

--

Julian Milano


Brendan Reynolds said:
Use a back-slash character to indicate that the character that follows is to
be output as a literal character rather than a formatting code. For example
...

? format$(date(),"\[dd/mm/yyyy\]")
[10/05/2004]

Note the back-slash characters in front of the "[" and "]" characters.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Julian Milano said:
I have a related question:

I want to have the following output for each condition:

CASE OPEN
[04/12/2004]

Ie. I want to put the square brackets around the dates only, using my
"format" line below. How do I do this? I've tried, but the brackets don't
come out since I think they are like a reserved character in Access!?

--


Julian Milano

Julian Milano said:
Sorry, I figgered it out:

IIf(IsNull([DateClosed]),"[ CASE
OPEN ]",Format([DateClosed],"dd-mmm-yyyy")) AS CaseStats

--


Julian Milano

I have a database which has dates in all sorts of formats, 2-digit,
4-digit..... days, months & years. I want to format the dates from a query
in a conforming way, but don't know how to do it. Here's my expression:


CaseStats: IIf(IsNull([DateClosed]),"[ CASE OPEN ]",[DateClosed])

It looks for a null date field and replaces the nulls with "[ CASE
OPEN ]".
But how do I format the [DateClosed] field to be in the format
"dd/mm/yyyy"
irrespective of the actual data's format?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top