Hi!

I have the following issue. I have 2 fields:

ContactTime (General Date)
ReviewDate (Short Date)

The Review date needs to be automatically 14 days after the ContactTime. But when I set the default value of ReviewDate to =[ContactTime] + 14, the ReviewDate includes the time.

I'm trying to run a query on the ReviewDate, only to input a date, but because of the time that's included from the ContactTime field, the query doesn't work if I only put in a date.

Can someone please help me with this one.

Thanks.

If the ReviewDate is always 14 days after ContactTime, then it should not be stored. It is a calculated value that can be derived at any time.

Look into using DateValue([ContactTime]) + 14 OR Int([ContactTime]) + 14

Thanks RuralGuy!!

I've used the DateValue([ContactTime])+14 and it works fine!!

THANKS!

I still stand by my initial post. This value should not be stored. You can still use RG's suggested expression for deriving the date, but do not store it.

I agree with niesz 100%.

What is the reason why it shouldn't be stored? I don't quite understand that part. So if it is not stored, can you still do queries etc with it?

First, data integrity. If your ContactTime field should ever change, without the calculation being re-performed, you now have inaccurate data.

Second, It takes up valuable storage space in the DB, as well as increases the memory/bandwidth used to open the result set.

Why store it? ...just recalc it when needed.



Yes. You can still perform queries using the derived expression.

Absolutely! Queries can do most of your calculations for you. Unless you have some historical reason it is almost always better to calculate the value each time. Do some searching here on the reasons since it has been covered countless times.

Thanks for all your input. I'm definitely still learning heaps and every bit of information helps!

Thanks a lot!

Glad I could help. We *all* started somewhere.