Results 1 to 4 of 4

Thread: vba issues

  1. #1
    Join Date
    Feb 2005
    Posts
    31

    Unanswered: vba issues

    this is currenly my code

    Private Sub Form_Load()

    'On Load of the switchboard check Jobs table for any uncompleted jobs

    Dim intStore As Integer

    'Count of uncomplete jobs that are past the Expected Completion Date
    intStore = DCount("[id]", "[client]", "[date] <=Now()")

    'If count of uncomplete jobs is zero display switchboard
    'Else display message box detailing amount of jobs
    'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
    Exit Sub
    Else
    If MsgBox("There are " & intStore & " uncompleted jobs" & _
    vbCrLf & vbCrLf & "Would you like to see these now?", _
    vbYesNo, "You Have Uncomplete Jobs...") = vbYes Then
    DoCmd.Minimize
    DoCmd.OpenForm "form2", acNormal
    Else
    Exit Sub
    End If
    End If
    End Sub



    i am trying to get a reminder to come up..after pressing a button, which would come up if the date field in the client table is 2 weeks away. so i want to do for the dcount (bold) [date] + 2 weeks <= NOW()

    anyone know how i can do this, please help!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you diligently read help and notice the in quotes bit and then it bites you in the bum. not fair really

    in an attempt to clarify, lets go the long way round first:
    dim myField as string
    dim myTable as string
    dim myCrits as string
    myField = "[id]"
    myTable = "[client]"
    myCrits = "[date] <= " & Now()
    intStore = Dcount(myfield, myTable, myCrits) 'look ma, no quotes!

    or with less typing
    intStore = Dcount("[id]", "[client]", "[date] <= " Now())

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2005
    Posts
    31
    ok, but i want to specify that the reminder will ONLY come up when the date is two weeks away (in two weeks time, like if todays the 1st december then the reminder for where date=15th december will come up)

    and the code i wrote in my first post worked perfectly, i just want to adapt it to include the above statement.

    thanks

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    well that's twice in two days i've been wrong here mostly because i didn't read the question! sorry!!

    instead of
    <= Now()
    have a go with
    dateadd("d", -14, Now())
    ...and i could be wrong again - don't remember if the 14 comes before or after the Now(). help on dateadd helps on this.

    izy

    ooops
    <= dateadd("d", -14, Now())
    of course
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •