If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DB Data Storage by Variable Type
Of interest are Date Type and String (Text) Type.
1) Since the DB stores Dates as a double is it best to use the default or go with a string in the format YYYYMMDDHHMMSS, or some other way When saving / seeking (finding) or whatever are there any advantages / disadvantages to either storage method? Is there a better format? 2) For strings (text) is -- All -- lower case, upper case, Title Case offer any advantages or disadvantages again in regard to saving, seeking (finding) etc.? Thanks David |
#2
|
|||
|
|||
DB Data Storage by Variable Type
"David" wrote in message
... Of interest are Date Type and String (Text) Type. 1) Since the DB stores Dates as a double is it best to use the default or go with a string in the format YYYYMMDDHHMMSS, or some other way When saving / seeking (finding) or whatever are there any advantages / disadvantages to either storage method? Is there a better format? Store date/time values in Date fields. Not only will it be more compact than a string in the format you suggest, but in the normal way of things you'll have fewer conversions to do between real date values and dates encoded as strings. 2) For strings (text) is -- All -- lower case, upper case, Title Case offer any advantages or disadvantages again in regard to saving, seeking (finding) etc.? I don't believe it makes any difference as far as comparisons (seeking and finding) are concerned, unless you need to enforce exact, same-case comparisons. Forcing a binary comparison (identical case) instead of a text comparison can add overhead that slows a query down a lot. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#3
|
|||
|
|||
DB Data Storage by Variable Type
If you want to do date calculations, you need to use the Double datatype.
While you can convert a date string into a numeric value, it adds an extra step and the corresponding overhead. Windows doesn't care, when searching, what case you are using. Store strings the way you expect to use them so that you do not need to use extra code to render them the way you want. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "David" wrote in message ... Of interest are Date Type and String (Text) Type. 1) Since the DB stores Dates as a double is it best to use the default or go with a string in the format YYYYMMDDHHMMSS, or some other way When saving / seeking (finding) or whatever are there any advantages / disadvantages to either storage method? Is there a better format? 2) For strings (text) is -- All -- lower case, upper case, Title Case offer any advantages or disadvantages again in regard to saving, seeking (finding) etc.? Thanks David |
#4
|
|||
|
|||
DB Data Storage by Variable Type
Thanks Mr. Goldgar and Mr. Meyer
Helps clear up some "lingering" question as to best approach. Have a nice evening. David "Arvin Meyer [MVP]" wrote in message ... If you want to do date calculations, you need to use the Double datatype. While you can convert a date string into a numeric value, it adds an extra step and the corresponding overhead. Windows doesn't care, when searching, what case you are using. Store strings the way you expect to use them so that you do not need to use extra code to render them the way you want. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "David" wrote in message ... Of interest are Date Type and String (Text) Type. 1) Since the DB stores Dates as a double is it best to use the default or go with a string in the format YYYYMMDDHHMMSS, or some other way When saving / seeking (finding) or whatever are there any advantages / disadvantages to either storage method? Is there a better format? 2) For strings (text) is -- All -- lower case, upper case, Title Case offer any advantages or disadvantages again in regard to saving, seeking (finding) etc.? Thanks David |
#5
|
|||
|
|||
DB Data Storage by Variable Type
"Dirk Goldgar" wrote in
: I don't believe it makes any difference as far as comparisons (seeking and finding) are concerned, unless you need to enforce exact, same-case comparisons. Forcing a binary comparison (identical case) instead of a text comparison can add overhead that slows a query down a lot. I you need to distinguish case, then you probably shouldn't be using Access. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#6
|
|||
|
|||
DB Data Storage by Variable Type
"Dirk Goldgar" wrote in
: Store date/time values in Date fields. Not only will it be more compact than a string in the format you suggest, but in the normal way of things you'll have fewer conversions to do between real date values and dates encoded as strings. It also means you can do date math, and get all the benefit of the fact that the date functions understand the calendar. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#7
|
|||
|
|||
DB Data Storage by Variable Type
"David W. Fenton" wrote in message
36.94... "Dirk Goldgar" wrote in : Store date/time values in Date fields. Not only will it be more compact than a string in the format you suggest, but in the normal way of things you'll have fewer conversions to do between real date values and dates encoded as strings. It also means you can do date math, and get all the benefit of the fact that the date functions understand the calendar. Yes, but you can do those things when your dates are stored as strings, so long as you convert to a Date data type first, do your date math and use your date functions, then convert from Date to string again on the way out. Those are the sort of extra conversions I was talking about. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|