Suggestions to handle Dates? Text or Date/Time Data type?

[dkmidi]

Hi all!

I see that the sample databases that Microsoft offers all use the Text datatype for things like phone numbers and dates. But wouldn't it make more sense to use the Date/Time data type for dates?

Is there any difference in how it sorts or what things you can do with one data type that you can't with the other?

I've set all my dates up now using the Date/Time data type but would there be any complications to change it to text data type?

Thanks all!

DK

 

[scriverb]

You should certainly use the Date/Time field data types. ACCESS does a lot for you there. Sorting, comparison, etc is all automatic. There are functions to seperate the date fields into its parts and make comparisons etc.

Sure you can store the dates as Text but to do anything worthwhile with them you would first then have to convert them back to Date format with a function call. CDate(. . )

The date/time data type is stored as a real number with the whole number side of the decimal representing the actual day in question. Actually it is the number of days since I think it is 1/1/1900 or something like that. The decimal portion of the date value represents the time since midnight for the day. This is important to know as if you store todays date with the Date() function you will only get the left side of the decimal stored. No time is stored. But, if you store todays date with the Now() function you will get a different value. Date on the left and the time right now. When making a comparison of two fields one with Date() and one with Now() the one with Now() will compare greater than the one with Date. Tricky.

I hope that this has helped you out in understanding this concepts.

Bob Scriver

 

[dkmidi]

Wow! Thanks Bob for the great explanation and the little tricky things!

Much appreciated!

DK