Number Fields

  • Thread starter Thread starter Mike G.
  • Start date Start date
M

Mike G.

I have a form populated with number fields used to enter
time spent meeting with a client. There are 31 fields
representing the number of days in a given month. Because
these are formatted as a number type field with 1 decimal
place, the fields appear as 0.0 until I enter data into
them. This makes the form look busy. Is there a way for
each number field to appear blank until data is entered
into it?
Thanks for any help!
 
Open your table in design view.
In the lower pane, delete the 0 from the Default Value of each field.

BTW, this is not a good design. Better to use a related table, with just 3
fields:
1) the foreign key (which client);
2) a date field (which date);
3) a number field (which value.

That will allow you much more flexibility to query and evaluate your data.
 
-----Original Message-----
Open your table in design view.
In the lower pane, delete the 0 from the Default Value of each field.

BTW, this is not a good design. Better to use a related table, with just 3
fields:
1) the foreign key (which client);
2) a date field (which date);
3) a number field (which value.

That will allow you much more flexibility to query and
Click to expand...
evaluate your data.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Click to expand...


Thank you Allen for your help. I agree that this is not a
good design, but I need to do it this way because it
replicates a Mass. state form that needs to be filled out
for billing purposes each month. I duplicated the state
form as a report.
Your suggestion to remove the "0" from the default did
the trick. However, it caused another problem. I used a
query as the form's source and had a field that would
total the hours for the month. Since I changed the
default, the [TotalHours] field doesn't calculate
anymore. Any suggestions?
Thanks!
 
Without knowing much about your TotalHours field, I'm guessing that you need
to use Nz() in the expression to convert nulls to zero?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

-----Original Message-----
Open your table in design view.
In the lower pane, delete the 0 from the Default Value of each field.

BTW, this is not a good design. Better to use a related table, with just 3
fields:
1) the foreign key (which client);
2) a date field (which date);
3) a number field (which value.

That will allow you much more flexibility to query and
Click to expand...
evaluate your data.


Thank you Allen for your help. I agree that this is not a
good design, but I need to do it this way because it
replicates a Mass. state form that needs to be filled out
for billing purposes each month. I duplicated the state
form as a report.
Your suggestion to remove the "0" from the default did
the trick. However, it caused another problem. I used a
query as the form's source and had a field that would
total the hours for the month. Since I changed the
default, the [TotalHours] field doesn't calculate
anymore. Any suggestions?
Thanks!
Click to expand...
 
Thank you Allen for your help. I agree that this is not a
good design, but I need to do it this way because it
replicates a Mass. state form that needs to be filled out
for billing purposes each month. I duplicated the state
form as a report.
Click to expand...

Just a comment:

This is an EXCELLENT example of the principle that it's a Bad Idea to
confuse data *storage* with data *presentation*.

Using a Massachusetts state form as a design guide for a relational
database is *quite certain* to give you a non-normalized, flawed
design.

It's almost always preferable to store your data in a properly
normalized set of tables, based on the *logical* relationships between
the data items. Access has a powerful report generator; between using
Queries to assemble the data, and the various report tools, it should
be possible to regenerate any desired layout of data.

John W. Vinson[MVP]
(no longer chatting for now)
 
Just a comment:

This is an EXCELLENT example of the principle that it's a Bad Idea to
confuse data *storage* with data *presentation*.

Using a Massachusetts state form as a design guide for a relational
database is *quite certain* to give you a non- normalized, flawed
design.

It's almost always preferable to store your data in a properly
normalized set of tables, based on the *logical* relationships between
the data items. Access has a powerful report generator; between using
Queries to assemble the data, and the various report tools, it should
be possible to regenerate any desired layout of data.

John W. Vinson[MVP]
(no longer chatting for now)
.
Click to expand...
John,
Thank you for your comments. While this design was not
the best way of creating what I needed, it did work. The
only problem I was having was that the fields had zero's
in them and made the form look busy. It calculated total
hours and total dollars just fine. It wasn't until I
removed the zero in the default setting of the table
design for each number field that it wouldn't calculate
anymore. I added Nz to my formula, but it didn't help.
That's where I am now.
 
John,
Thank you for your comments. While this design was not
the best way of creating what I needed, it did work. The
only problem I was having was that the fields had zero's
in them and made the form look busy. It calculated total
hours and total dollars just fine. It wasn't until I
removed the zero in the default setting of the table
design for each number field that it wouldn't calculate
anymore. I added Nz to my formula, but it didn't help.
That's where I am now.
.
Click to expand...
I forgot to add the formula information:
There are 31 fields like this [SDate1], [SDate2] etc.
In a query, I added a [TotalHours] and [Amount] field. I
also have a [Rate] field that stores the $ amount charge
per hour. The formulas are:

TotalHours:Nz([SDate1]+[SDate2]+[SDate3] and so on)
Amount:[TotalHours]*[Amount]
 
The
only problem I was having was that the fields had zero's
in them and made the form look busy.
Click to expand...

I think that normalizing the data is still a good idea... but you can
get around this problem using the somewhat obscure "four part format"
property of numbers. You can set the Format property of a textbox to
(for instance)

$#,##0.00;($#,##0.00)[Red];"";"Undefined"

to display positive numbers like $32,834.00; zeros as an empty string;
negatives as ($3,204.50) in red; and NULL values as the word
Undefined. The four formats apply to positive, negative, zero and NULL
values respectively.

In you case, you could just use the "" format in the third position to
suppress display of zero values.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top