Variable Initialisation Best Practice

[GK in the UK]

Hello all


Novice Access user here. Quick intro posted in Introductions but I'm basically an old Pascal hand of only moderate experience.


I seem to recall that in Pascal, it was Good Practice to initialise any variable on definition so I'm having to make a bit of adjustment with having all these nulls floating around. Undefined variables were guaranteed to (and did) introduce all sorts of garbage into your Pascal application.


I just got to a bit in my vba code where I'm about to set a field (column?) to 1 on first use. Then I thought, why aren't I setting the default in the Access table definition. I did set some numeric variables as defaults but I also read (I think) that string fields should never be initialised in the table. Allen Browne's site, maybe. I'm favouring setting numerics on first use in VBA code as it takes away the uncertainty (Did I set a default for this ?)


What's the accepted good practice on this ?

 

[Galaxiom]

VBA variables and field values are quite different things.

Nulls are fine in table fields. It wouldn't make sense to put a value in a field if you don't have a valid one because that would be misleading and may cause calculation errors. For example Nulls are skipped when calculating Counts and Averages in Aggregates so putting zero in will give a different answer to a Null.

In VBA, variables are automatically initialised when declared.

Variant is initialised as Empty.
Numeric types are initialised as zero.
Strings are initialised with a Zero Length String.

Only a Variant datatype can hold a Null. The others will throw an error if given a Null.

Object variables are usually declared as a particular type but they can simply be declared as Object. Variant also holds objects.

Undeclared variables and variables with no type declared are Variant.

All variables should be declared and the Option Explicit declaration should be at the top of every module to ensure undeclared variables are not permitted. A setting can be changed in the VBA editor Options to always begin new modules with Option Explicit.

 

[The_Doc_Man]

As Galaxiom suggests, it is not necessary to initialize any variable declared in any statement beginning with a Dim, Public, Private, or Static keyword. (Also a couple of others that are rarely used.) I will add a couple of fine points for you.

Object variables are normally instantiated to Nothing (which is the address equivalent to Null in a more traditional variable.) Remember that when you have object variables, you do not use "Var = Value" syntax. You use "Set var = object" syntax. However, you can instantiate an object variable using "Dim objvar as NEW type" which has the effect of creating a new object and initializing objvar at the same time. The new object is itself empty other than the object's intrinsic initialization.

You cannot use "Dim var As type = value" syntax to declaratively initialize anything that needs it in VBA because that isn't part of the language declaration. (VB could do it, but not VBA).

As noted above, it is not necessary to initialize any variable declared in any statement beginning with a Dim, Public, Private, or Static keyword. Note, however, that if you are declaring named constants, you could say "Public Const var As type = value" and that would work.

One other way of assigning values to variables involves the Enum statement, which you would use if your initialization involved formal state-name variables. If this interests you, look it up with "VBA Enum Syntax" and you will see some nice articles with examples. In this case, Enum declarations are constants. You cannot declare "Waiting" as an enum state with a value of 1 and then later change it to have a value of 2. The defaults for Enum variables are "Public Const" for the value-names declared that way.

 

[GK in the UK]

Thank you Galaxiom and The_Doc_Man.


Can you help me with a solution to store 16 options (yes/no) in an integer ?


It's been a long time, I think I used to do this with a SET.
So I want the equivalent of OptionSet = OptionSet + [Option1]
Where OptionSet is 16 bits.
Then I would test with
If Option1 in OptionSet then
... do something


Looking for the VBA equivalent and trying to avoid 16 yes/no fields in my record

 

[Galaxiom]

Can you help me with a solution to store 16 options (yes/no) in an integer ?

That isn't typically done. Normally you would have separate fields.

However if you really must, look into the use of Bitwise Operators. They use AND and OR (just like the Boolean combinations).

There is an example that uses them for a different purpose in post 6 here.

 

[GK in the UK]

Well, I did wonder if it departed from the usual practice. I did have a look at the use of the enum type but I didn't think it was the solution.


I don't need 16 yes/no options per record, even 8 would be plenty but I like to allow for future expansion. It just seems such a waste of 7 bits for each yes/no. Guess I need to let go of my old obsession to save every scrap of storage space.


Thank you again.

 

[Galaxiom]

I don't need 16 yes/no options per record, even 8 would be plenty but I like to allow for future expansion. It just seems such a waste of 7 bits for each yes/no. Guess I need to let go of my old obsession to save every scrap of storage space.

SQL Server condenses eight bit fields into one in the background.

You could use it as your back end then you would wouldn't have to feel like you were wasting storage.

 

[Mark_]

I don't need 16 yes/no options per record, even 8 would be plenty but I like to allow for future expansion. It just seems such a waste of 7 bits for each yes/no. Guess I need to let go of my old obsession to save every scrap of storage space.

In a database you normally would have child records for "Yes/No" values when you start getting into "8 would be plenty, but I like ot allow for future expansion". This is because you can sort and order on a field far easier than you can on the return value from a function. It also means I can ask the SQL server to get me the 74 records with "Yes/No number 12 set to YES" rather than asking "Give me all 778,445 records so I can figure out which ones have Yes/No number 12 set to YES".

Saving a fraction of some small coin's worth of storage space does not equate to the excessive use of resources to do extensive calculations to gain such a small advantage.

 

[The_Doc_Man]

The philosophy of bit-packing came about when the memory map of any task was a 16-bit address space. You had 64KB and that was IT. Under those circumstances, bit-packing was not only useful, it was necessary. However, as time has passed, the memory model in question has expanded significantly. Where you once strained at 64KB, now you can have program address spaces up to 4GB. Access databases have a limit of 2GB per database file, but that gives you room for a much larger working area.

Of course there are cases where your data comes to you bit-packed and you must unravel it. There, the bitwise AND, OR, NOT, and other operators become helpful. But for ease of programming you might do better to express each of your packed values as a simple Boolean variable or Yes/No field. I say that because while I have used all sorts of insane bit-packing schemes, it is always more logical and syntactically simpler to just write a statement that asks if the XYZ field or variable was TRUE - without unpacking it first.

Just for snorts & giggles, imagine a record with a bunch of data in it to describe a person, including long name, address, phone, and a few other personal descriptive items. So that typically takes up 40 bytes for a name, 80 bytes for an address, 10 bytes for a phone, let's give up another 20 bytes for other personal data, and then add your eight attributes. That's either 151 bytes (with packed attribute flags) or 158 bytes (with unpacked attribute flags.) Relatively speaking, 7 bytes / 150 (approximately) total is just less than 5%. Further, if you had 10,000 records, that is 1.5 million bytes of which 70KB is in question. You'll never notice the difference.

IF you have the choice, it is my considered advice to not bother with bit packing.

 

[GK in the UK]

I'm definitely dropping the bit-packing largely for evaluation difficulties. Pascal had a language construct to do it without bit operations but as you say we're in different times. Although, for different reasons, I may not use yes/no fields. I need to digest this article and decide.

Oh, as a new member I can't post the link but it's Allen Browne's article NoYesNo

 

[The_Doc_Man]

I believe you refer to this article.

http://allenbrowne.com/NoYesNo.html

Allen's articles are commonly seen here. While I don't 100.00% agree with him on every thing he writes, my disagreements are VERY VERY limited and are more about style than anything strictly technical.

You would rarely go wrong by paying attention to an Allen Browne article.

 

[Pat Hartman]

I don't need 16 yes/no options per record, even 8 would be plenty but I like to allow for future expansion

I am 100% with Mark on this. When you have more than one of something, you have many and many requires a separate table, even if each record has only a typeField (so you know which option you are talking about) and a bit field or an integer to store the value.

I happen to agree with allen's position on Yes/No fields. Although it is convenient to have Access make the check box automatically when you build a form, I think it is more important to retain the option of null to represent an unknown answer so my apps always use integers. If there really is just two states, I set a default at the table level to 0 or -1 depending on which makes sense as the default. Otherwise all fields default to null.

I also Change the AllowZeroLengthString default on the table from Yes to No. MS has vacillated on this over the years and you will see some Access versions where the default is No and others where the default is Yes. The problem with allowing ZLS is that they add another layer of confusion. Now I have to remember to check for Null OR "" when I am working with a text field rather than just null. ZLS is not an option for numeric or date data types. As the name implies, it applies ONLY to strings. To effectively use ZLS = Yes, you really must make the field required. That is the only option that makes sense. In reality, this has only caused me a problem in a couple of cases. That is with data being imported from a mainfram where the records were fixed length. Those are stuffed with ZLS to space them out. Now I just link to my import file and use an append query that gets rid of the ZLS to avoid the problem.

 

[GK in the UK]

This has been an interesting discussion. I had to smile to myself knowing that I'd come in here wanting to store 16 flags in 2 bytes, and gone out understanding that I should use 2 bytes to store 1 flag.

 

[Pat Hartman]

the times they are a changin'

The Y2K problem (one of them) was caused because systems didn't store the century because 2 bytes was 2 bytes too many. The other Y2K problem was caused because not all programmers who knew the 100 year rule also knew the 400 year rule which cancelled the 100 year rule and made 2000 a leap year. If they didn't know the 100 year rule they automatically thought 2000 was a leap year because it is divisible by 4. So they were just "fat, dumb, and happy" as it were.

 

[The_Doc_Man]

And of course, the NEXT big date problem is 8 millennia hence, when we have the Y10K problem. By then, of course, we will have had to adjust dates at least once, maybe twice, because the current "leap year" rule breaks sometime after the year 3100.

That's what we get for having a year that is 365.2422 days long.

 

[Pat Hartman]

I never took any Y2K work because I can truly say that I never caused any of the problems and I'm certainly not going to take any 3100 work. Back in 1972, I happened to develop a mortgage application which had a 30 year horizon and since thorough texting means that you have to test at the margins of your data, I discovered both problems early in our testing.