Page 1 of 4 1234 LastLast
Results 1 to 15 of 46
  1. #1
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80

    Date between in recordset maybe

    I have to write a piece of code on a combobox onclick event. I know what it needs to do but I just need some advice about the best and most efficient way to do it.


    What needs to happen is
    If date of delegate already scheduled is 2 weeks before or after the date of the combobox then messagebox pops up.

    I thought the best way would be to call a recordset that would bring up the eventstartdate of the event selected and the delegate but I don't know how to calculate 2 weeks before and after this date. Can anyone shed any light or show me where to look. All the examples i've seen can show before or after a specific date but this date is variable and I'm a bit lost to tell the truth.

  2. #2
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    I am trying a different method but I need some advice on how to calculate 14 days before and after a date in a dlookup statement

  3. #3
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    This is what I have so far:
    If Dlookup("[EventStartDate]","tblEventDelegate",EventID = " & Me.cmboEvents & " AND EventStartDate = " & Format(Me.txtEventEndDayDate, "\#mm\/dd\/yyyy\#") +14 then
    But it is giving me an expected:expression error on the highlighted # I don't know whether the statement is right and I'm not sure how to add 14 to the date so i've just tried this

  4. #4
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    My date in the tbl is dd/mm/yyyy so do I need to change it to this in the dlookup or is it dependent on windows settings

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,939

  6. #6
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Thanks i've read the query one before but neither give me what I really want. I have been working on this problem further and have changed my plan completely. I know have the following:
    Code:
     
    If Dlookup("[EventStartDate]","tblEventDelegate","DelegateID = " & Me.DelegateID AND EventStartDate = " & DateAdd("d",14,Me.cmboEvents.Column(1))<) then
    But gives me an expected:separator or ) on the "d" but I think the logic is improved. I'm not sure where the < operator should go as I've been told it should be outside the DateAdd function. Also I don't know if the DateAdd function can do what I need it to do which is work out dates up to 14 days before and after the date in the combobox (cmboEvents).

  7. #7
    BobC is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2011
    Posts
    4
    Hi Andy
    What you seem to need is the datediff function and the abs function. The datediff function calculates the difference between two dates in days , months weeks etc. and the Abs function returns the absolute number.

    Your code on the on click event would look like this, although you may need to change this to afterupdate if the combo has just been updated.

    if abs(datediff("d",date1, date2))>=14 then
    msgbox "whatever"
    end if

    The datediff will give you the number days betweem date1 and date2, these are your control names, the abs gets rid of the - sign if date2 is less then date1.

    Hope this helps Bobc

  8. #8
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Bob

    Thank you so much that works exactly how I want.

  9. #9
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    But no I just tested it and it seems to come up for every entry I have:
    Code:
     
    If DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID & " And EventStartDate = #" & Abs(DateAdd("d", -14, Me.cmboEvents.Column(1))) >= 14 & "#") Then
     
    MsgBox " The delegate is scheduled already onto an event 2 weeks after this event please reschedule"
    Exit Sub
    End If
    I've changed it now to:
    Code:
     
    'If Abs(DateAdd("d","[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID & ",Me.cmboEvents.Column(1))) >= 14 Then
    But this gives me an epxected separator or ) on the end of the line

  10. #10
    BobC is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2011
    Posts
    4
    Why are you using a dlookup? I assumed both the combo and event start date are on the same form.
    If event start date is in another table then use the dlookup to get the event start date then use the small piece of code to determine it the event start date falls + or - 14 days from the date in the combo



    dim estartdate as date
    ' get and store the event date
    estartdate = DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID & ")

    'get then number days between estartdate and the combo if greater or equal to 14 display message. Note the "d" tells the function to return the value in days. You need the abs so it works both ways, if there are 7 or -7 days between dates you get 7 and this can be compared to 14

    if abs(datediff("d",estartdate, Me.cmboEvents.Column(0)))>=14 then
    msgbox "whatever"
    end if

    I changed to column(0) because column count starts at zero you are getting the value of the second column. You must use column(0) unless you have mutli columns in combo then use the column -1 to get the correct data.

    Hope this sorts your problem BobC

  11. #11
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Thanks Bob I've been away from here sorting out some other more pressing issues with the db. I tried the code you suggest and I can see exactly what you mean however the line estartdate= gives me an expected list separator or ) error. I have tried bracketing the whole dlookup but that doesn't work and gives same error. My code is now:

    Code:
     
    Dim estartdate As Date
    estartdate = DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID & ")
    If Abs(DateDiff("d", estartdate, Me.cmboEvents.Column(1))) >= 28 Then
    MsgBox "You have scheduled this delegate already etc etc"
    End If
    The column is right (1) as the event date is the second column in the cmbobox the first being the eventid I have put 28 because I want 2 weeks either side of the event.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,939
    It appears to me that,
    if DelegateId is numeric, you don't need the rightmost & "

    and if DelegateId is character, you would need
    "DelegateID = '" & Me.DelegateID & "'"

    Just my $.02....

  13. #13
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Thanks orange yes the code now doesn't give me the error but when I run through it just runs through it without doing anything. I put dummy data in to create the state but allows me to carry on adding the details which is done with an update query at the end of the code.

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,939
    You're not getting a message?
    You could put an ELSE in with a message to make sure the code is actually being processed, if that's a concern.

  15. #15
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    I did as you suggested and it just opened the message I put in the else so the code runs through OK

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Recordset not updatable...WHY?
    By jgelpi16 in forum Queries
    Replies: 3
    Last Post: 11-30-2010, 08:40 AM
  2. Lookup using second recordset
    By vavs in forum Programming
    Replies: 7
    Last Post: 08-16-2010, 12:42 PM
  3. Recordset Search
    By ColPat in forum Programming
    Replies: 2
    Last Post: 08-14-2010, 08:25 PM
  4. Replies: 1
    Last Post: 11-13-2009, 03:03 AM
  5. This Recordset Is Not Updateable
    By botts121 in forum Access
    Replies: 1
    Last Post: 11-11-2009, 05:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums