Re: Query field question

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


<ryan.fitzpatrick3@xxxxxxxxxxx> wrote in message
news:701128f5-2392-4905-857a-7cbe7d2f2f3c@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?




On Apr 22, 1:31 pm, "Graham Mandeno" <Graham.Mand...@xxxxxxxxxxxxx>
wrote:
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

<ryan.fitzpatri...@xxxxxxxxxxx> wrote in message

news:49cd2236-834e-46f4-b208-75821a158176@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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



.

Relevant Pages

  • Re: formatting a calculated number field on a form
    ... > A query in an MDB file CAN call a user-defined function. ... > function name is Age and it returns a double. ... The user will certainly "notice" if a patient was born on Jan ... I just want control over the *display format* of a number ...
    (microsoft.public.access.forms)
  • RE: Query count then average.
    ... What was happening in my database was the query returned the ... "Ken Sheridan" wrote: ... It probably depends where you set the Format property. ... "Robert F." ...
    (microsoft.public.access.queries)
  • RE: Cannot get code to work for API Save Dialog Box
    ... I got it working to format correctly now. ... that table in my other queries and union query. ... The code is actually working by giving me the totals I need. ... The first spreadsheet placed where I told it to be contains the incorrect ...
    (microsoft.public.access.forms)
  • RE: Excel & MS Query caused compile errors
    ... My data is in an Excel file called ... The format of it were distorted after copying and pasting. ... Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query ...
    (microsoft.public.excel.programming)
  • RE: Query count then average.
    ... It probably depends where you set the Format property. ... "Robert F." ... "Ken Sheridan" wrote: ... You can format the column in the query: ...
    (microsoft.public.access.queries)