How Convert Date/Time Field

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

Guest

Hi,

I have two fields that are Date/time but not all the fields are filled in,
sometimes a date/time will be populated & sometimes a Blank.

So I tried to use DateValue([CLOSE_TIME]) but the blank cells are populated
with error.

Anyway I can fix this? Thanks...
 
Heather said:
Hi,

I have two fields that are Date/time but not all the fields are filled in,
sometimes a date/time will be populated & sometimes a Blank.
Click to expand...

Based on the function you're using, my guess is that the data type of
the fields is Text, not Date/time, and that you want to convert it to
Date/time.
So I tried to use DateValue([CLOSE_TIME]) but the blank cells are populated
with error.

Anyway I can fix this? Thanks...
Click to expand...

Suppose your data look like this...

[Times] Table Datasheet View:

OPEN_TIME CLOSE_TIME
--------- ----------
8:57 AM 11:30 AM
9:03 AM
9:05 am 9:30 am
9:45 am

Then the following Query might do something like what you want. I don't
know what value you want for the blank/empty fields; you'll need to
choose whatever makes sense to you.

I changed DateValue() to TimeValue() because you called your field
[CLOSE_TIME], but perhaps DateValue() is what you really want.

Since a blank character or a string of blank spaces is not the same as a
Null value, I used Trim() to get rid of blanks.

[Q_DateValues] SQL:
SELECT TimeValue(IIf(IsNull(
Trim([Times]![CLOSE_TIME])),
#12/30/1899 17:0:0#,
TimeValue([Times]![CLOSE_TIME]))) AS [Close]
FROM Times;

The result for my example looks like this:

[Q_DateValues] Query Datasheet View:
Close
-----------
11:30:00 AM
5:00:00 PM
9:30:00 AM
5:00:00 PM


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Try the following formula.

IIF ([Close_Time] is null, Null,DateValue([Close_Time))

If you want some specific value when Close_Time is blank (null) then you can
replace the Null in the above with that specific value.
 
Back
Top