# Thread: DateDiff Function with Operators

1. Registered User
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. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,102
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]`

3. Registered User
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. Moderator
Join Date
Mar 2009
Posts
5,442
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.

5. Registered User
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.

6. Registered User
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
•