Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 46

Date between in recordset maybe

  1. #16
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Ah I've got it working I think but if I try a delegate without a previous event scheduled it gives an invalid use of null error message do i need NZ around the Dlookup if there is no record already in the eventdelegate table. I actually changed the code to:
    Code:
     
    Dim estartdate As Date
    estartdate = DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID)
    If Abs(DateDiff("d", estartdate, Me.cmboEvents.Column(1))) <= 14 Then
    MsgBox "You have scheduled this delegate onto an event within 2 weeks"
    Exit Sub
    Else
    If Abs(DateDiff("d", estartdate, Me.cmboEvents.Column(1))) >= 14 Then
    MsgBox "You have scheduled this delegate onto an event within 2 weeks"
    Exit Sub
    End If
    End If
    End Sub
    which made it work with my data though it could be because I have only a few records in place yet and therefore it is hard to test fully

  2. #17
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    I've changed it further by putting NZ into the the dlookup but now it runs through the first bit OK but after the else it brings up the 2nd message where there isn't anything in the table at all. My code now:
    Code:
     
    Dim estartdate As Date
    estartdate = Nz(DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID), 0)
    If Abs(DateDiff("d", estartdate, Me.cmboEvents.Column(1))) <= 14 Then
    MsgBox "You have scheduled this delegate onto an event within 2 weeks before"
    Exit Sub
    Else
    If Abs(DateDiff("d", estartdate, Me.cmboEvents.Column(1))) >= 14 Then
    MsgBox "You have scheduled this delegate onto an event within 2 weeks after this event"
    Exit Sub
    End If
    End If
    End Sub

  3. #18
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    I ran the dlookup through the immediate window and as expected the result was null as there are no records for this event and delegate but I'm baffled as to why it runs through the first part Ok and not the second part.

  4. #19
    BobC is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2011
    Posts
    4
    Andy >= 28 will only display message when estartdate is >= 28 days before or after the event change it back to 14 and do as Orange said. You could also try adding a temp msgbox before the if statement to display estartdate to check the dlookup is working. Remember diffdate function will give you a postive number in one direction from the event date and a negative number in the other direction. Using the Abs gives a postive number of days in both direction so you are checking forwards and backwards at the same time. You could use 2 datediff functions and one for +numbers and one for -numbers but that gets messy.

  5. #20
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    I'm assuming that you can only have one of each DELEGATEID on your TBLEVENTDELEGATE. If not you are not necessarily going to get the same result every time you perform this dlookup. If you do indeed have only one DELEGATEID on this table you have to alter your dlookup function.

    Code:
    estartdate = DLookup("[EventStartDate]", "tblEventDelegate", """ & [DelegateID] = " & Me.DelegateID & """")
    You basically have to encapsulate the " marks in your dlookup function for it to correctly build the statement plus you have to have opening and closing " marks to capture me.delegateID. So in this case you want an opening " mark in your criteria (""") then a denotation that you're going to add form field name (" before the first &), then at the end you want to close your form field name (" after the second &) and then insert a closing " mark (""") before the closing paren.

    After that if you change your ABS function to be 14 days instead of 28 you should be ok.

    EDIT: This is the code I used and it works as expected.

    Code:
    Dim estartdate As Date
    
    estartdate = DLookup("[EventStartDate]", "tblEventDelegate", """ & [DelegateID] = " & Me.DelegateID & """")
    
    If Abs(DateDiff("d", estartdate, Me.cmboEvents.Column(1))) <= 14 Then
        MsgBox "You have scheduled this delegate onto an event within 2 weeks"
    Else
        MsgBox "Event greater than two weeks away"
    End If
    
    End Sub

  6. #21
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Thanks rpeare I have been away for a while so have only just got back to this problem. I tried the code as you suggest and at first I thought it worked correctly as I tested it on a couple of records I and it gave the expected message however I tried it on a delegateID with no record in the eventdelegate table and it gave me the second message. I can see why because it didn't find the first record and therefore ran the else part of the code instead as it hadn't found a record so I took out the else part as that is superfluous anyway as I don't need a msg if there is no record. It seems to work correctly but there are more than one instance of each delegateid in the eventdelegate table as it's a table constantly being appended to from a query when a delegate is scheduled or their status is changed elsewhere in the db. Will I definitely encounter problems with this because of the repitition of delegateIDs or is this a maybe do you think.

  7. #22
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    So it's possible for delegates to attend more than one event in this table. If that's the case you have to find a way to isolate them, for instance your dlookup would have to include a criteria for BOTH a DELEGATEID and an EVENTID. I would assume that other than your primary key this would probably work.

    To program for an event date not being populated after you perform the dlookup you could have something like

    if isnull(estartdate) then
    Msgbox "error message for no estartdate"
    else
    Do what you'd normally do
    endif

  8. #23
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Thanks I see I'm having issues with the code as I test it. I get the 1st message if someone has not been scheduled onto an event already so something's not right at all.

  9. #24
    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,306
    In addition to rpeare's comment,I'm not sure of your design. You may do this already, but you have to ensure that any DelegateId that has a record in the
    tblEventDelegate, must already exist in the tblDelegate.

    That is, you must have an identified Delegate, before that Delegate can attend 1 or more events. That would indicate the possibility of a Delegate being identified that, for some reason, has not/did not attend any events.

  10. #25
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Every delegate has an id when they are entered into the db and that same id is entered into the eventdelegate table when they are scheduled (the eventdelegate table is a joining table between tblevent and tbldelegate)

  11. #26
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    It doesn't come up with the message everytime i've discovered though.

  12. #27
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    In fact it hasn't happened for a while i've been testing in the last few minutes

  13. #28
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Ah i think I've narrowed it down that it comes up when the eventid is equal to an event in the eventdelegate table so it's only comparing the dates and not the delegate as well.

  14. #29
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    I don't think you're understanding orange's point.

    Let's say you have a delegates table

    Code:
    tblDelegates
    DelegateID  DelegateName
    1           AAAA
    2           BBBB
    an Event table

    Code:
    tblEvents
    EventID  EventName
    1        YYYY
    2        ZZZZ
    Now let's say this is the contents of your tblEventDelegate
    Code:
    tblEventDelegate
    ID  EventID  DelegateID
    1   1        1
    2   1        2
    3   2        1
    There are things you have to check for (which may be causing you problems if you blindly perform a dlookup)

    What orange is saying is that you have to program for the possibility that there will be no record for a lookup of a delegateID/eventID pair. In this example if you were to lookup information for event 2 delegate 2 your dlookup would return a null value and if you tried to perform any functions on that returned value it would either bomb your code out or continue to give you a null value (depending on what you're doing with the returned value).

    Thanks I see I'm having issues with the code as I test it. I get the 1st message if someone has not been scheduled onto an event already so something's not right at all.
    This is exactly the result I would expect if you changed your code to look up by eventid/delegateID. You would want to know if the person you're examining is even signed up for the event unless I'm missing the point you're trying to make.

  15. #30
    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,306
    Perhaps you could show us the table structures including the PK/FK.

    You shouldn't get a NULL in the sample rpeare gave when looking for Delegate 2 in Event 2. There should not be a record - nothing would be NULL. My original point was -you could not have a record with say Delegate 5 Event 6 in you tblEventDelegate if you did not already have a Delegate 5 (in tblDelegate) and an Event 6 (in tblEvent).

    In my view there should be a compound unique index on EventId + DelegateId in tblEventDelegate.

Page 2 of 4 FirstFirst 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
  •  
Tech Forums: Microsoft Office Forums