Query field question

[ryan.fitzpatrick3]

I have two query fields one which divides into the other. i.e. fieldA
divided by fieldB = fieldC, easy enough, except that instead of
getting an answer such as (fieldA) 2752/ (fieldB) 2800 = (fieldC) .
9828 it gives me .90 it rounds
it. I went into format field to standard and I added decimal places
but the answer in fieldC still reflects .90 instead of .9828. Any
thoughts?

Ryan

 

[Graham Mandeno]

Hi Ryan

Firstly, if FieldC will *always* be FieldA/FieldB, then you should not store
it in your table. If you do, then you will have the additional hassle,
every time you change the value in FieldA or FieldB, of needing to
recalculate and store a new value for FieldC.

Instead, create a query which includes all the fields from you table that
are required for your form/report and add a calculated field:
FieldC: [FieldA] / [FieldB]

If it's possible that FieldB is null or zero, then use this:
FieldC: IIf( [FieldB]>0, [FieldA] / [FieldB], Null )

The decimal places problem will definitely be due to formatting. If you
always want 4dp, then try setting the format property of your textbox to
0.0000.

 

[ryan.fitzpatrick3]

Hi Graham,

I do not have any tables just a query. It's one query which pulls
table information, normally when I do this the calculation works fine,
but this time its not.

Another quick question since it's related. I have one query field that
has item numbers if I do not want item numbers

000011, 000012, 000013 do I put not "000011" or "000012" or "000013"
in the criteria line?

Hi Ryan

Firstly, if FieldC will *always* be FieldA/FieldB, then you should not store
it in your table. If you do, then you will have the additional hassle,
every time you change the value in FieldA or FieldB, of needing to
recalculate and store a new value for FieldC.

Instead, create a query which includes all the fields from you table that
are required for your form/report and add a calculated field:
FieldC: [FieldA] / [FieldB]

If it's possible that FieldB is null or zero, then use this:
FieldC: IIf( [FieldB]>0, [FieldA] / [FieldB], Null )

The decimal places problem will definitely be due to formatting. If you
always want 4dp, then try setting the format property of your textbox to
0.0000.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have two query fields one which divides into the other. i.e. fieldA
divided by fieldB = fieldC, easy enough, except that instead of
getting an answer such as (fieldA) 2752/ (fieldB) 2800 = (fieldC) .
9828 it gives me .90 it rounds
it. I went into format field to standard and I added decimal places
but the answer in fieldC still reflects .90 instead of .9828. Any
thoughts?

Ryan

 

[alemanp]

Hi Graham,

I do not have any tables just a query. It's one query which pulls
table information, normally when I do this the calculation works fine,
but this time its not.

Another quick question since it's related. I have one query field that
has item numbers if I do not want item numbers

000011, 000012, 000013 do I put not "000011" or "000012" or "000013"
in the criteria line?

Hi Ryan

Firstly, if FieldC will *always* be FieldA/FieldB, then you should not store
it in your table.  If you do, then you will have the additional hassle,
every time you change the value in FieldA or FieldB, of needing to
recalculate and store a new value for FieldC.

Instead, create a query which includes all the fields from you table that
are required for your form/report and add a calculated field:
    FieldC: [FieldA] / [FieldB]

If it's possible that FieldB is null or zero, then use this:
    FieldC: IIf( [FieldB]>0, [FieldA] / [FieldB], Null )

The decimal places problem will definitely be due to formatting.  If you
always want 4dp, then try setting the format property of your textbox to
0.0000.

Graham Mandeno [Access MVP]
Auckland, New Zealand

- Show quoted text -

Ryan,
For your first problem. Try using the Format function in your query.
SELECT tblAmount.Amount1, tblAmount.Amount2, Format(([Amount1]/
[Amount2]),"#.####") AS Percent1
FROM tblAmount;


The second problem can be solved by stating:
Select ItemNo, Name, Price from tblProduct Where ItemNo Not In
("000011", "000012", "000013");

If the ItemNo is numeric, then no quotes.

HTH

Pete Aleman
Vancouver WA

 

[Graham Mandeno]

Hi Ryan

Sorry, I missed the fact that it was a query field. Lecture on
normalization unnecessary

So did the format I suggested fix the decimal places?

Is your item number field numeric or text? If it is numeric you don't need
the quotes. If it is text, then the text must be an exact match - i.e.
"000011" is not the same as "00011".

Either way, the NOT should apply to the whole criteria expression, so you
need brackets.

For text:
not ("000011" or "000012" or "000013")

For numeric:
not (11 or 12 or 13)

If the list is long, you can also say:
not in (11, 12, 13, ...)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I do not have any tables just a query. It's one query which pulls
table information, normally when I do this the calculation works fine,
but this time its not.

Another quick question since it's related. I have one query field that
has item numbers if I do not want item numbers

000011, 000012, 000013 do I put not "000011" or "000012" or "000013"
in the criteria line?

Hi Ryan

Firstly, if FieldC will *always* be FieldA/FieldB, then you should not
store
it in your table. If you do, then you will have the additional hassle,
every time you change the value in FieldA or FieldB, of needing to
recalculate and store a new value for FieldC.

Instead, create a query which includes all the fields from you table that
are required for your form/report and add a calculated field:
FieldC: [FieldA] / [FieldB]

If it's possible that FieldB is null or zero, then use this:
FieldC: IIf( [FieldB]>0, [FieldA] / [FieldB], Null )

The decimal places problem will definitely be due to formatting. If you
always want 4dp, then try setting the format property of your textbox to
0.0000.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have two query fields one which divides into the other. i.e. fieldA
divided by fieldB = fieldC, easy enough, except that instead of
getting an answer such as (fieldA) 2752/ (fieldB) 2800 = (fieldC) .
9828 it gives me .90 it rounds
it. I went into format field to standard and I added decimal places
but the answer in fieldC still reflects .90 instead of .9828. Any
thoughts?

Ryan

 

[ryan.fitzpatrick3]

Got it too work, thanks.

Hi Ryan

Firstly, if FieldC will *always* be FieldA/FieldB, then you should not store
it in your table. If you do, then you will have the additional hassle,
every time you change the value in FieldA or FieldB, of needing to
recalculate and store a new value for FieldC.

Instead, create a query which includes all the fields from you table that
are required for your form/report and add a calculated field:
FieldC: [FieldA] / [FieldB]

If it's possible that FieldB is null or zero, then use this:
FieldC: IIf( [FieldB]>0, [FieldA] / [FieldB], Null )

The decimal places problem will definitely be due to formatting. If you
always want 4dp, then try setting the format property of your textbox to
0.0000.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have two query fields one which divides into the other. i.e. fieldA
divided by fieldB = fieldC, easy enough, except that instead of
getting an answer such as (fieldA) 2752/ (fieldB) 2800 = (fieldC) .
9828 it gives me .90 it rounds
it. I went into format field to standard and I added decimal places
but the answer in fieldC still reflects .90 instead of .9828. Any
thoughts?

Ryan