Data Type of Unbound Control

[Kari]

How do I force an unbound control to be data type Double in Access 2007?

I have a simple form for searching records. It is set to Datasheet view.
There is a Totals row, which calculates several totals. All well and good,
until I tried to display a total for a calculated control. The control in
question is an Extended Price, calculated by multiplying [Cost] by [Qty
Shipped]. The problem is that Access thinks the result is a string somehow,
and only offers me "Count" and "None" as options in the Totals row (the
options available for a String field).

I know I've seen references to a method to tell Access what datatype to use,
rather than having it guess, but I can't find them. I tried wrapping the
calculation in CCur or CDbl, but neither worked.

I'm sure this is a simple question, but I would really appreciated some help
on it!

TIA

Kari

 

[Allen Browne]

Set the unbound control's Format property to:
General Number

You can verify that Access now treats it as a numeric value by trying to
enter a string.

The other important aspect is *not* to programmatically assign a non-numeric
value to it. If you need to clear it, use:
Me.Text0 = Null
not:
Me.text0 = ""

Similarly don't put a non-numeric value in its DefaultValue property.

 

[Kari]

Allen,

Thanks for your reply, but I thought that the Format property didn't change
the data type for the underlying field. e.g. changing the Format property to
Short Date doesn't mean that the time is not stored with the value. What is
Access thinking? Is the difference because the Date field is bound and the
Number field is not?

Considering the source I'm sure your solution will work, but I'd like to
understand the "why" behind it as well. . . . . .

Thanks for your help.

Kari

Set the unbound control's Format property to:
General Number

You can verify that Access now treats it as a numeric value by trying to
enter a string.

The other important aspect is *not* to programmatically assign a non-numeric
value to it. If you need to clear it, use:
Me.Text0 = Null
not:
Me.text0 = ""

Similarly don't put a non-numeric value in its DefaultValue property.

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

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

How do I force an unbound control to be data type Double in Access 2007?

I have a simple form for searching records. It is set to Datasheet view.
There is a Totals row, which calculates several totals. All well and
good,
until I tried to display a total for a calculated control. The control in
question is an Extended Price, calculated by multiplying [Cost] by [Qty
Shipped]. The problem is that Access thinks the result is a string
somehow,
and only offers me "Count" and "None" as options in the Totals row (the
options available for a String field).

I know I've seen references to a method to tell Access what datatype to
use,
rather than having it guess, but I can't find them. I tried wrapping the
calculation in CCur or CDbl, but neither worked.

I'm sure this is a simple question, but I would really appreciated some
help
on it!

 

[Allen Browne]

Try it, Kari.

With bound controls, the Format property does not alter the stored value.

With unbound controls, what happens is that the attempt to format the value
fails if the data type is wrong, and so Access is alerted to the bad data.

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

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

Allen,

Thanks for your reply, but I thought that the Format property didn't
change
the data type for the underlying field. e.g. changing the Format property
to
Short Date doesn't mean that the time is not stored with the value. What
is
Access thinking? Is the difference because the Date field is bound and
the
Number field is not?

Considering the source I'm sure your solution will work, but I'd like to
understand the "why" behind it as well. . . . . .

Thanks for your help.

Kari

Set the unbound control's Format property to:
General Number

You can verify that Access now treats it as a numeric value by trying to
enter a string.

The other important aspect is *not* to programmatically assign a
non-numeric
value to it. If you need to clear it, use:
Me.Text0 = Null
not:
Me.text0 = ""

Similarly don't put a non-numeric value in its DefaultValue property.

How do I force an unbound control to be data type Double in Access
2007?

I have a simple form for searching records. It is set to Datasheet
view.
There is a Totals row, which calculates several totals. All well and
good,
until I tried to display a total for a calculated control. The control
in
question is an Extended Price, calculated by multiplying [Cost] by [Qty
Shipped]. The problem is that Access thinks the result is a string
somehow,
and only offers me "Count" and "None" as options in the Totals row (the
options available for a String field).

I know I've seen references to a method to tell Access what datatype to
use,
rather than having it guess, but I can't find them. I tried wrapping
the
calculation in CCur or CDbl, but neither worked.

I'm sure this is a simple question, but I would really appreciated some
help
on it!

 

[Kari]

Allen,

That makes sense--thanks for the further explanation.

I'm still having trouble. I thought the Totals row wasn't working because
it thought the value wasn't a number (and therefore not summable), but even
with your suggestion of setting the Format to General Number it won't total
it. Did I read somewhere that you can't sum calculated controls? Is there
any way around this? I know I could put a control on the form and have it
calculate the total directly, but I'd really like to use the Totals row
available in Access 2007.

Any ideas?

Thanks again,

Kari

Try it, Kari.

With bound controls, the Format property does not alter the stored value.

With unbound controls, what happens is that the attempt to format the value
fails if the data type is wrong, and so Access is alerted to the bad data.

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

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

Allen,

Thanks for your reply, but I thought that the Format property didn't
change
the data type for the underlying field. e.g. changing the Format property
to
Short Date doesn't mean that the time is not stored with the value. What
is
Access thinking? Is the difference because the Date field is bound and
the
Number field is not?

Considering the source I'm sure your solution will work, but I'd like to
understand the "why" behind it as well. . . . . .

Thanks for your help.

Kari

Set the unbound control's Format property to:
General Number

You can verify that Access now treats it as a numeric value by trying to
enter a string.

The other important aspect is *not* to programmatically assign a
non-numeric
value to it. If you need to clear it, use:
Me.Text0 = Null
not:
Me.text0 = ""

Similarly don't put a non-numeric value in its DefaultValue property.

How do I force an unbound control to be data type Double in Access
2007?

I have a simple form for searching records. It is set to Datasheet
view.
There is a Totals row, which calculates several totals. All well and
good,
until I tried to display a total for a calculated control. The control
in
question is an Extended Price, calculated by multiplying [Cost] by [Qty
Shipped]. The problem is that Access thinks the result is a string
somehow,
and only offers me "Count" and "None" as options in the Totals row (the
options available for a String field).

I know I've seen references to a method to tell Access what datatype to
use,
rather than having it guess, but I can't find them. I tried wrapping
the
calculation in CCur or CDbl, but neither worked.

I'm sure this is a simple question, but I would really appreciated some
help
on it!

 

[Allen Browne]

That's right: you can sum fields, but not controls.

Repeat the expression in the Control Source of the text box in the Form
Footer. For example, if you have a text box named Amount that has
ControlSource of:
=[Quantity] * [PriceEach]
you cannot use:
=Sum([Amount])
but you can use:
=Sum([Quantity] * [PriceEach])

Alternatively, you can create a calculated field in your query, by typing an
expression like this in the Field row in query design:
Amount: [Quantity] * [PriceEach]
Now you do have a field named Amount in the report's RecordSource, so you
can sum it.

Also, make sure you are using the Form Footer section (or a group footer in
a report.) It doesn't work in the Page Footer section.

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

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

I'm still having trouble. I thought the Totals row wasn't working because

 

[Kari]

Allen,

Thanks for the work-arounds. I moved the calculation to the query so that I
could use the built in Totals row to sum it up. Don't know why I didn't
think of that. (I guess that's the downside to having so many ways to skin
the cat--it's easy to loose track of some of them. . . . )

Thanks for all your help!

Kari

That's right: you can sum fields, but not controls.

Repeat the expression in the Control Source of the text box in the Form
Footer. For example, if you have a text box named Amount that has
ControlSource of:
=[Quantity] * [PriceEach]
you cannot use:
=Sum([Amount])
but you can use:
=Sum([Quantity] * [PriceEach])

Alternatively, you can create a calculated field in your query, by typing an
expression like this in the Field row in query design:
Amount: [Quantity] * [PriceEach]
Now you do have a field named Amount in the report's RecordSource, so you
can sum it.

Also, make sure you are using the Form Footer section (or a group footer in
a report.) It doesn't work in the Page Footer section.

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

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

I'm still having trouble. I thought the Totals row wasn't working because

it thought the value wasn't a number (and therefore not summable), but
even
with your suggestion of setting the Format to General Number it won't
total
it. Did I read somewhere that you can't sum calculated controls? Is
there
any way around this? I know I could put a control on the form and have it
calculate the total directly, but I'd really like to use the Totals row
available in Access 2007.