Possible? Using field description to automate storing date as integer.

[natsirtm]

I've been playing around with storing date values as numbers (Integer/Long)
I'm interested in coding something to kind of "automate" it.

My thought was to use the field description to keep track of fields which should be considered dates, and then automating the storage as Integer and the display as Date.

I know I could use the functions I've written to convert to and from integer dates into queries/forms/reports, but I'd like to go a step further.

For eg.
fieldname | data type | description
startdate | ..integer.. | intdate

and then anytime I am putting data in that field, it would use the function to convert the date into an integer, and anytime I am displaying data from that field, it would use the function to convert the integer to a date, allowing me to avoid specifying those functions in every query/form/report.

Is this a wild goose chase? Possible?

TIA

 

[KeithG]

Why do you want to do this?

 

[natsirtm]

Simply to avoid having to explicitly use the conversion functions in every data insert/update or when displaying in form/report.

 

[KeithG]

Why not just store the value as a date?

 

[natsirtm]

Access stores date/time fields as a double. (Storage size: 8 bytes)
Integers only take up 2 bytes.

Comparing dates should also be easier. Instead of;

DateDiff ("d", #6/1/2007#, #6/8/2007#)
= 7

Could just subtract the integers.

2715 - 2708
= 7

 

[stopher]

You can subtract dates also:
NumberOfDays: [Date1]-[Date2]

Is storage really that big an issue for you?
Stopher

 

[natsirtm]

Didn't know you can just subtract dates, that's cool to know.

My concerns are twofold.

#1, In a month, I get close to 125,000 records, with every record having 2-3 date fields. When I made the changes it reduced a 20mb compacted file to less than 10mb.

#2. The purpose of this database is to calculate aging. My Business Days function can be used up to 3 times in the same query. Those queries take a fair amount of time to run. I think I can optimize the Business Days function if I am using Integers.

I'm concerned because the whole point of calculating the aging is to track success for a new Service Level being implemented. So I don't see this database going away anytime soon, and instead of being handled by our Service Provider due to lack of resources - it will just reside locally on my PC.

Therefore I'd like to get it as tight as I can before it grows too large.

 

[chergh]

You should probably thinking about changing to sql server express edition instead of using access.

 

[natsirtm]

That has some distinct possibilities, thanks chergh. (Was unaware of any free sql server version)

 

[KeithG]

With SQL Server Express you have the double the storage capacity of Access.

Access=2gb
SQL Server Express=4gb

 

[gemma-the-husky]

natsirtm

dates are stored as numerics anyway, so you can add/subtract them exactly as any other number. offhand theyre either long integers storing a tickcount, or they are double storing fractional days

either way, i would be very surprised if any vba code could surpass the speed of the inherent date functions in access - in addition there are loads of functions youve probably never even considered - eg weekday, weeknumbers, time handling

i would think you're database compact size reduction you are noticing is more likely to occur by the process of removing temporary objects etc, than anything to do with date stroage

 

[The_Doc_Man]

I'm with Gemma here. Choosing to store dates as something other than their "natural" form - which is itself a typecast anyway - is in all probability a waste of effort. Since dates are already numeric, converting them to a different type of number for the sake of 6 bytes per record is what my Dad used to call "penny wise and pound foolish."

In the bigger scheme of things, doing a frequent data maintenance by repair and compacting will get more space than any data reduction associated with date binary converions.

But then, the ultimate reason you don't want to do that conversion anyway is that we are nearing or already past the point where dates would wrap around a 16-bit integer. Trust me, the anomalies caused by such a wrap-around would be horrendous. Using "native" date format, this cannot happen because your computer won't be running by the time that format runs out - a couple of million days from now. That's several THOUSAND years into the future. By then, I think the warranty will have expired.

Back to the point at hand - 125,000 records a month isn't so bad. That is about 1.5 million records per year. If you have a way to retire older records after they become obsolete, your database will be able to manage that very easily. You can find relevant articles on record life-cycles by searching the forum for "archiving."