Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    16

    Unanswered: DateDiff Function with Operators

    Hello,

    I am using a search criteria access form which has a combo box with operators (greater than, less than, equals, etc...). I have an Expression that looks like this:

    IIf([Forms]![EvalTstSrch]![cboOperator] Is Null Or [Forms]![EvalTstSrch]![txtDate] Is Null ,True,IIf([SUMMATIVE] Is Null,Null,Eval(DateDiff("d",[Forms]![EvalTstSrch]![txtDate],[SUMMATIVE]) & [Forms]![EvalTstSrch]![cboOperator] & [SUMMATIVE])))

    If the txtDate and the Summative shows there isn't a difference between the two dates and they are the same, my result is nothing even though the operator is "=". I am thinking the format has to be changed because the DateDiff function calculates as zero. So my formula in a sense looks like "0 = 03/06/09". I appreciate anyone's suggestions.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I'm not sure what you're trying to accomplish here. It looks to me as though you're allowing users to enter two dates and an operator, and then produce an expression based on those inputs that may or may not make sense.

    Also, according to the Help file, the function Eval requires a string argument. You need to convert the contents of the brackets into a string and remove the bits not connected to the DateDiff function before that section will work properly:
    Code:
    Eval("DateDiff("""d""", [Forms]![EvalTstSrch]![txtDate], [SUMMATIVE])") & [Forms]![EvalTstSrch]![cboOperator] & [SUMMATIVE]
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Oct 2009
    Posts
    16
    Actually I am having the user enter one date with an operator and compare it to the date associated in the table. The comparison seems to come out right, but when the date matches the date in the table, it isn't display. It will only do greater than or less than, never equal. I need to figure out a formula that if there is no difference, display the date and the record of that date.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    In a table, the Date/Time data type stores a date AND a time. If you want to compare such data with a date entered by a user you have to convert it to only perform the comparison on the date part. If the user only types the date part it is interpreted as 02/15/2008 00:00:00, and 02/15/2008 08:19:48 does not equal 02/15/2008.
    Have a nice day!

  5. #5
    Join Date
    Oct 2009
    Posts
    16
    Unfortunately, the table that is storing date and time stores the time as 12:00:00 AM. The format in access is showing the general date.

    I've added an attachment of the form to give you an idea of what I am doing. The SUMMATIVE row would show the dates. Then in the search criteria you would use the operator and the date entered to find what you are looking for. Hope this helps.
    Attached Thumbnails Attached Thumbnails ScreenShot001.jpg  

  6. #6
    Join Date
    Oct 2009
    Posts
    16
    I found a way to differientiate between the dates since it is a value rather than a date. Here is what I did:

    Code:
    IIf([Forms]![EvalTstSrch]![cboOperator] Is Null Or [Forms]![EvalTstSrch]![txtDate] Is Null,True,
    IIf([SUMMATIVE] Is Null,Null,Eval(DateDiff("d",Date(),[SUMMATIVE]) & [Forms]![EvalTstSrch]![cboOperator]
    & (DateDiff("d",Date(),[Forms]![EvalTstSrch]![txtDate])))))

Posting Permissions

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