DateDiff

[garygdj]

Is there a way in a query to write a datediff that looks at all the possibilities month, day, year? I have 10 different fields that I need to do a date diff on. I would like to have the fields checked in one place rather than 10 day checks then 10 month checks and 10 year checks. I don't need to use the data any where but I need it to let me know if there is a difference between a calculated date and a date that was input by a user. If there is then I know the user input the wrong date, and it shows as an error.

Thanks

 

[Pat Hartman]

I don't understand the question. DateDiff() gives you the difference between two dates. The difference is returned as days, months, years, hours, or whatever you choose.

 

[garygdj]

It's not working that way for some reason. For example lets say the two dates are 02/01/2016 and 02/16/2021. A date diff should catch that. however, it's not. the fields are both formatted the same. Heres the strange part. If I go back to my table and change the day in the first date from a 01 to a 16 then go back and run the query it will catch it. Or if I change the year in the second date to 2016 it will catch it. I've got to be missing something.

Sorry about the initial question. I left for home and thought about how the question was not really done very well.

 

[Pat Hartman]

Catch what? Both dates are valid and it doesn't matter which order they are placed in the calculation. One way and you'll get a positive number, the other way, you'll get a negative number. How a date is formatted has nothing to do with anything. Formatting is for human convenience. Dates are actually stored as double precision numbers.

Access attempts to save you when you enter invalid/incomplete dates. Sometimes this is good and sometimes it is bad. For example if you are using USA date settings the default short date is mm/dd/yyyy. If you enter 16/02/06 - you'd think you might get an error but you won't. Access will "interpret" the date as 2/6/16. If you enter 16/2/2006, Access will "interpret" the date as 2/16/06. If there is any logical way to rearrange the fields that makes sense, that's what Access assumes. Is that the problem you are having?

If you want to prevent future dates, you can add an edit rule on the table field:
<= Date()

 

[garygdj]

I got it figured out and it was all my fault. I hate those kind of issues.