Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 46

Date between in recordset maybe

  1. #31
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Yes I see the point. The tables are as you say. I need as you said to have 2 dlookups one for the delegate and one for the event and then look for matches of both. so do have something like:
    Code:
     
    estartdate = DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID)
    delid = DLookup ("[DelegateID]","tblEventDelegate", "EventStartDate = "& Me.cmboEvents.Column(1))
    and then something like:
    Code:
     
    If Abs(DateDiff("d", estartdate, delid, Me.cmboEvents.Column(1))) <= 14 Then
        MsgBox "You have scheduled this delegate onto an event within 2 weeks"


  2. #32
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    here is a screenshot of the 3 tables

  3. #33
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    here is a scrshot of the eventdeltable in datasheet view

  4. #34
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Quote Originally Posted by AndycompanyZ View Post
    Yes I see the point. The tables are as you say. I need as you said to have 2 dlookups one for the delegate and one for the event and then look for matches of both. so do have something like:
    Code:
     
    estartdate = DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID)
    delid = DLookup ("[DelegateID]","tblEventDelegate", "EventStartDate = "& Me.cmboEvents.Column(1))
    and then something like:
    Code:
     
    If Abs(DateDiff("d", estartdate, delid, Me.cmboEvents.Column(1))) <= 14 Then
        MsgBox "You have scheduled this delegate onto an event within 2 weeks"
    I don't understand what you're doing here.

    You are looking up a delegateID then trying to use it in a datediff function which will not work. You have a unique identifier on your tblEVENTDELEGATE table you should be using that for your dlookup.

    I see you have a form in the background called frmMainNavigation. Is that form based on the table (or a query based on the table) tblEVENTDELEGATE? if so you you can just do a dlookup based on the primary key item which is what you really should be doing because your results are going to be inconsistent on any other field unless you have a very clearly defined rule about how you can identify individual records based on the contents of the record.

    if the form frmMainNavigation is not based on the table tblEventDelegate what are the contents of that form, what table is it based on or is it unbound, if it is unbound what tables are you using to populate the data?

    You are trying to supply a warning for a specific event by using the dlookup function. dlookup will find ONE item only, if you supply criteria that will find MORE THAN ONE record dlookup will only find the first item on that list regardless of whether it's the correct one or not. Your dlookup function as it stands could find multiple items and the first one it finds is not always going to be the same and not likely to be the one you actually want.

  5. #35
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,287
    Since we all seem somewhat confused with the code, perhaps we could get a restatement of the requirement now that there has been communication.

    What exactly is the requirement?

  6. #36
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    The frmmainnavigation is the navigation page for the whole db, it is in Access2010 so has this option to create a tabbed db for navigation (the tables I showed were just the relevant parts of the db copied into a new db for showing here, the actual db is mush bigger and it wouldn't have been clear). It includes all the forms as subforms.. As for the code I put in I don't know the right way to go about it and that was just pseudocode to see if I had the right idea but obviously not. How can I use the unique identifier if it shows the an instance of 2 seperate ids (delegate and event) as a lookup as the problem i have is not directly concerned with whether 2 ids are the same but a 2 week time period around an instance of them. (Or actually typing that I see that could be the answer though not sure how i can do that)

  7. #37
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    yes sorry orange it is getting wayward.

    I need to find out if when the user selects an event from a combobox relating to a delegate if that delegate is already scheduled onto an event within a 2 week period and if so then bring up a message to the user to alert them of this. I enclose a screenshot of the form.

  8. #38
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Ok, I think I understand.

    You don't care which event a delegate attended, you just want to know if they are scheduled for *any* event within 14 days before of start date of the event, or 14 days after the the close of the event?

  9. #39
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Yes that's right

  10. #40
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Here is an example of what I think you want.

    I would really recommend you try to stay away from domain functions (dsum, dcount, dlookup, etc) especially if your database has the potential to be large. They are not very efficient as the amount of data grows.

    I took a different route but this works.

    Also note that you can do exactly what I did here with static queries, but I really prefer to keep my database windows as clean as possible and only have necessary objects (forms, reports, complex queries, etc)

  11. #41
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Thanks a lot for this rpeare sorry I took so long to get back been away for the weekend camping. I copied and altered for my db the code and it works kind of. The problem I'm having though is that it is coming up with the message about the 2 week time period even if the delegate chosen has not been scheduled onto it. I think what it's doing is looking just for a record in the eventdelegate table that matched the eventid solely and not the delegateid as well, therefore any eventid that is in the table eventdelegates that matches the 2 week criteria is triggering the msg. I need also the delegate in the sql statement as well i think as I can't see that in the statement. I have now:
    Code:
     
    Dim db As Database
    Dim rst As Recordset
    Dim ssql As String
    Dim dStartDate As Date
    Dim dEndDate As Date
    Dim smsg As String
    'first find the start and end points
    If IsNull(EventID) Then
        MsgBox "No event chosen"
    Else
        Set db = CurrentDb
        ssql = "SELECT * FROM tblEvent WHERE ([EventID] = " & Me.cmboEvents & ")"
    'this assumes EventID is a number (autonumber field)
        Set rst = db.OpenRecordset(ssql)
        rst.MoveFirst
    'there should only be one record for any eventID in the tblEvents table
        dStartDate = rst.Fields("EventStartDayDate").Value
        dEndDate = rst.Fields("EventEndDayDate").Value
        rst.Close
    'set the end points of your search
        dStartDate = DateAdd("d", -14, dStartDate)
        dEndDate = DateAdd("d", 14, dEndDate)
    'this query looks for any record on tbleventdelegate that has a startdate between the two end points or an end date between the two end points
        ssql = "SELECT EventStartDate, EventEndDate "
        ssql = ssql & "FROM tblEventDelegate INNER JOIN tblEvent ON tblEventDelegate.EventID = tblEvent.EventID "
        ssql = ssql & "WHERE("
        ssql = ssql & "((tblEvent.EventStartDayDate) Between #" & dStartDate & "# And #" & dEndDate & "#) "
        ssql = ssql & "OR "
        ssql = ssql & "((tblEvent.EventEndDayDate) Between #" & dStartDate & "# And #" & dEndDate & "#) "
        ssql = ssql & ")"
        Set rst = db.OpenRecordset(ssql)
        If rst.RecordCount <> 0 Then
            MsgBox "This delegate is already scheduled to an event within 14 days of beginning or ending of this scheduled event"
        'Else
            'MsgBox "No conflicts"
        End If
        rst.Close
        Set db = Nothing
    End If
    Do I need another ssql along the lines of;

    ssql = "SELECT DelegateID"
    ssql = ssql & "FROM tblEventDelegate INNER JOIN tblEvent ON tblEventDelegate.DelegateID = Me.DelegateID

    Though looking at that it doesn't look right and I'm not sure how I would incorporate it into the statement.

  12. #42
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Did you test my database with information that matches the dates in your data? did it give you the correct results or were those results incorrect as well?

    Without knowing that I would hesitate to offer suggestions on your adapted code, particularly with no example to work with. If you have a copy of your database that has some test data that is giving you the error you want to correct and post it here (access 2003 or less please) I can take a look

  13. #43
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Hi
    Yes I tested the db you sent me and it gave the right results. I will try and post a copy up with some data in but I will have to adapt it because I have signed an NDA for the data.

  14. #44
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    I just thought I can't do this as my db is in A2010 and it includes a navigation form which is not compatible with prvious versions at all and it can't be separated. Can you see what I thoink is happening in that it is only looking for the eventid in the eventdelegate tbl and comparing that to the eventid chosen on the form and then doing the date range calculation rather than the instance of both the eventid and the delegateid and then calculating on that date.

  15. #45
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    If my code works and yours doesn't you either have fields that it's important for you to separate data by or you've modified the code for your specifications and I can't really tell you what's wrong without having the example to work with.

    I don't need your entire database, just the form that you are trying to work with, and example data in the forms that will duplicate the error you are encountering. If you can't provide that I'm a little bit at a loss to provide you an answer.

    You are basically using my code line for line so it's got to be something with your table structure (there's an additional field that needs to be checked)

Page 3 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