I am trying to use calculated fields in my query but all the results
are zero ven when they're not. Please help. Thanks.
5  4038 
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********@hot mail.com> wrote in message
news:ae******** *************** ***@posting.goo gle.com... I am trying to use calculated fields in my query but all the results
are zero ven when they're not. Please help. Thanks.
Thank you very much for your reply.I had a database query of 5 fields,
name,address,ag e , 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*********@Se eSig.Invalid> wrote in message news:<40******* *************** *@freenews.iine t.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********@hot mail.com> wrote in message
news:ae******** *************** ***@posting.goo gle.com... I am trying to use calculated fields in my query but all the results
are zero ven when they're not. Please help. Thanks.
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********@hot mail.com> wrote in message
news:ae******** *************** ***@posting.goo gle.com... Thank you very much for your reply.I had a database query of 5 fields,
name,address,ag e , 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*********@Se eSig.Invalid> wrote in message
news:<40******* *************** *@freenews.iine t.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********@hot mail.com> wrote in message
news:ae******** *************** ***@posting.goo gle.com... I am trying to use calculated fields in my query but all the results
are zero ven when they're not. Please help. Thanks.
You understood it perfectly. The zeros are right-aligned. Thank you.
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message news:<40******* *************** *@freenews.iine t.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********@hot mail.com> wrote in message
news:ae******** *************** ***@posting.goo gle.com... Thank you very much for your reply.I had a database query of 5 fields,
name,address,ag e , 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*********@Se eSig.Invalid> wrote in message
news:<40******* *************** *@freenews.iine t.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********@hot mail.com> wrote in message
news:ae******** *************** ***@posting.goo gle.com...
> I am trying to use calculated fields in my query but all the results
> are zero ven when they're not. Please help. Thanks.
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\system3 2)
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********@hot mail.com> wrote in message
news:ae******** *************** **@posting.goog le.com... You understood it perfectly. The zeros are right-aligned. Thank you.
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:<40******* *************** *@freenews.iine t.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********@hot mail.com> wrote in message
news:ae******** *************** ***@posting.goo gle.com... Thank you very much for your reply.I had a database query of 5 fields,
name,address,ag e , 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*********@Se eSig.Invalid> wrote in message
news:<40******* *************** *@freenews.iine t.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********@hot mail.com> wrote in message
> news:ae******** *************** ***@posting.goo gle.com...
> > I am trying to use calculated fields in my query but all the
results > > are zero ven when they're not. Please help. Thanks.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Anonymous |
last post by:
Hi all,
I have a form people use to enter checking data. One of the fields is
calculated based on finding the difference of two input fields on the form.
Here are the fields:
CheckAmount
AmountApplied
Funds Remaining
|
by: Miguelito Bain |
last post by:
hi everybody.
i'm trying to find out what you can and can't do with/in calculated
fields. any help, pointers, and/or advice would be greatly
appreciated. i'm a newbie, but i want to learn, and i've tried a lot
before posting.
here's my situation.
i have a simple form with dates on it. i have an indate and outdate
|
by: david |
last post by:
Hi,
I have a form with a couple of calculated fields.
I want to put some code into the 'Form-Load' event to set various
object states on the form, depending on the value of these fields.
The problem is that the calculated fields are blank for a split second
when the form opens, and then the value appears. This makes any tests
fail. How...
|
by: Ryker |
last post by:
I have a Purchase Order where I have a calculated field called Price
that is calculated by multiplying Qty * Sales Price. I have 10 of
these fields...Price 1 - Price 10 (for each line of the PO). The price
fields are summed into another calculated field of Total Price (for the
whole purchase order). My problem is that on the purchase order...
|
by: Olveres |
last post by:
Hi,
I have managed to work out how to add new lines into a calculated text
box. However in this text box some of the outcome fields are empty
however when previewing the report it includes the blank fields, so
each section of the report is the same size, my field is set to can
grow/shrink, but I think my inclusion in the code for the...
| | |
by: Henrik |
last post by:
The problem is (using MS Access 2003) I am unable to retrieve long
strings (255 chars) from calculated fields through a recordset.
The data takes the trip in three phases:
1. A custom public function returns a long string. This works.
2. A query has a calculated field based on the custom function above.
This works when the query is run...
|
by: jcf378 |
last post by:
hi all.
I have a form which contains a calculated control ("days") that outputs the # of days between two dates (DateDiff command between the fields and ).
However, when I click "Filter by Form" in order to search for records based on this form, I would like to be able to enter a value in this "days" control so that I can filter records on...
|
by: Matthew Wells |
last post by:
If I have a calculated field that returns a value from a udf, does that
field get calculated even when it's not being used in a Select statement? I
knw that if a calculated field's formula is based on fields in the same
record that the calculation is only performed when one of the two fields is
updated. But if I'm calling a UDF, there's no...
|
by: mkbrady |
last post by:
I have a query that includes calculated fields that generate numeric results.
I have wrapped conversion functions CLng() and CDdl() around the calculated
fields to ensure the data types are correct. The query results are correct
in MS Access 2007 but when I export to MS Excel 2007, some of the numeric
fields are converted to text. I...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
