converting text dates to integer with nulls present

[jjz0512]

I have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;

DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2))))


DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2))))
DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4))))
Time: IIf([TIME] Is Not Null,[TIME])

When I have a value of Null, i keep getting #Error, I think when it's null.

Help???

[June7]

CInt() and Val() will error on Null.

The problem with IIf is all parts of the IIf must be able to be evaluated, even if it is not the part that returns answer. So expressions will error if [Date] is Null.

Try the expression without the IIf and without the Is Not Null for the 3 date parts. If you are using CInt to remove leading zeros, that is a complication. Could handle possible Null with Nz and Val() would work. Expression will return 0 if Null is encountered.
DateD: Val(Mid(Nz([Date],0),3,2))
DateM: Val(Left(Nz([Date],0),2))
DateY: Val(Mid(Nz([Date],0),5))

Why bother with an expression for [Time] field?

BTW, Date and Time are reserved words. Advise not to use reserved words as field names.

[jjz0512]

CInt() and Val() will error on Null.

The problem with IIf is all parts of the IIf must be able to be evaluated, even if it is not the part that returns answer. So expressions will error if [Date] is Null.

Try the expression without the IIf and without the Is Not Null for the 3 date parts. If you are using CInt to remove leading zeros, that is a complication. Could handle possible Null with Nz and Val() would work. Expression will return 0 if Null is encountered.
DateD: Val(Mid(Nz([Date],0),3,2))
DateM: Val(Left(Nz([Date],0),2))
DateY: Val(Mid(Nz([Date],0),5))

Why bother with an expression for [Time] field? It's also a text field with the : embedded

BTW, Date and Time are reserved words. Advise not to use reserved words as field names.

Your solution works, thanks, except it still leave a 0 for null, when i need a null value.
I have an inner join involved, referrencing a different column.
So i don't a joined record it's blank, correct. When I do have join on that column and don't have data, i get a 0. Need to leave it blank as it did with no join

So i modified your script like this: StartDateD: IIf([START_DATE] Is Not Null,Val(Mid(Nz([START_DATE],Null),3,2)),Null), no join/no zero, joined blank date/got the zero. any suggestions??

Used an expression is database passed columns as DATE & TIME and to be able to embed column names such as StartDateD and StopDateD needed for a submission.

[June7]

I am a little lost. Don't understand why returning 0 is an issue. Guess I need to work with data.

Also, why have Nz return Null? This should error in the Val() function. The idea of Nz is to not return Null.

[jjz0512]

I am a little lost. Don't understand why returning 0 is an issue. Guess I need to work with data.

Also, why have Nz return Null? This should error in the Val() function. The idea of Nz is to not return Null.

In the source database there's an issue, start & stop date. All of them are either a 10 char date date or a mixture of blanks. any the what me to be able to submit seperate fields for each, day, month, year & time as integer with nulls where there's no data. so here's the goal;

1 - seperate integer fields for all 4
2 - join transfused and transfusion data
3 - where a join, supply an integer field from the text fields where there's data, null where there's not.

Believe me, i share your confusion, but I hope this helps. thanks for helping me with this June7!!!

[Bob Fitz]

Excuse me for butting in, but could it be that when the OP talks about returning a null, he/she actually needs a zero length string? If so, perhaps the following would work:

DateD: Mid(Nz([Date],""),3,2)
DateM: Left(Nz([Date],""),2)
DateY: Mid(Nz([Date],""),5)

[June7]

What are the linking fields for the join?

Which table do the dates need parsing?

Why do 'they' need parsed dates?

Possibly:

Do the query that parses the dates, accept the 0 returns.

Do another query that joins the first query to other data.

Expressions in the joined query to set the 0's as Null

IIf([fieldname]=0, Null,[fieldname])

Or do something like that expression in the initial query:

DateD: IIf(Val(Mid(Nz([Date],0),3,2)) = 0, Null, Val(Mid(Nz([Date],0),3,2)))

Handles the Null to eliminate error in the Val() function so the IIf won't fail but will never return 0.

EDIT: Just saw Bob's post. Maybe empty string will work or maybe creates same issues as 0.

[jjz0512]

Many thanks to you both, June7 & Bob Fitz!!!

Bob, unless i misunderstood, your answer did correct my issue, but when I tried to create an export file for my submission file, the date fields where with "" "", i needed Integers. Only times were to be as a short time field

June7, i was try to do what you sent, beat me to it, which was great!!

Hears what I used for Issue, Start and Stop dates and times;

IssueDateD: IIf(Val(Mid(Nz([Field],0),3,2))=0,Null,Val(Mid(Nz([Field],0),3,2)))
IssueDateM: IIf(Val(Left(Nz([Field],0),2))=0,Null,Val(Left(Nz([Field],0),2)))
IssueDateY: IIf(Val(Mid(Nz([Field],0),5))=0,Null,Val(Mid(Nz([Field],0),5)))
IssueTime: Format([Field],"Short Time")

Thanks again for all your help!!!