VBA Code to trigger event - Convert excel formula to expression - to VBA

Original issue was: 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 expression but it's not working??

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;

I understand that Access cannot
ADD amd SUBTRACT directly (+/-) ...DateDiff , DateAdd

VBA code tha triggered by an event -

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

See the attached screen shots for view of access form and origianl excel spreadsheet

I need help with the following VBA Function

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

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

End Function

------------------------------------------
I need help to write this to determine what to pass to that function to execute on whatever event you need it to gve result as original excel formula:------------------------------------------
I need help to sort out the data validation and error handling.

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)
------------------------------------------
 

Screenshot-14.jpg
Screenshot-11.jpg
Screenshot-7.jpg
Screenshot-8.jpg
Screenshot-9.jpg
Screenshot-11.jpg
Jon_Wales_UKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.Commented:
So basically you are trying to find the number of weeks between two dates?

>> I understand that Access cannot ADD and SUBTRACT
>> directly (+/-) ...DateDiff , DateAdd

It can but has a tendency to innacuracy depending on the input value. The DateAdd and DateDiff for you to specify the results you are looking at.

?fnTestThis(date()-75, date())
 11
?fnTestThis(date()-81, date())
 12

Converting this code:

=IF(TYPE(CODE(B11))=1,IF(F11=0,(E11-$B$6)/7,(E11-F11)/7)," ")
It would be something like

=Iif(Nz(MyRefField,0)=1, Iif(Nz(MyRefField1,0)=0, CLng(DateDiff("d",PlanDate,ActualDate)/7), CLng(DateDiff("d",PlanDate,FinishDate)/7))
Public Function fnTestThis(dat4Plan As Date, dat5Actual As Date) As Long
 
fnTestThis = CLng(DateDiff("d", dat4Plan, dat5Actual)/7)
 
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.