can"t find where to change data type

Dear Access Group:

This is the most basic and most embarrassing of questions, but I
cannot find where to change the data type of a text field that I have
in Access 2003 to a number field. I've searched high and low through
help databases and on the internet.

The directions say to :
Open the table in Design view
Click the Data Type column of the field you want to change, click the
arrow, and select the new data type.

I'm in design view, and I've gotten everything done for my
application, but I need to change the data types of several text
fields to number fields. I just can't make the data type column thing
pop up for the field that I've highlighted so that I can make the
change. Please let me know what I'm overlooking.

By the way, while I'm here, how can I make one of my numerical
database fields to be the sum total of several other numerical
fields. Thank you all and talk to you soon!

Sincerely,

Michael

Re: can"t find where to change data type

mlwerth wrote:
> Dear Access Group:
>
> This is the most basic and most embarrassing of questions, but I
> cannot find where to change the data type of a text field that I have
> in Access 2003 to a number field. I've searched high and low through
> help databases and on the internet.
>
> The directions say to :
> Open the table in Design view
> Click the Data Type column of the field you want to change, click the
> arrow, and select the new data type.
>
> I'm in design view, and I've gotten everything done for my
> application, but I need to change the data types of several text
> fields to number fields. I just can't make the data type column thing
> pop up for the field that I've highlighted so that I can make the
> change. Please let me know what I'm overlooking.

What you did not state is if you have a front/backend setup and you are
attempting to update the tables in the front end.

If you do have a front/backend setup, are you in the backend?

> By the way, while I'm here, how can I make one of my numerical
> database fields to be the sum total of several other numerical
> fields. Thank you all and talk to you soon!

Use a query...unless you are seeking to remove those fields and simply
have 1 field...then delete the fields. With a query, you can do
something like
TotField:[fld1]+[fld2]+[fld3]

Lips
http://www.youtube.com/watch?v=Dq42To1QgtY
>
> Sincerely,
>
> Michael

Re: can"t find where to change data type

"mlwerth" wrote

> The directions say to :
> Open the table in Design view
> Click the Data Type column of the field you want to change, click the
> arrow, and select the new data type.
>
> I'm in design view, and I've gotten everything done for my
> application, but I need to change the data types of several text
> fields to number fields. I just can't make the data type column thing
> pop up for the field that I've highlighted so that I can make the
> change. Please let me know what I'm overlooking.

I'm sorry, but I don't understand what you mean by "I just can't make the
data type column thing
pop up for the field that I've highlighted so that I can make the change."
If you have the Table open in Design View, you should see three columns,
"Field Name", "Data Type", and "Description." If you place the mouse
pointer in the column "Data Type" of the Field you want to change, and
click, you should see a vertical bar for entering text and, on the right of
that column, a little box with a down-arrow. If you click the down arrow, it
should drop down a list of data types, and you should be able to click on
"Number", and the drop-down will disappear but the column should read
"Number". In the area below the list of fields, you should see the Field's
properties, one of which "Field Size", is where you specify the type of
number, again by clicking, and choosing from a drop-down.

Even for linked tables, where you can't change the properties, it will let
you do this; but, when you try to close the table view, it won't save them.

Maybe you can list, at this level of detail, what you are doing, what you
see, and what happens when you click, and someone can help you.


> By the way, while I'm here, how can I make one of my numerical
> database fields to be the sum total of several other numerical
> fields. Thank you all and talk to you soon!

In Access, you cannot define a Field in a table to be the result of a
calculation. You can do that calculation in a Query, or in the Report, or
Form in which you are displaying the data.

Larry Linson
Microsoft Access MVP

Re: can"t find where to change data type

Thank you everyone!

Hi Larry:

I finally found that behind the form that I was designing was a screen
that allowed me to switch to table design, and then I changed the
field types from text to number no problem.

Now, about the query! I've got 9 number fields that I want to total,
and have it show up on my form. So, I'm in the form design view, and
my form is looking beautiful. I have a field on the form that I want
to be the total of the 9 fields, but I was told that one field cannot
be based on a calculation from other fields.

How do I get a box to appear on the screen where I can put a formula
into it to calculate the sum of these other 9 fields? What menu
commands do I use, or can I use the mouse to drag a "query box" of
some kind onto the screen where I can program it? I really know
nothing about how to make a query appear on my form desgin. Thank you
again and talk to you soon.

Sincerely,

Michael

Re: can"t find where to change data type

mlwerth wrote:
> Thank you everyone!
>
> Hi Larry:
>
> I finally found that behind the form that I was designing was a screen
> that allowed me to switch to table design, and then I changed the
> field types from text to number no problem.
>
> Now, about the query! I've got 9 number fields that I want to total,
> and have it show up on my form. So, I'm in the form design view, and
> my form is looking beautiful. I have a field on the form that I want
> to be the total of the 9 fields, but I was told that one field cannot
> be based on a calculation from other fields.

Someone must be pulling your leg then.

> How do I get a box to appear on the screen where I can put a formula
> into it to calculate the sum of these other 9 fields? What menu
> commands do I use, or can I use the mouse to drag a "query box" of
> some kind onto the screen where I can program it? I really know
> nothing about how to make a query appear on my form desgin. Thank you
> again and talk to you soon.

In your query you can create a calculated field. Let's say you want to
add fld1...fld3. You could enter in the querybuilder
TotalOfFields : [fld1] + [fld2] + [fld3]

There's the chance you want to create the calculated field on the form
instead of the query. In the control source of the field (TotalFlds)
you could enter something like
=[fld1] + [fld2] + [fld3]

There's a caveat. What happens if you have Null values in the values to
be summed. Let's pretend fld2 is blank.
fld1 = 1
fld2 = Null
fld3 = 2
? fld1 + fld2 + fld3
Null
That's not right. Right? You want to see a 3 as the result.

You need to do 1 of 2 things. NZ() the fields or change the table slightly.

Using NZ, you can force a value of zero if null. Ex:
=NZ([fld1],0) + NZ([fld2],0) + NZ([fld3],0)

If you do have null values (open the table and scan thru it looking for
blanks in the numeric fields). If some fields are blank, you can fix
the table it so all records will have zeros in the fields.

Open the table and ensure the DefaultValue is 0 for each fld to be
summed (If the DefaultValue says Null or is blank, enter a zero).

Next, update all fields used in your summing and set the value to 0 if
null. Ex:
UPDATE Table1 SET Table1.Fld1 = 0
WHERE (((Table1.Fld1) Is Null));

Run this query for each field to be summed...in your case 9 times. Once
all null values have been updated to zero all new records will default
to zero for those fields. You won't need to NZ() them.

>
> Sincerely,
>
> Michael
>

Re: can"t find where to change data type

Hi Salad:

Thank you for the excellent information. I plan to use the NZ
feature, because we don't want to automatically populate the fields
because we want to know if someone puts a zero in on purpose or some
other value, then we will know if the field has been reviewed and
updated.

Now, let me break down my question as simply as possible.
1) I've got a field on my form design that is called TtlProf . This
field will be the sum of the other 9 fields on the screen called
m1PY , m2PY, m3PY, etc.
2) Where is the control source that you've discussed for the TtlProf
field?
3) I clicked on the field box on the form design and typed what you
said, but when I went to view it, it said #Name?
4) I must therefore not be finding a think called control source on my
form design. I have the field there on the screen, and I want it to
automatically be the sum of these other 9 fields.

This has got to be simple, but I just don't know where to click to
type in the code. Thank you very much for your help.

Sincerely,

Michael




On Jan 11, 3:35=A0pm, Salad wrote:
> mlwerth wrote:
> > Thank you everyone!
>
> > Hi Larry:
>
> > I finally found that behind the form that I was designing was a screen
> > that allowed me to switch to table design, and then I changed the
> > field types from text to number no problem.
>
> > Now, about the query! =A0I've got 9 number fields that I want to total,
> > and have it show up on my form. =A0So, I'm in the form design view, and
> > my form is looking beautiful. =A0I have a field on the form that I want
> > to be the total of the 9 fields, but I was told that one field cannot
> > be based on a calculation from other fields.
>
> Someone must be pulling your leg then.
>
> > How do I get a box to appear on the screen where I can put a formula
> > into it to calculate the sum of these other 9 fields? =A0What menu
> > commands do I use, or can I use the mouse to drag a "query box" of
> > some kind onto the screen where I can program it? =A0 I really know
> > nothing about how to make a query appear on my form desgin. =A0Thank you=

> > again and talk to you soon.
>
> In your query you can create a calculated field. =A0Let's say you want to
> add fld1...fld3. =A0You could enter in the querybuilder
> =A0 =A0 =A0 =A0 TotalOfFields : [fld1] + [fld2] + [fld3]
>
> There's the chance you want to create the calculated field on the form
> instead of the query. =A0In the control source of the field (TotalFlds)
> you could enter something like
> =A0 =A0 =A0 =A0 =3D[fld1] + [fld2] + [fld3]
>
> There's a caveat. =A0What happens if you have Null values in the values to=

> be summed. =A0Let's pretend fld2 is blank.
> =A0 =A0 =A0 =A0 fld1 =3D 1
> =A0 =A0 =A0 =A0 fld2 =3D Null
> =A0 =A0 =A0 =A0 fld3 =3D 2
> =A0 =A0 =A0 =A0 ? fld1 + fld2 + fld3
> =A0 =A0 =A0 =A0 Null
> That's not right. =A0Right? =A0You want to see a 3 as the result.
>
> You need to do 1 of 2 things. =A0NZ() the fields or change the table sligh=
tly.
>
> Using NZ, you can force a value of zero if null. =A0Ex:
> =A0 =A0 =A0 =A0 =3DNZ([fld1],0) + NZ([fld2],0) + NZ([fld3],0)
>
> If you do have null values (open the table and scan thru it looking for
> blanks in the numeric fields). =A0If some fields are blank, you can fix
> the table it so all records will have zeros in the fields.
>
> Open the table and ensure the DefaultValue is 0 for each fld to be
> summed (If the DefaultValue says Null or is blank, enter a zero).
>
> Next, update all fields used in your summing and set the value to 0 if
> null. =A0Ex:
> =A0 =A0 =A0 =A0 UPDATE Table1 SET Table1.Fld1 =3D 0
> =A0 =A0 =A0 =A0 WHERE (((Table1.Fld1) Is Null));
>


> Run this query for each field to be summed...in your case 9 times. =A0Once=

> all null values have been updated to zero all new records will default
> to zero for those fields. =A0You won't need to NZ() them.
>
>
>
>
>
> > Sincerely,
>
> > Michael- Hide quoted text -
>
> - Show quoted text -

Re: can"t find where to change data type

mlwerth wrote:

> Hi Salad:
>
> Thank you for the excellent information. I plan to use the NZ
> feature, because we don't want to automatically populate the fields
> because we want to know if someone puts a zero in on purpose or some
> other value, then we will know if the field has been reviewed and
> updated.
>
> Now, let me break down my question as simply as possible.
> 1) I've got a field on my form design that is called TtlProf . This
> field will be the sum of the other 9 fields on the screen called
> m1PY , m2PY, m3PY, etc.
> 2) Where is the control source that you've discussed for the TtlProf
> field?

You select TextBox from the Toolbox and put the new textbox on the form.
You can dbl-click on this new textbox on the form and the property
sheet will appear. In the Other tab, you can set the name to TtlProf
(if it doesn't already exist). In the Data tab, set the control source
to the formula to add the field.

To help you understand the concept, go thru a quick Form/New/FormWizard
process. Select a few fields, call/save the form as junk, and open it
for modification/design. Now from the toolbox add a command button,
listbox, combo box, checkbox, frame, etc. Then click on each type and
look at the property sheet for each object. Also, the fields from a
table have a control source filled in. This is a "bound" field
associated with a table field. An unbound field isn't associated with a
table field. Many properties are the same for all types but there may
be new ones as well. It would pay to get familiar with them (not
memorize them) so you know where to go to fix whatever.


> 3) I clicked on the field box on the form design and typed what you
> said, but when I went to view it, it said #Name?

I have no idea what property you attempted to update.

> 4) I must therefore not be finding a think called control source on my
> form design. I have the field there on the screen, and I want it to
> automatically be the sum of these other 9 fields.

Solution is in point 2.
>
> This has got to be simple, but I just don't know where to click to
> type in the code. Thank you very much for your help.

Hang out in this newsgroup, read the posts, and in a while you will be
providing solutions to others.
>
> Sincerely,
>
> Michael
>
>
>
>
> On Jan 11, 3:35 pm, Salad wrote:
>
>>mlwerth wrote:
>>
>>>Thank you everyone!
>>
>>>Hi Larry:
>>
>>>I finally found that behind the form that I was designing was a screen
>>>that allowed me to switch to table design, and then I changed the
>>>field types from text to number no problem.
>>
>>>Now, about the query! I've got 9 number fields that I want to total,
>>>and have it show up on my form. So, I'm in the form design view, and
>>>my form is looking beautiful. I have a field on the form that I want
>>>to be the total of the 9 fields, but I was told that one field cannot
>>>be based on a calculation from other fields.
>>
>>Someone must be pulling your leg then.
>>
>>
>>>How do I get a box to appear on the screen where I can put a formula
>>>into it to calculate the sum of these other 9 fields? What menu
>>>commands do I use, or can I use the mouse to drag a "query box" of
>>>some kind onto the screen where I can program it? I really know
>>>nothing about how to make a query appear on my form desgin. Thank you
>>>again and talk to you soon.
>>
>>In your query you can create a calculated field. Let's say you want to
>>add fld1...fld3. You could enter in the querybuilder
>> TotalOfFields : [fld1] + [fld2] + [fld3]
>>
>>There's the chance you want to create the calculated field on the form
>>instead of the query. In the control source of the field (TotalFlds)
>>you could enter something like
>> =[fld1] + [fld2] + [fld3]
>>
>>There's a caveat. What happens if you have Null values in the values to
>>be summed. Let's pretend fld2 is blank.
>> fld1 = 1
>> fld2 = Null
>> fld3 = 2
>> ? fld1 + fld2 + fld3
>> Null
>>That's not right. Right? You want to see a 3 as the result.
>>
>>You need to do 1 of 2 things. NZ() the fields or change the table slightly.
>>
>>Using NZ, you can force a value of zero if null. Ex:
>> =NZ([fld1],0) + NZ([fld2],0) + NZ([fld3],0)
>>
>>If you do have null values (open the table and scan thru it looking for
>>blanks in the numeric fields). If some fields are blank, you can fix
>>the table it so all records will have zeros in the fields.
>>
>>Open the table and ensure the DefaultValue is 0 for each fld to be
>>summed (If the DefaultValue says Null or is blank, enter a zero).
>>
>>Next, update all fields used in your summing and set the value to 0 if
>>null. Ex:
>> UPDATE Table1 SET Table1.Fld1 = 0
>> WHERE (((Table1.Fld1) Is Null));
>>
>
>
>
>>Run this query for each field to be summed...in your case 9 times. Once
>>all null values have been updated to zero all new records will default
>>to zero for those fields. You won't need to NZ() them.
>>
>>
>>
>>
>>
>>
>>>Sincerely,
>>
>>>Michael- Hide quoted text -
>>
>>- Show quoted text -
>
>

Re: can"t find where to change data type

Hello Salad:

Thank you for all your help.

Your tip of adding the fresh blank text box + the earlier tip of
forcing the zeros for null fields led to the break through for me. I
thank you for your help and everyone else's as well. I could not get
a sum with everything that I tried until we forced the zeros for the
blank fields. "=NZ([fld1],0) + NZ([fld2],0) + NZ([fld3],0) " - Salad

Thank you all again, and I hope to benefit and participate from this
site in the future.

Sincerely,

Michael

Re: can"t find where to change data type

mlwerth wrote:
> Hello Salad:
>
> Thank you for all your help.
>
> Your tip of adding the fresh blank text box + the earlier tip of
> forcing the zeros for null fields led to the break through for me. I
> thank you for your help and everyone else's as well. I could not get
> a sum with everything that I tried until we forced the zeros for the
> blank fields. "=NZ([fld1],0) + NZ([fld2],0) + NZ([fld3],0) " - Salad
>
> Thank you all again, and I hope to benefit and participate from this
> site in the future.
>
> Sincerely,
>
> Michael
>
>
Glad you got things working.

Hip Hip Hurray!
http://www.youtube.com/watch?v=bXk5PWs0EEs