Time format conversion

[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

 

[KARL DEWEY]

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

 

[Jacqueline]

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?

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

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

 

[KC-Mass]

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

 

[John W. Vinson]

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

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], "@@:\@@@"))

 

[KC-Mass]

I should say that I don't understand your formating of the date.

Regards

Kevin

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: IIF(isnull(End_Time),Null,CDate(Format([End_TIME]),"@@\@@"))

Thanks Much

 

[Jacqueline]

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]),"@@\@@"))

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: IIF(isnull(End_Time),Null,CDate(Format([End_TIME]),"@@\@@"))

Thanks Much

 

[Jacqueline]

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

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

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], "@@:\@@@"))

 

[Jacqueline]

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

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

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], "@@:\@@@"))