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

Posted on 2008-11-03
Last Modified: 2011-10-19
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()

lngDelta = fnTestThis(dat_4Plan, dat_5Actual)

Question by:Jon_Wales_UK
    1 Comment
    LVL 38

    Accepted Solution

    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())
    ?fnTestThis(date()-81, date())

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now