Time format conversion

  • Thread starter Thread starter Jacqueline
  • Start date Start date
J

Jacqueline

I am trying to convert a time field in my query from text i.e. 0830A to a
time signature of 8:30 AM. However, some fields are blank and should remain
blank. I have tried several different nested IIf but get #Error in all the
blank fields. The fields with a text time convert just fine.

Here is the formula I am using, can anyone help.... my hair is short and the
bald spots from pulling it out are not attractive.... :(

AltEtime: CDate(Format(IIf([End_TIME]=Null,Null,[End_TIME]),"@@\:@@@"))

Thanks Much
Jacqueline
 
Try this --
AltEtime: IIf([End_TIME]=Null,Null,CVDate(Format([End_TIME],"@@\:@@ @\M")))
 
Nope, no luck. I think the issue is with the Else statment, it is trying to
format a Null field as a time... any suggestions?

KARL DEWEY said:
Try this --
AltEtime: IIf([End_TIME]=Null,Null,CVDate(Format([End_TIME],"@@\:@@ @\M")))

Jacqueline said:
I am trying to convert a time field in my query from text i.e. 0830A to a
time signature of 8:30 AM. However, some fields are blank and should remain
blank. I have tried several different nested IIf but get #Error in all the
blank fields. The fields with a text time convert just fine.

Here is the formula I am using, can anyone help.... my hair is short and the
bald spots from pulling it out are not attractive.... :(

AltEtime: CDate(Format(IIf([End_TIME]=Null,Null,[End_TIME]),"@@\:@@@"))

Thanks Much
Jacqueline
Click to expand...
Click to expand...
 
Try something like this:

AltEtime: IIF(isnull(End_Time),Null,CDate(Format([End_TIME]),"@@\:@@@"))

You want to avoid trying to convert and or format Nulls.

Regards

Kevin
 
I am trying to convert a time field in my query from text i.e. 0830A to a
time signature of 8:30 AM. However, some fields are blank and should remain
blank. I have tried several different nested IIf but get #Error in all the
blank fields. The fields with a text time convert just fine.

Here is the formula I am using, can anyone help.... my hair is short and the
bald spots from pulling it out are not attractive.... :(

AltEtime: CDate(Format(IIf([End_TIME]=Null,Null,[End_TIME]),"@@\:@@@"))

Thanks Much
Jacqueline
Click to expand...

NULL is a funny beast: it means "undefined, unknown, unspecified". As such
nothing is ever equal (or for that matter unequal) to Null.

Instead of = Null, use the IsNull function. Also turn your expression inside
out - CDate will gag on nulls:

IIF(IsNull([End_TIME]), Null, CDate(Format([End_TIME], "@@:\@@@"))
 
I should say that I don't understand your formating of the date.

Regards

Kevin

KC-Mass said:
Try something like this:

AltEtime: IIF(isnull(End_Time),Null,CDate(Format([End_TIME]),"@@\:@@@"))

You want to avoid trying to convert and or format Nulls.

Regards

Kevin


Jacqueline said:
I am trying to convert a time field in my query from text i.e. 0830A to a
time signature of 8:30 AM. However, some fields are blank and should
remain
blank. I have tried several different nested IIf but get #Error in all
the
blank fields. The fields with a text time convert just fine.

Here is the formula I am using, can anyone help.... my hair is short and
the
bald spots from pulling it out are not attractive.... :(

AltEtime: IIF(isnull(End_Time),Null,CDate(Format([End_TIME]),"@@\:@@@"))

Thanks Much
Click to expand...
Click to expand...
 
No, still no luck. Here is the code I use for the main classroom time field.
I do not have to deal with Null values in this field and it works fine. We
just started pulling the alternate screen data into reports and silly me I
thought I could fix the time field there too.... :(

Etime: CDate(Format(IIf([EndTime]="ARR","0001A",[EndTime]),"@@\:@@@"))

KC-Mass said:
Try something like this:

AltEtime: IIF(isnull(End_Time),Null,CDate(Format([End_TIME]),"@@\:@@@"))

You want to avoid trying to convert and or format Nulls.

Regards

Kevin


Jacqueline said:
I am trying to convert a time field in my query from text i.e. 0830A to a
time signature of 8:30 AM. However, some fields are blank and should
remain
blank. I have tried several different nested IIf but get #Error in all the
blank fields. The fields with a text time convert just fine.

Here is the formula I am using, can anyone help.... my hair is short and
the
bald spots from pulling it out are not attractive.... :(

AltEtime: IIF(isnull(End_Time),Null,CDate(Format([End_TIME]),"@@\:@@@"))

Thanks Much
Click to expand...
Click to expand...
 
Hi John, I was hoping you were out there somewhere. I think you helped me
devlop the first query to to convert the text times to actual time signature.
The code you gave me worked on the Nulls, but now the fields that had text in
them are getting the #Error .... (sigh) I am playing around with the order
but anythig you want to add will be greatly appreciated.
thanks
Jacqueline

John W. Vinson said:
I am trying to convert a time field in my query from text i.e. 0830A to a
time signature of 8:30 AM. However, some fields are blank and should remain
blank. I have tried several different nested IIf but get #Error in all the
blank fields. The fields with a text time convert just fine.

Here is the formula I am using, can anyone help.... my hair is short and the
bald spots from pulling it out are not attractive.... :(

AltEtime: CDate(Format(IIf([End_TIME]=Null,Null,[End_TIME]),"@@\:@@@"))

Thanks Much
Jacqueline
Click to expand...

NULL is a funny beast: it means "undefined, unknown, unspecified". As such
nothing is ever equal (or for that matter unequal) to Null.

Instead of = Null, use the IsNull function. Also turn your expression inside
out - CDate will gag on nulls:

IIF(IsNull([End_TIME]), Null, CDate(Format([End_TIME], "@@:\@@@"))
Click to expand...
 
John,
It works!!!! :) the \ was in the wrong place and causeing the error. Once I
fixed that the code works great! Corrected code:

IIf(IsNull([End_TIME]),Null,CDate(Format([End_TIME],"@@\:@@@")))

Thanks so much!
Jacqueline


John W. Vinson said:
I am trying to convert a time field in my query from text i.e. 0830A to a
time signature of 8:30 AM. However, some fields are blank and should remain
blank. I have tried several different nested IIf but get #Error in all the
blank fields. The fields with a text time convert just fine.

Here is the formula I am using, can anyone help.... my hair is short and the
bald spots from pulling it out are not attractive.... :(

AltEtime: CDate(Format(IIf([End_TIME]=Null,Null,[End_TIME]),"@@\:@@@"))

Thanks Much
Jacqueline
Click to expand...

NULL is a funny beast: it means "undefined, unknown, unspecified". As such
nothing is ever equal (or for that matter unequal) to Null.

Instead of = Null, use the IsNull function. Also turn your expression inside
out - CDate will gag on nulls:

IIF(IsNull([End_TIME]), Null, CDate(Format([End_TIME], "@@:\@@@"))
Click to expand...
 
Back
Top