Storing and retrieving data for months of the year into one field

[cheekybuddha]

Public Enum blMonth
  Jan = 2
  Feb = 4
  Mar = 8
  Apr = 16
  May = 32
  Jun = 64
  Jul = 128
  Aug = 256
  Sep = 512
  Oct = 1024
  Nov = 2048
  Dec = 4091
End Enum

Why does blMonth.Dec = 4091 ?

 

[MajP]

Typo

 

[sonic8]

Access does not have a packed binary data type, though SQL Server does.

Not sure what you mean by packed binary in this context.
Anyways, Access has a binary data type. For reasons beyond my knowledge, such a column just cannot be created in the visual table designer.

 

[The_Doc_Man]

@sonic8 - I may have used the wrong name. After looking it up, I see they call the data type "bit" and it occupies one bit. "Packed" was the name used when you had a "holder" for several "bit" types in the same physical storage element, but it might not have been in SQL context. I must admit that my memory is imperfect. From the description, if you have any BIT fields, SQL server will automatically pack them transparently to you so that you don't know anything about the packing aspect of things. You just see that there are one-bit fields.

bit (Transact-SQL) - SQL Server

The bit data type is an integer data type that can take a value of 1, 0, or NULL, representing Boolean values.

learn.microsoft.com

 

[Dave E]

... or three states.

Modern SQL: Three-Valued Logic (3VL) — Purpose, Benefits and Special Cases

Making decision despite missing SQL data (NULL): How to avoid OR…IS NULL in the WHERE clause.

modern-sql.com

Yes. The check boxes are initialised to ensure a two-state operation, and the code determines whether each is True or False. That part of the code is completed a working satisfactorily. The code tests for True or False so won't look for values.

In other circumstances, I tend to use an IsNothing() function which I found on this site many (many,many) years ago, when I still worked for a living. It appears to deal with Null, zero, "", and empty input boxes without any problems.

 

[Pat Hartman]

I will have unbound 12 check boxes and a separate field that has 12 symbols representing each month

Why would you ever do that? Just use 12 check boxes named Jan, Feb, etc!!! NO CODE is required to populate or save the values. You are overcomplicating the solution and requiring code when none should be necessary.

 

[Dave E]

The reason being that I can make the search easier using one field rather than 12?
I can search for single months or multiple months (seasons, but not exclusively).

By my thinking, 400 records X 12 fields is a lot more than 400 fields...and would use less coding.

 

[MajP]

The reason being that I can make the search easier using one field rather than 12?
I can search for single months or multiple months (seasons, but not

If you wanted the "least code" then you create a field for each month allowing simple add, Edit, delete with no code. Then use a simple union query for all of the searching requiring little or no code.
Size would be irrelevant at 400 records or even 40,000 records.

The "best" most flexinble and efficient way is a normalized child table, which you still could design a simple form with check boxes to add delete child records.

Your solution seems to be neither the easiest or best approach, but you seem set on it and it will probably work fine. Seems unnecessarily kludgy. A solution without a problem.

 

[Pat Hartman]

The reason being that I can make the search easier using one field rather than 12?

Mushing multiple attributes into a single field which you are trying to do and which the bit solution also does, does NOT make anything easier. It forces you to use code when none would otherwise be needed. It forces you to use UDF's in your queries. It prevents the database engine from being able to use indexes to find records and results in full table scans for every search you do. Does that sound more "efficient"? And we won't talk about violating first normal form which is the most basic of all the normal forms.

 

[Dave E]

Thank you both for your comments. I still prefer my method but I accept it may not align with good practice.

 

[Pat Hartman]

I still prefer my method but I accept it may not align with good practice

Okey dokey

 

[Dave E]

Thank you both for your comments. I still prefer my method but I accept it may not align with good practice.

And, of course, it will work.