Build an Access expression that will do the same as the following Excel formula?

[JonWales via AccessMonster.com]

Question: In Access 2003/XP/2000/97, I'm trying to build an expression that
will do the same as the following Excel formula with the result formatted as

=IF(TYPE(CODE(B11))=1,IF(F11=0,(E11-$B$6)/7,(E11-F11)/7)," ")

I've tried this but it's not working??

IIF(ISNULL([1_ID_REFERENCE),NULL,IIF([5_ACTUAL]=0,([4_PLAN]-Date())/7,(
[4_PLAN]-[5_ACTUAL])/7))

The Access split form field names are selection type text boxes
1_ID_Reference is a 'Text' datatype
5_ACTUAL is a 'date/time' datatype
4_PLAN is a 'Date/Time' datatype
How can I do this?

 

[Beetle]

I assume you are trying to count the number of days between two
dates and divide the result by 7, in which case you need something
along the lines of;

IIf(IsNull([1_ID_REFERENCE),Null,IIf([5_ACTUAL]=0,DateDiff("d", [4_Plan],
Date)/7,DateDiff("d", [4_PLAN], [5_ACTUAL])/7)))

You may need to switch the order of the fileds in the DateDiff functions
depending on which way you want it to calculate.

 

[tkelley]

Does this have to be an IIF expression? Can it be VBA code that is triggered
by an event?

If not, as typed, it looks like you're missing a closing bracket on "ISNULL(
[1_ID_REFERENCE)"

 

[JonWales via AccessMonster.com]

Thanks for the help...
It seems to still throw up errors.. function containing wrong number of
arguments...or can't find object ..maybe VBA might do it? or am i doing
something wrong?

I assume you are trying to count the number of days between two
dates and divide the result by 7, in which case you need something
along the lines of;

IIf(IsNull([1_ID_REFERENCE),Null,IIf([5_ACTUAL]=0,DateDiff("d", [4_Plan],
Date)/7,DateDiff("d", [4_PLAN], [5_ACTUAL])/7)))

You may need to switch the order of the fileds in the DateDiff functions
depending on which way you want it to calculate......

 

[JonWales via AccessMonster.com]

Thanks for the help but it hasn't worked.... still getting function
containing wrong number of arguments or "table can't find the object ......is
a new macro or macro group, make sure you have saved it and you have typed
its name correctly"

I assume you are trying to count the number of days between two
dates and divide the result by 7, in which case you need something
along the lines of;

IIf(IsNull([1_ID_REFERENCE),Null,IIf([5_ACTUAL]=0,DateDiff("d", [4_Plan],
Date)/7,DateDiff("d", [4_PLAN], [5_ACTUAL])/7)))

You may need to switch the order of the fileds in the DateDiff functions
depending on which way you want it to calculate.

Question: In Access 2003/XP/2000/97, I'm trying to build an expression that
will do the same as the following Excel formula with the result formatted as

[quoted text clipped - 11 lines]

4_PLAN is a 'Date/Time' datatype
How can I do this?

 

[JonWales via AccessMonster.com]

Thanks for the reply..

No, it does not need to be an expression .. I understand that Access cannot
ADD amd SUBTRACT directly (+/-) ...DateDiff , DateAdd VBA code that
triggered by an event i would certainly try it

Does this have to be an IIF expression? Can it be VBA code that is triggered
by an event?

If not, as typed, it looks like you're missing a closing bracket on "ISNULL(
[1_ID_REFERENCE)"

Question: In Access 2003/XP/2000/97, I'm trying to build an expression that
will do the same as the following Excel formula with the result formatted as

[quoted text clipped - 11 lines]

4_PLAN is a 'Date/Time' datatype
How can I do this?

 

[tkelley via AccessMonster.com]

So I assume the missing bracket was just a typo, and your original code has
it in there?

Thanks for the reply..

No, it does not need to be an expression .. I understand that Access cannot
ADD amd SUBTRACT directly (+/-) ...DateDiff , DateAdd VBA code that
triggered by an event i would certainly try it

Does this have to be an IIF expression? Can it be VBA code that is triggered
by an event?

[quoted text clipped - 7 lines]

 

[JonWales via AccessMonster.com]

I asume the missing bracket should be follows

IIF(ISNULL([1_ID_Reference]),NULL,IIF([5_ACTUAL]=0,([4_PLAN]-Date())/7,(
[4_PLAN]-[5_ACTUAL])/7))

So I assume the missing bracket was just a typo, and your original code has
it in there?

Thanks for the reply..

[quoted text clipped - 7 lines]

 

[tkelley via AccessMonster.com]

Yeah, I posted that above ... maybe you didn't see it. But that is indeed
where a bracket was missing.

I asume the missing bracket should be follows

IIF(ISNULL([1_ID_Reference]),NULL,IIF([5_ACTUAL]=0,([4_PLAN]-Date())/7,(
[4_PLAN]-[5_ACTUAL])/7))

So I assume the missing bracket was just a typo, and your original code has
it in there?

[quoted text clipped - 4 lines]

 

[JonWales via AccessMonster.com]

Yep, tried expression below... this but still not working... what would you
suggest for a VBA / event solution?


IIf(IsNull([1_ID_REFERENCE]),Null,IIf([5_ACTUAL]=0,DateDiff("d", [4_Plan],
Date)/7,DateDiff("d", [4_PLAN], [5_ACTUAL])/7)))

Yeah, I posted that above ... maybe you didn't see it. But that is indeed
where a bracket was missing.

I asume the missing bracket should be follows

[quoted text clipped - 6 lines]

 

[tkelley via AccessMonster.com]

I'd build this function:

Public Function fnTestThis(dat4Plan As Date, dat5Actual As Date) As Long

fnTestThis = CLng(DateDiff("d", dat4Plan, dat5Actual))

End Function

------------------------------------------
Then I'd write this to determine what to pass to that function to execute on
whatever event you need it to:
------------------------------------------
dim dat_4Plan as date
dim dat_5Actual as date
dim lngDelta as long 'datediff number that will be retd from function

dat_4Plan = me.4_Plan

if me.5_Actual=0 then
dat_5Actual = date()
else
dat5_Actual=me.5_Actual
endif

lngDelta = fnTestThis(dat_4Plan, dat_5Actual)
------------------------------------------
Play around with that and let me know ... I'll leave data validation and
error handling up to you.

Yep, tried expression below... this but still not working... what would you
suggest for a VBA / event solution?

IIf(IsNull([1_ID_REFERENCE]),Null,IIf([5_ACTUAL]=0,DateDiff("d", [4_Plan],
Date)/7,DateDiff("d", [4_PLAN], [5_ACTUAL])/7)))

Yeah, I posted that above ... maybe you didn't see it. But that is indeed
where a bracket was missing.

[quoted text clipped - 4 lines]