By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Calculated fields

P: n/a
I am trying to use calculated fields in my query but all the results
are zero ven when they're not. Please help. Thanks.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
John, can you post an example of your calculated query field?

One possible cause is the use of Nulls. If the expression is made up of
various parts, you may need to wrap Nz() around each one.

Another is that Access can misunderstand the intended data type, so you may
need to explicitly typecast. Details in article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Bahran" <jo********@hotmail.com> wrote in message
news:ae**************************@posting.google.c om...
I am trying to use calculated fields in my query but all the results
are zero ven when they're not. Please help. Thanks.

Nov 12 '05 #2

P: n/a
Thank you very much for your reply.I had a database query of 5 fields,
name,address,age , multiplier and result.Age and multiplier are
numbers. They are multiplied and if >0 are put in result. But I get 0
everywhere.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...
John, can you post an example of your calculated query field?

One possible cause is the use of Nulls. If the expression is made up of
various parts, you may need to wrap Nz() around each one.

Another is that Access can misunderstand the intended data type, so you may
need to explicitly typecast. Details in article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Bahran" <jo********@hotmail.com> wrote in message
news:ae**************************@posting.google.c om...
I am trying to use calculated fields in my query but all the results
are zero ven when they're not. Please help. Thanks.

Nov 12 '05 #3

P: n/a
In query design view, in the Field row, you have numeric fields named
Multipler and Age, and then you have a calculated field that looks like
this:
Expr1: [Multiplier] * [Age]

The query shows zero on every row, no matter what values are in the
Multiplier and Age fields? If so, is the zero displayed left-aligned or
right-aligned?

Or have I misunderstood what you are saying?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Bahran" <jo********@hotmail.com> wrote in message
news:ae**************************@posting.google.c om...
Thank you very much for your reply.I had a database query of 5 fields,
name,address,age , multiplier and result.Age and multiplier are
numbers. They are multiplied and if >0 are put in result. But I get 0
everywhere.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40***********************@freenews.iinet.net .au>...
John, can you post an example of your calculated query field?

One possible cause is the use of Nulls. If the expression is made up of
various parts, you may need to wrap Nz() around each one.

Another is that Access can misunderstand the intended data type, so you may need to explicitly typecast. Details in article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

"John Bahran" <jo********@hotmail.com> wrote in message
news:ae**************************@posting.google.c om...
I am trying to use calculated fields in my query but all the results
are zero ven when they're not. Please help. Thanks.

Nov 12 '05 #4

P: n/a
You understood it perfectly. The zeros are right-aligned. Thank you.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...
In query design view, in the Field row, you have numeric fields named
Multipler and Age, and then you have a calculated field that looks like
this:
Expr1: [Multiplier] * [Age]

The query shows zero on every row, no matter what values are in the
Multiplier and Age fields? If so, is the zero displayed left-aligned or
right-aligned?

Or have I misunderstood what you are saying?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Bahran" <jo********@hotmail.com> wrote in message
news:ae**************************@posting.google.c om...
Thank you very much for your reply.I had a database query of 5 fields,
name,address,age , multiplier and result.Age and multiplier are
numbers. They are multiplied and if >0 are put in result. But I get 0
everywhere.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40***********************@freenews.iinet.net .au>...
John, can you post an example of your calculated query field?

One possible cause is the use of Nulls. If the expression is made up of
various parts, you may need to wrap Nz() around each one.

Another is that Access can misunderstand the intended data type, so you may need to explicitly typecast. Details in article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

"John Bahran" <jo********@hotmail.com> wrote in message
news:ae**************************@posting.google.c om...
> I am trying to use calculated fields in my query but all the results
> are zero ven when they're not. Please help. Thanks.

Nov 12 '05 #5

P: n/a
John, are we talking Access tables (not linked ODBC)?
If so, I am intrigued by this behavior. Email me using the address in the
sig. below, and include the text of this thread.

In table design view, what is the Field Size of the two Number fields?
Double? Long? Decimal? (Access does handle the Decimal type poorly.)

What version numbers do you have for:
- msaccess.exe (typically in \Program Files\Microsoft Office\Office)
- msjet40.dll (typically in Windows\system32)
Right-click the file in Windows Explorer, and choose Properties | Version.
(For Access 97, the 2nd file will be msjet35.dll.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Bahran" <jo********@hotmail.com> wrote in message
news:ae*************************@posting.google.co m...
You understood it perfectly. The zeros are right-aligned. Thank you.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40***********************@freenews.iinet.net .au>...
In query design view, in the Field row, you have numeric fields named
Multipler and Age, and then you have a calculated field that looks like
this:
Expr1: [Multiplier] * [Age]

The query shows zero on every row, no matter what values are in the
Multiplier and Age fields? If so, is the zero displayed left-aligned or
right-aligned?

Or have I misunderstood what you are saying?

"John Bahran" <jo********@hotmail.com> wrote in message
news:ae**************************@posting.google.c om...
Thank you very much for your reply.I had a database query of 5 fields,
name,address,age , multiplier and result.Age and multiplier are
numbers. They are multiplied and if >0 are put in result. But I get 0
everywhere.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40***********************@freenews.iinet.net .au>...
> John, can you post an example of your calculated query field?
>
> One possible cause is the use of Nulls. If the expression is made up of > various parts, you may need to wrap Nz() around each one.
>
> Another is that Access can misunderstand the intended data type, so you
may
> need to explicitly typecast. Details in article:
> Calculated fields misinterpreted
> at:
> http://allenbrowne.com/ser-45.html
>
> "John Bahran" <jo********@hotmail.com> wrote in message
> news:ae**************************@posting.google.c om...
> > I am trying to use calculated fields in my query but all the

results > > are zero ven when they're not. Please help. Thanks.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.