Removing "seconds" off General Date on a report.

[Ryan]

Hello,
I'm new to Access and DB's in general. I've taken over some light
duty support for a lab information system we use in house. Most of our
dates are reported as "10/31/2006 12:30:00 PM" (With seconds listed in
the report field).

However, nobody wants to see the seconds. So I tried to format it
=Format(DateCollected, "mm/dd/yy hh:nn AM/PM").

Works great, right? Well.. yeah for the most part. However if we
have something come in from a client and they list a date with no time,
then the report spits out "10/30/06 12:00 AM" since the date is empty,
the formatting assumes it's 0 Hour I guess.
So in short, the formatting works unless we have no time recorded for
the particular item we are testing.

Is there a way to get the format to ignore the hh:nn AM/PM section if
no time exists? Keep in mind these are when generating reports that go
back to the client... so they must be accurate.

Thank you, I hope I've made it clear what I'm looking for.

-Ryan

[Kc-Mass]

Ryan
Try this. At one point you say the date is empty But I think you meant there
is no time, yes?

= IIF( Instr(Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),"12:00 AM") 0,
Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),
Format(DateCollected, "mm/dd/yy "))

Kevin C

"Ryan" <mi********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

Hello,
I'm new to Access and DB's in general. I've taken over some light
duty support for a lab information system we use in house. Most of our
dates are reported as "10/31/2006 12:30:00 PM" (With seconds listed in
the report field).

However, nobody wants to see the seconds. So I tried to format it
=Format(DateCollected, "mm/dd/yy hh:nn AM/PM").

Works great, right? Well.. yeah for the most part. However if we
have something come in from a client and they list a date with no time,
then the report spits out "10/30/06 12:00 AM" since the date is empty,
the formatting assumes it's 0 Hour I guess.
So in short, the formatting works unless we have no time recorded for
the particular item we are testing.

Is there a way to get the format to ignore the hh:nn AM/PM section if
no time exists? Keep in mind these are when generating reports that go
back to the client... so they must be accurate.

Thank you, I hope I've made it clear what I'm looking for.

-Ryan

[Ryan]

Kevin,
Yes I apologize, I meant time. There's always a date entered, but
sometimes not a time.

I'll give that a whack here.. I appreciate it.
Kc-Mass wrote:

Ryan
Try this. At one point you say the date is empty But I think you meant there
is no time, yes?

= IIF( Instr(Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),"12:00 AM") 0,
Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),
Format(DateCollected, "mm/dd/yy "))

Kevin C

"Ryan" <mi********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

Hello,
I'm new to Access and DB's in general. I've taken over some light
duty support for a lab information system we use in house. Most of our
dates are reported as "10/31/2006 12:30:00 PM" (With seconds listed in
the report field).

However, nobody wants to see the seconds. So I tried to format it
=Format(DateCollected, "mm/dd/yy hh:nn AM/PM").

Works great, right? Well.. yeah for the most part. However if we
have something come in from a client and they list a date with no time,
then the report spits out "10/30/06 12:00 AM" since the date is empty,
the formatting assumes it's 0 Hour I guess.
So in short, the formatting works unless we have no time recorded for
the particular item we are testing.

Is there a way to get the format to ignore the hh:nn AM/PM section if
no time exists? Keep in mind these are when generating reports that go
back to the client... so they must be accurate.

Thank you, I hope I've made it clear what I'm looking for.

-Ryan

[Ryan]

Kevin,

Ok it worked. Not at first, I had to adjust it to this:

=IIf(InStr(Format([DateCollected],"mm/dd/yy hh:nn AM/PM"),"12:00
AM")>0,Format([DateCollected],"mm/dd/yy"),Format([DateCollected],"mm/dd/yy
hh:nn AM/PM"))

So I just reversed the second and third Format statements....

Thanks!
Ryan wrote:

Kevin,
Yes I apologize, I meant time. There's always a date entered, but
sometimes not a time.

I'll give that a whack here.. I appreciate it.
Kc-Mass wrote:

Ryan
Try this. At one point you say the date is empty But I think you meant there
is no time, yes?

= IIF( Instr(Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),"12:00 AM") 0,
Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),
Format(DateCollected, "mm/dd/yy "))

Kevin C

"Ryan" <mi********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

Hello,
I'm new to Access and DB's in general. I've taken over some light
duty support for a lab information system we use in house. Most of our
dates are reported as "10/31/2006 12:30:00 PM" (With seconds listed in
the report field).
>
However, nobody wants to see the seconds. So I tried to format it
=Format(DateCollected, "mm/dd/yy hh:nn AM/PM").
>
Works great, right? Well.. yeah for the most part. However if we
have something come in from a client and they list a date with no time,
then the report spits out "10/30/06 12:00 AM" since the date is empty,
the formatting assumes it's 0 Hour I guess.
>
>
So in short, the formatting works unless we have no time recorded for
the particular item we are testing.
>
Is there a way to get the format to ignore the hh:nn AM/PM section if
no time exists? Keep in mind these are when generating reports that go
back to the client... so they must be accurate.
>
Thank you, I hope I've made it clear what I'm looking for.
>
-Ryan
>

[Ryan]

I wish I could go back and edit posts..

It seems to work on some of my "DateRecieved" Controlsource fields and
not on others... they all pull from the same table, but sometimes I get
stuff like #error rather than a date...
Ryan wrote:

Kevin,

Ok it worked. Not at first, I had to adjust it to this:

=IIf(InStr(Format([DateCollected],"mm/dd/yy hh:nn AM/PM"),"12:00
AM")>0,Format([DateCollected],"mm/dd/yy"),Format([DateCollected],"mm/dd/yy
hh:nn AM/PM"))

So I just reversed the second and third Format statements....

Thanks!
Ryan wrote:

Kevin,
Yes I apologize, I meant time. There's always a date entered, but
sometimes not a time.

I'll give that a whack here.. I appreciate it.
Kc-Mass wrote:

Ryan
Try this. At one point you say the date is empty But I think you meant there
is no time, yes?
>
= IIF( Instr(Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),"12:00 AM") 0,
Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),
Format(DateCollected, "mm/dd/yy "))
>
Kevin C
>
>
>
"Ryan" <mi********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...
Hello,
I'm new to Access and DB's in general. I've taken over some light
duty support for a lab information system we use in house. Most of our
dates are reported as "10/31/2006 12:30:00 PM" (With seconds listed in
the report field).

However, nobody wants to see the seconds. So I tried to format it
=Format(DateCollected, "mm/dd/yy hh:nn AM/PM").

Works great, right? Well.. yeah for the most part. However if we
have something come in from a client and they list a date with no time,
then the report spits out "10/30/06 12:00 AM" since the date is empty,
the formatting assumes it's 0 Hour I guess.


So in short, the formatting works unless we have no time recorded for
the particular item we are testing.

Is there a way to get the format to ignore the hh:nn AM/PM section if
no time exists? Keep in mind these are when generating reports that go
back to the client... so they must be accurate.

Thank you, I hope I've made it clear what I'm looking for.

-Ryan

[Kc-Mass]

I would guess those records have a null value - no date, no time

"Ryan" <mi********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

>I wish I could go back and edit posts..

It seems to work on some of my "DateRecieved" Controlsource fields and
not on others... they all pull from the same table, but sometimes I get
stuff like #error rather than a date...
Ryan wrote:

>Kevin,

Ok it worked. Not at first, I had to adjust it to this:

=IIf(InStr(Format([DateCollected],"mm/dd/yy hh:nn AM/PM"),"12:00
AM")>0,Format([DateCollected],"mm/dd/yy"),Format([DateCollected],"mm/dd/yy
hh:nn AM/PM"))

So I just reversed the second and third Format statements....

Thanks!
Ryan wrote:

Kevin,
Yes I apologize, I meant time. There's always a date entered, but
sometimes not a time.

I'll give that a whack here.. I appreciate it.
Kc-Mass wrote:
Ryan
Try this. At one point you say the date is empty But I think you
meant there
is no time, yes?

= IIF( Instr(Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),"12:00
AM") 0,
Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),
Format(DateCollected, "mm/dd/yy "))

Kevin C

"Ryan" <mi********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...
Hello,
I'm new to Access and DB's in general. I've taken over some
light
duty support for a lab information system we use in house. Most of
our
dates are reported as "10/31/2006 12:30:00 PM" (With seconds listed
in
the report field).

However, nobody wants to see the seconds. So I tried to format it
=Format(DateCollected, "mm/dd/yy hh:nn AM/PM").

Works great, right? Well.. yeah for the most part. However if
we
have something come in from a client and they list a date with no
time,
then the report spits out "10/30/06 12:00 AM" since the date is
empty,
the formatting assumes it's 0 Hour I guess.
So in short, the formatting works unless we have no time recorded
for
the particular item we are testing.

Is there a way to get the format to ignore the hh:nn AM/PM section
if
no time exists? Keep in mind these are when generating reports
that go
back to the client... so they must be accurate.

Thank you, I hope I've made it clear what I'm looking for.

-Ryan

[Lyle Fairfield]

Ryan wrote:

Hello,
I'm new to Access and DB's in general. I've taken over some light
duty support for a lab information system we use in house. Most of our
dates are reported as "10/31/2006 12:30:00 PM" (With seconds listed in
the report field).

However, nobody wants to see the seconds.

Being a compulsive coder I would

1. create a function in the report's module as:

Private Function MySpecialDateFormat(ByVal d As Date) As String
MySpecialDateFormat = Format(d, "mm\/dd\/yyyy")
If Hour(d) 0 Or Minute(d) 0 Then _
MySpecialDateFormat = MySpecialDateFormat & _
Format(d, "\ hh:nn AM/PM")
End Function

(those things that look like \/ are one of these \ and one of these /
with no space between)

' gives
11/01/2006 for VBA.Date()
11/01/2006 07:43 AM for VBA.Now()

2. Set the control source to:
=MySpecialDateFormat([DateFieldName])

3. Access has this neat little trick. When a wizard creates a report it
names the control with the same name as the field. Then if we decide to
modify control source (as above) to use a function that refers to the
field's value it gives an error because, so it is said, in examining
the form it finds the control's name before it finds the field's name
and so think it has a circular reference. (This is really UGLY! [the
naming of the control with the same name as the field] and something MS
should have fixed years and years ago.) But there is a simple solution.
Be sure the field and the control do not have the same name.

Of course, you could make the function Public although there's not too
much point to that if you put it in the Report Module. If you make it
Public and put it into a Standard module then other objects and
procedures could use it.

[Ryan]

Kevin,
No they most certainly do have dates. We generate several reports upon
recieving items and then again a different set of reports to give to
the client that tell them what we found with the items in question..

The DateRecieved field is the same for item "X" all the way through
the reporting process...

Not sure why the formatting works one place and tosses an #ERROR in
another.

Kc-Mass wrote:

I would guess those records have a null value - no date, no time

"Ryan" <mi********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

I wish I could go back and edit posts..

It seems to work on some of my "DateRecieved" Controlsource fields and
not on others... they all pull from the same table, but sometimes I get
stuff like #error rather than a date...
Ryan wrote:

Kevin,

Ok it worked. Not at first, I had to adjust it to this:

=IIf(InStr(Format([DateCollected],"mm/dd/yy hh:nn AM/PM"),"12:00
AM")>0,Format([DateCollected],"mm/dd/yy"),Format([DateCollected],"mm/dd/yy
hh:nn AM/PM"))

So I just reversed the second and third Format statements....

Thanks!
Ryan wrote:
Kevin,
Yes I apologize, I meant time. There's always a date entered, but
sometimes not a time.

I'll give that a whack here.. I appreciate it.
Kc-Mass wrote:
Ryan
Try this. At one point you say the date is empty But I think you
meant there
is no time, yes?
>
= IIF( Instr(Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),"12:00
AM") 0,
Format(DateCollected, "mm/dd/yy hh:nn AM/PM"),
Format(DateCollected, "mm/dd/yy "))
>
Kevin C
>
>
>
"Ryan" <mi********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...
Hello,
I'm new to Access and DB's in general. I've taken over some
light
duty support for a lab information system we use in house. Most of
our
dates are reported as "10/31/2006 12:30:00 PM" (With seconds listed
in
the report field).

However, nobody wants to see the seconds. So I tried to format it
=Format(DateCollected, "mm/dd/yy hh:nn AM/PM").

Works great, right? Well.. yeah for the most part. However if
we
have something come in from a client and they list a date with no
time,
then the report spits out "10/30/06 12:00 AM" since the date is
empty,
the formatting assumes it's 0 Hour I guess.


So in short, the formatting works unless we have no time recorded
for
the particular item we are testing.

Is there a way to get the format to ignore the hh:nn AM/PM section
if
no time exists? Keep in mind these are when generating reports
that go
back to the client... so they must be accurate.

Thank you, I hope I've made it clear what I'm looking for.

-Ryan

[Ryan]

This point hit the nail on the head on my #ERROR rows. I just changed
the name field while leaving the control source the same....

I made the function public in a module called "DateFormat" since we
have several reports so making it a private function in each of them
didn't seem the best way to go about it. This just seemed more
'global', maybe I misunderstood what you meant about putting it in the
report module, though.

Thanks for the assitance.

3. Access has this neat little trick. When a wizard creates a report it
names the control with the same name as the field. Then if we decide to
modify control source (as above) to use a function that refers to the
field's value it gives an error because, so it is said, in examining
the form it finds the control's name before it finds the field's name
and so think it has a circular reference. (This is really UGLY! [the
naming of the control with the same name as the field] and something MS
should have fixed years and years ago.) But there is a simple solution.
Be sure the field and the control do not have the same name.

Of course, you could make the function Public although there's not too
much point to that if you put it in the Report Module. If you make it
Public and put it into a Standard module then other objects and
procedures could use it.