Troublesome IIf statemnt

[SusanC]

Can anyone help?

I'm trying to write an IIf statement but am getting a bit confused.

I have several columns in a query. Two columns have a number in them, either 0 or 1.
If these two columns are less than 2, (i.e. 1 and 0, or 0 and 0) then I wanted a calculation.

I.e.

Field = IIf ([Field1] + [Field2] <=2, ([DaysYOB]+[DayDOD])+([TotalYears]*365))
However, this gives me an error message. What have I done wrong?
thanks
Sye

 

[neileg]

Two problems. The first is that you need an else value. Iif works like this
Iif(Condition, Value if true, Value if false)

Secondly, you probably need some brackets on the condition
Iif (([Field1] + [Field2]) <=2, etc

 

[SusanC]

Two problems. The first is that you need an else value. Iif works like this
Iif(Condition, Value if true, Value if false)

Secondly, you probably need some brackets on the condition
Iif (([Field1] + [Field2]) <=2, etc

I thought it would be something simple - I@m a bit new at this you see!!!
thanks
Sue

 

[Mile-O]

Are you sure you want to multiply something by 365 when some years have 366 days?

 

[SusanC]

Are you sure you want to multiply something by 365 when some years have 366 days?

mm, well yes that was my next hurdle really! Leap years? I thought I could just add one using an If statement? WOuld that work?

 

[Mile-O]

Give details on the fields in the expression - it may be easy; it may require some thought.

 

[SusanC]

Give details on the fields in the expression - it may be easy; it may require some thought.

I have two fields with julian dates in

DOB
193.02

DOD
200.03

I have the calculations to find the amount of days inbetween basedon 365 days. However if DOB is 193.02 and DOD is 11.05 I'll need to add 1.

Day diff : ([DaysYOB]+[DayDOD])+([TotalYears]*365))

You're making me wonder if this is the best way to do this? Could I have found the difference between julian dates an easier way?

 

[neileg]

Probably easier to convert the Julian dates to conventional format and use DateDiff to calculate the difference.

Is it possible to hold all your dates as conventional dates, and convert them only when you want to display them, on forms and in reports?

 

[SusanC]

Probably easier to convert the Julian dates to conventional format and use DateDiff to calculate the difference.

Is it possible to hold all your dates as conventional dates, and convert them only when you want to display them, on forms and in reports?

Hi,
I have code to convert the date to conventional date but don't know how to use it with Date diff....
Function CJulian2Date(JulDay As Integer, Optional YYYY) As Date

If IsMissing(YYYY) Then YYYY = Year(Date)

If Not IsNumeric(YYYY) Or YYYY \ 1 <> YYYY Or YYYY < 100 Or YYYY > 9999 Then Exit Function

If JulDay > 0 And JulDay < 366 Or JulDay = 366 And YYYY Mod 4 = 0 And YYYY Mod 100 <> 0 Or YYYY Mod 400 = 0 Then CJulian2Date = DateSerial(YYYY, 1, JulDay)

End Function


Function CDate2Julian(MyDate As Date) As String
CDate2Julian = Format(MyDate - DateSerial(Year(MyDate) - 1, 12, 31), "000")
End Function


How do I write this into a query with Datediff?
thank you
Sue