evaluate field

[alex]

Using Access ’03…

I have a form (actually used as a subform) with approx. 15 columns.

I attempting to write code that would evaluate 15 fields (in 3 sets)
and ask the question: iff is null then “*”.

For example, if a user types data in one field, I would like Access to
insert “*” in the four other fields (if they are null or contain “”).
If the user types data into two of the fields, then “*” would be
inserted into three fields.

I’m not sure if I need to define three separate arrays, then ask: if
any (contained in the array) is null or “”, then value = “*”.

Any thoughts?
alex

 

[John W. Vinson]

Using Access ’03…

I have a form (actually used as a subform) with approx. 15 columns.

I attempting to write code that would evaluate 15 fields (in 3 sets)
and ask the question: iff is null then “*”.

For example, if a user types data in one field, I would like Access to
insert “*” in the four other fields (if they are null or contain “”).
If the user types data into two of the fields, then “*” would be
inserted into three fields.

I’m not sure if I need to define three separate arrays, then ask: if
any (contained in the array) is null or “”, then value = “*”.

Any thoughts?
alex

What's the structure of your underlying tables? If the asterisk is totally
dependent on the existng value, I'd be leery of storing it at all; if it's
just for display then there may be better ways of doing this. What's the
underlying logic? It sounds like you may have repeating fields in your table,
and might be better served by normalizing the data into a one-to-many
relationship!

 

[Ken Sheridan]

Alex:

If you simply want an asterisk in any column which is otherwise Null or a
zero-length string then why not just set the column's DefaultValue property
to an asterisk in table design, and disallow zero-length strings and Nulls -
the latter by setting the Required property to True (Yes)

Like John, however, I suspect this might really be a normalization issue.

Ken Sheridan
Stafford, England

 

[alex]

Alex:

If you simply want an asterisk in any column which is otherwise Null or a
zero-length string then why not just set the column's DefaultValue property
to an asterisk in table design, and disallow zero-length strings and Nulls  -
the latter by setting the Required property to True (Yes)

Like John, however, I suspect this might really be a normalization issue.

Ken Sheridan
Stafford, England










- Show quoted text -

Ken & John,
Thanks for the help...The table in which we speak is used in 3 queries
as the criteria, it's on the many side of a one-to-many relationship.

For example:
qry1 looks at 5 columns in the aforementioned table. If all 5 are
empty, fine; they should stay null. However, if the user populated
one of the fields, the other 4 need "*" because of the query. If the
user populated two fields, the other 3 need "*".
qry2 looks at the other 5...
qry3 looks at the last 5.

I need to code (probably in the form's close event) to look at every
field (in a grouping) and ask the question above. I could tell the
users to populate the * themselves, but you know how that works!

 

[Clif McIrvin]

On Oct 27, 2:27 pm, Ken Sheridan
<snip>
Ken & John,
Thanks for the help...The table in which we speak is used in 3 queries
as the criteria, it's on the many side of a one-to-many relationship.

For example:
qry1 looks at 5 columns in the aforementioned table. If all 5 are
empty, fine; they should stay null. However, if the user populated
one of the fields, the other 4 need "*" because of the query. If the
user populated two fields, the other 3 need "*".
qry2 looks at the other 5...
qry3 looks at the last 5.

I need to code (probably in the form's close event) to look at every
field (in a grouping) and ask the question above. I could tell the
users to populate the * themselves, but you know how that works!


------
Alex:
Sounds like revising your Query(ies) might be the solution, not
inserting "*" in place of null values ... generally queries can be
written to work correctly with null values.

Post the SQL from one of the queries and I expect you'll get good
suggestions.

 

[Ken Sheridan]

As Cliff says there should be no reason why the query can't handle Nulls.
The real question, however, is whether each set of 5 columns represent values
of the same attribute type, which is what John was referring to when he
talked about 'repeating fields'. If so then the solution is not to work
around the Nulls but to decompose the table so that it is normalized.

A hypothetical example of a set of columns representing values of the same
attribute type would be in a table Contacts where Phone1, Phone2 and Phone3
columns represent different phone numbers for a contact. This would be
decomposed by creating a related Phones table, and possibly a ContactPhones
table if the relationship is many-to-many, i.e. two or more contacts might
share the same phone number(s).

Ken Sheridan
Stafford, England

 

[alex]

As Cliff says there should be no reason why the query can't handle Nulls. 
The real question, however, is whether each set of 5 columns represent values
of the same attribute type, which is what John was referring to when he
talked about 'repeating fields'.  If so then the solution is not to work
around the Nulls but to decompose the table so that it is normalized.

A hypothetical example of a set of columns representing values of the same
attribute type would be in a table Contacts where Phone1, Phone2 and Phone3
columns represent different phone numbers for a contact.  This would be
decomposed by creating a related Phones table, and possibly a ContactPhones
table if the relationship is many-to-many, i.e. two or more contacts might
share the same phone number(s).

Ken Sheridan
Stafford, England








- Show quoted text -

Thanks Ken/Clif...I'll give this some thought.