Date between in recordset maybe

[AndycompanyZ]

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:

 
estartdate = DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID)
delid = DLookup ("[DelegateID]","tblEventDelegate", "EventStartDate = "& Me.cmboEvents.Column(1))

and then something like:

 
If Abs(DateDiff("d", estartdate, delid, Me.cmboEvents.Column(1))) <= 14 Then
    MsgBox "You have scheduled this delegate onto an event within 2 weeks"

[AndycompanyZ]

here is a screenshot of the 3 tables

[AndycompanyZ]

here is a scrshot of the eventdeltable in datasheet view

[rpeare]

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:

 
estartdate = DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID)
delid = DLookup ("[DelegateID]","tblEventDelegate", "EventStartDate = "& Me.cmboEvents.Column(1))

and then something like:

 
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.

[orange]

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?

[AndycompanyZ]

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)

[AndycompanyZ]

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.

[rpeare]

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?

[AndycompanyZ]

Yes that's right

[rpeare]

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)

[AndycompanyZ]

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:

 
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.

[rpeare]

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

[AndycompanyZ]

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.

[AndycompanyZ]

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.

[rpeare]

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)