Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2011
    Posts
    20

    Unanswered: date syntax in dlookup add date

    I am writing some code for a combobox on click event. I need to check the date that has been selected in the combobox against a table (tblEventDelegates) that holds a list of delegates who have been scheduled onto events with the dates of that event. I need to see if the event that had been selected in the combobox (cmboEvents) is more or less than 14 days from any event that the dlegate has already been scheduled on. I have the following so far:

    Code:
    If Dlookup("[EventStartDate]","tblEventDelegate",EventID = " & Me.cmboEvents & " AND EventStartDate = " & Format(Me.txtEventStart, "\#dd\/mm\/yyyy\#")+14d  Then
    but is giving me an expected:expression error and highlights the first # in the date part. I'm not even sure if this will produce the results I need and need some help or advice on where to go from here. Thanks in advance

  2. #2
    Join Date
    Mar 2011
    Posts
    20
    I've changed it slightly to :
    Code:
    If Dlookup("[EventStartDate]","tblEventDelegate",EventID = " & Me.cmboEvents & " AND EventStartDate = " & Format(Me.txtEventStart, "\mm\dd\yyyy\")+14d ) Then
    but this gives an expected: list separator or ) error but I can't see where

  3. #3
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    1. There is a missing leadin quote before EventID: ..."tblEventDelegate", "EventID = "...
    2. What does this means: Format(Me.txtEventStart, "\#dd\/mm\/yyyy\#")+14d?
    Have a nice day!

  4. #4
    Join Date
    Mar 2011
    Posts
    20
    The +14 is the bit I'm having my main problem with. What I'm trying to do is work out 14 days after the event that has been selected. Even as I type this I realise another problem. What I am trying to do is:
    The user opens the form to schedule a delegate onto an event by selecting from the combobox (I already have code in place to check if they haven't already selected the same event or event on same date) but I need to check if the event they have selected is more or less than 14 days from previous scheduled events as they will be away from their office for too long in a month. I'm n ot sure at all how to do this.
    But +14 will only say more than 14 or will it go up to 14 days I'm wondering

  5. #5
    Join Date
    Mar 2011
    Posts
    20
    I have changed the code now to:
    If Me.cmboEvents.Column(1) = Dlookup("[EventStartDate]","tblEventDelegate", "DelegateID = " & Me.DelegateID) +14d) then

    This compares the date selected in the combobox and compares it to the evntstartdate of the event that the delegate has been scheduled on but the +14 still is the issue for me

  6. #6
    Join Date
    Mar 2011
    Posts
    20
    I have tried yet another way:
    Code:
     If Dlookup("[EventStartDate]","tblEventDelegate","DelegateID = " & Me.DelegateID  AND EventStartDate = " & DateAdd("dd",=< 14,Me.cmboEvents.Column(1))) then
    This throws up an expected list seperator or ) error on the "dd" but I think the logic is better though I'm not sure.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    It's better but in the DateAdd function, the argument for the interval should be "d" not "dd" and the second argument (the number to add) must be a numeric value, not a comparison. The comparison should be made outside the DateAdd function.
    Have a nice day!

  8. #8
    Join Date
    Mar 2011
    Posts
    20
    I am still getting expected;list seperator or ) on the "d" now and am unsure as to where to put the comparison I now have;
    Code:
    If Dlookup("[EventStartDate]","tblEventDelegate","DelegateID = " & Me.DelegateID  AND EventStartDate = " & DateAdd("d", 14,[Me.cmboEvents.Column(1)])<) then
    Also can DateAdd be used to subtract dates as well as add because I need to work out up to 14 days before and after the event the delegate is scheduled on (EventStartdate]

  9. #9
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    From Access help:

    DLookup(Expression, Domain [, Criteria])

    The DLookup() function is used to return a field value in a domain, or specified set of records.
    The function has three arguments: expression, domain, and criteria. (The criteria argument is optional.)
    The expression argument is the name of the field that contains the data in the domain.
    The domain argument is the name of the record set that identifies the domain. It can be the name of a table or a query.
    The criteria argument is an optional string expression used to restrict the range of the data that the DLookup() function is performed on. The criteria argument is identical to the WHERE clause in an SQL expression without the keyword WHERE).


    DATEADD (datepart , number , date)

    The function has three arguments: datepart , number and date.
    datepart is the part of date to which an integer number is added. The valid datepart arguments are (user-defined variable equivalents are not valid):
    "yyyy" = year
    "q" = quarter
    "m" = month
    "y" = dayofyear
    "d" = day
    "ww" = week
    "w" = weekday
    "h" = hour
    "n" = minute
    "s" = second
    number is a numeric expression (Long Integer) that represents the number of intervals added to a datepart of date. Number can be positive or negative.
    date is an expression that can be resolved to a date/time value. date can be an expression, column expression, user-defined variable, or string literal. If the expression is a string literal, it must resolve to a date/time.

    So we have:
    Code:
    If Dlookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID & "  AND EventStartDate = " & DateAdd("d", 14,[Me.cmboEvents.Column(1)])) {Comparison} Then
    Now the question is: What's {Comparison}, i.e. to what do you want to compare the date ([EventStartDate]) returned by the function DLookUp()?
    Have a nice day!

  10. #10
    Join Date
    Mar 2011
    Posts
    20
    I'm trying to work it out ...I think I need to compare the EventStartDate to the Date chosen by the combobox + and - 14 days. With this in mind I have the following:
    Code:
    If Dlookup("[EventStartDate]","tblEventDelegate","DelegateID = " & Me.DelegateID  AND EventStartDate = " & DateAdd("d",14,[Me.cmboEvents.Column(1)])+/-) then
    But still gives me an expectedeparator or ) on the "d". I don't know whether the +/- is right either but I don't know of anyway to write this comparison in VBA.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    number is a numeric expression (Long Integer) that represents the number of intervals added to a datepart of date. Number can be positive or negative.
    Then:
    Code:
    DateAdd("d",-14,[Me.cmboEvents.Column(1)])
    Have a nice day!

  12. #12
    Join Date
    Mar 2011
    Posts
    20
    I am still getting an expected : Separator or ) error on the "d" on the line but all the help I've read on this show that this should be correct.
    My code is now :

    If Dlookup("[EventStartDate]","tblEventDelegate","DelegateID = " & Me.DelegateID AND EventStartDate = " & DateAdd("d",-14,[Me.cmboEvents.Column(1)])) then

  13. #13
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quotes for litteral expression work in pair, i.e. for each leading quote character there must be a matching trailing quote character. Moreover what's in quotes its treated litterally and not evalutated.

    1. "DelegateID = " is a litteral --> OK.
    2. Me.DelegateID is the value of a control (must be evaluated) --> OK.
    3. AND EventStartDate = " Should be a litteral but the starting quote is missing --> Not OK.
    4. DateAdd("d",-14,[Me.cmboEvents.Column(1)]should be OK (expression to be evaluated) but is now a litteral because what should be the trailing quote of 3. (above) actually is an opening quote for 4.

    Put a breakpoint on the offending line and when the execution of the code reaches it, open the immediate window type in:
    Code:
    Print ","DelegateID = " & Me.DelegateID AND EventStartDate = " & DateAdd("d",-14,[Me.cmboEvents.Column(1)])
    and see what you get in return.
    Have a nice day!

  14. #14
    Join Date
    Mar 2011
    Posts
    20
    Thanks I'm now making some headway. I put in the quote on EventStartDate and yes the code stopped giving me the error expected : separator or ). I put the breakpoint in and then put in the code in the immediate window and it said it couldn't find the 1 refernced which must be the column of the combobox. I have 2 columns in my combobox the first is the eventid which I need to reference as I have an update query running on that and the 2nd is the date which i need for this code. column 0 is for the eventid and column 1 is for the date ( or so I thought)

  15. #15
    Join Date
    Mar 2011
    Posts
    20
    I changed it to (2) just to check the result and it still says it "can't find the field `1` referred in your expression". I realise now that that is not the column but a field maybe in a table but I don't know what that is.

Posting Permissions

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