Calc values from a form viewed back in a query table

  • Thread starter Thread starter Cindy
  • Start date Start date
C

Cindy

SELECT First([Cindy Pump Calc Query].Flow) AS Flow, First
([Cindy Pump Calc Query].Temperature) AS Temperature, First
([Cindy Pump Calc Query].Consistency) AS Consistency, Sum
([Cindy Pump Calc Query].[Section Friction Loss]) AS
[Total Dynamic Head]
FROM [Cindy Pump Calc Query];

The [Section Friction Loss] comes up empty because in my
Form!Cindy Pump Calc! which feeds Cindy Pump Calc Query,
it doesn't enter this number.

Section Friction Loss is a long formula that uses
information off my form, it is not an entered number by
the user. Only numbers that are entered in the form are
sent back to the query (table view). I would like a way
to get the calculated values, as [section friction loss]
back into my query, so that when I make a sum of them, I
can receive the answer, send it to a report and everyone's
happy.

I am so sorry I am bad at explaining, Is there something
else specific I should be asking?

Cindy
 
Hi,


In a computation, a NULL value is generally propagated:


? 1 + 0 * NULL


returns

NULL


So, in your formula, if any value is not supplied, it is assumed to be NULL
and the end result (in some cases it may not, but in some cases it does, as
shown) is NULL. To avoid that, replace the NULL with a "substitution" value
:

? 1 + 0 * Nz( myControl, 0 )


so, if myControl Is NULL, the NULL would be change, here, for a zero, and
the result is now 1, instead of the NULL. That is not done by magic, but by
the use of that nice Nz function. Generally a zero is appropriate, for the
substitution, but sometimes, for a date, it can be Now( ), and sometimes, a
very large negative value is appropriate,... all depends of you situation.



Hoping it may help,
Vanderghast, Access MVP




Cindy said:
SELECT First([Cindy Pump Calc Query].Flow) AS Flow, First
([Cindy Pump Calc Query].Temperature) AS Temperature, First
([Cindy Pump Calc Query].Consistency) AS Consistency, Sum
([Cindy Pump Calc Query].[Section Friction Loss]) AS
[Total Dynamic Head]
FROM [Cindy Pump Calc Query];

The [Section Friction Loss] comes up empty because in my
Form!Cindy Pump Calc! which feeds Cindy Pump Calc Query,
it doesn't enter this number.

Section Friction Loss is a long formula that uses
information off my form, it is not an entered number by
the user. Only numbers that are entered in the form are
sent back to the query (table view). I would like a way
to get the calculated values, as [section friction loss]
back into my query, so that when I make a sum of them, I
can receive the answer, send it to a report and everyone's
happy.

I am so sorry I am bad at explaining, Is there something
else specific I should be asking?

Cindy
-----Original Message-----
Your question is a little too vague. Give more detail, along with whatever
it is that you're referencing in the first paragraph. Post your SQL
statement if possible.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Click to expand...
Click to expand...
 
Hi,
sorry but I have no idea what you are saying. I want the
value that is calculated back somewhere else where I can
add them up.
My form is pages long, each section gets answers for
calculated values. I would like this value to come back -
so I can add them up.
Getting up a +1 instead of a null won't do it.
I am really really confused by your answer.

Thanks though,
Cindy
-----Original Message-----
Hi,


In a computation, a NULL value is generally propagated:


? 1 + 0 * NULL


returns

NULL


So, in your formula, if any value is not supplied, it is assumed to be NULL
and the end result (in some cases it may not, but in some cases it does, as
shown) is NULL. To avoid that, replace the NULL with a "substitution" value
:

? 1 + 0 * Nz( myControl, 0 )


so, if myControl Is NULL, the NULL would be change, here, for a zero, and
the result is now 1, instead of the NULL. That is not done by magic, but by
the use of that nice Nz function. Generally a zero is appropriate, for the
substitution, but sometimes, for a date, it can be Now ( ), and sometimes, a
very large negative value is appropriate,... all depends of you situation.



Hoping it may help,
Vanderghast, Access MVP




Cindy said:
SELECT First([Cindy Pump Calc Query].Flow) AS Flow, First
([Cindy Pump Calc Query].Temperature) AS Temperature, First
([Cindy Pump Calc Query].Consistency) AS Consistency, Sum
([Cindy Pump Calc Query].[Section Friction Loss]) AS
[Total Dynamic Head]
FROM [Cindy Pump Calc Query];

The [Section Friction Loss] comes up empty because in my
Form!Cindy Pump Calc! which feeds Cindy Pump Calc Query,
it doesn't enter this number.

Section Friction Loss is a long formula that uses
information off my form, it is not an entered number by
the user. Only numbers that are entered in the form are
sent back to the query (table view). I would like a way
to get the calculated values, as [section friction loss]
back into my query, so that when I make a sum of them, I
can receive the answer, send it to a report and everyone's
happy.

I am so sorry I am bad at explaining, Is there something
else specific I should be asking?

Cindy
-----Original Message-----
Your question is a little too vague. Give more detail, along with whatever
it is that you're referencing in the first paragraph. Post your SQL
statement if possible.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,
In my query, I have no problems finding information that
isn't calculated. This information automatically gets
entered on the query that is used to create the form.

I am trying to do a new summary query using the first
query. The only information that is entered is the ones
that were entered data and not that had formulas. Is
there a way to find these calculated data from the form -
or from the last query?

Much appreciated for help :)

Cindy
Click to expand...
Click to expand...


.
Click to expand...
 
p.s. I do not get null values in the form, my form is
great, I want my query to receive these values from my
form so I can see them in a table, or somewhere else but
my form. The values get calculated and are right.
Thanks!
Cindy
-----Original Message-----
Hi,


In a computation, a NULL value is generally propagated:


? 1 + 0 * NULL


returns

NULL


So, in your formula, if any value is not supplied, it is assumed to be NULL
and the end result (in some cases it may not, but in some cases it does, as
shown) is NULL. To avoid that, replace the NULL with a "substitution" value
:

? 1 + 0 * Nz( myControl, 0 )


so, if myControl Is NULL, the NULL would be change, here, for a zero, and
the result is now 1, instead of the NULL. That is not done by magic, but by
the use of that nice Nz function. Generally a zero is appropriate, for the
substitution, but sometimes, for a date, it can be Now ( ), and sometimes, a
very large negative value is appropriate,... all depends of you situation.



Hoping it may help,
Vanderghast, Access MVP




Cindy said:
SELECT First([Cindy Pump Calc Query].Flow) AS Flow, First
([Cindy Pump Calc Query].Temperature) AS Temperature, First
([Cindy Pump Calc Query].Consistency) AS Consistency, Sum
([Cindy Pump Calc Query].[Section Friction Loss]) AS
[Total Dynamic Head]
FROM [Cindy Pump Calc Query];

The [Section Friction Loss] comes up empty because in my
Form!Cindy Pump Calc! which feeds Cindy Pump Calc Query,
it doesn't enter this number.

Section Friction Loss is a long formula that uses
information off my form, it is not an entered number by
the user. Only numbers that are entered in the form are
sent back to the query (table view). I would like a way
to get the calculated values, as [section friction loss]
back into my query, so that when I make a sum of them, I
can receive the answer, send it to a report and everyone's
happy.

I am so sorry I am bad at explaining, Is there something
else specific I should be asking?

Cindy
-----Original Message-----
Your question is a little too vague. Give more detail, along with whatever
it is that you're referencing in the first paragraph. Post your SQL
statement if possible.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,
In my query, I have no problems finding information that
isn't calculated. This information automatically gets
entered on the query that is used to create the form.

I am trying to do a new summary query using the first
query. The only information that is entered is the ones
that were entered data and not that had formulas. Is
there a way to find these calculated data from the form -
or from the last query?

Much appreciated for help :)

Cindy
Click to expand...
Click to expand...


.
Click to expand...
 
Ok, what I understand is that you have a query that has, for
illustration, three fields, f1, f2 and f3. Your form, tied to the query,
display f1, f2 and f3, but also makes some computation to display f4. You
want the computed value f4 back in the query or in a table. Right?

I would then suggest to make the computation in the query rather than in
the form. The query would then supply the value for f4, and the form would
have only to display it as usual. Being a computed value, there is no need
to store it in a table, and use the query, which now computes f4, when and
every where you need f4.


Vanderghast, Access MVP


Cindy said:
p.s. I do not get null values in the form, my form is
great, I want my query to receive these values from my
form so I can see them in a table, or somewhere else but
my form. The values get calculated and are right.
Thanks!
Cindy
-----Original Message-----
Hi,


In a computation, a NULL value is generally propagated:


? 1 + 0 * NULL


returns

NULL


So, in your formula, if any value is not supplied, it is assumed to be NULL
and the end result (in some cases it may not, but in some cases it does, as
shown) is NULL. To avoid that, replace the NULL with a "substitution" value
:

? 1 + 0 * Nz( myControl, 0 )


so, if myControl Is NULL, the NULL would be change, here, for a zero, and
the result is now 1, instead of the NULL. That is not done by magic, but by
the use of that nice Nz function. Generally a zero is appropriate, for the
substitution, but sometimes, for a date, it can be Now ( ), and sometimes, a
very large negative value is appropriate,... all depends of you situation.



Hoping it may help,
Vanderghast, Access MVP




Cindy said:
SELECT First([Cindy Pump Calc Query].Flow) AS Flow, First
([Cindy Pump Calc Query].Temperature) AS Temperature, First
([Cindy Pump Calc Query].Consistency) AS Consistency, Sum
([Cindy Pump Calc Query].[Section Friction Loss]) AS
[Total Dynamic Head]
FROM [Cindy Pump Calc Query];

The [Section Friction Loss] comes up empty because in my
Form!Cindy Pump Calc! which feeds Cindy Pump Calc Query,
it doesn't enter this number.

Section Friction Loss is a long formula that uses
information off my form, it is not an entered number by
the user. Only numbers that are entered in the form are
sent back to the query (table view). I would like a way
to get the calculated values, as [section friction loss]
back into my query, so that when I make a sum of them, I
can receive the answer, send it to a report and everyone's
happy.

I am so sorry I am bad at explaining, Is there something
else specific I should be asking?

Cindy
-----Original Message-----
Your question is a little too vague. Give more detail,
along with whatever
it is that you're referencing in the first paragraph.
Post your SQL
statement if possible.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,
In my query, I have no problems finding information that
isn't calculated. This information automatically gets
entered on the query that is used to create the form.

I am trying to do a new summary query using the first
query. The only information that is entered is the ones
that were entered data and not that had formulas. Is
there a way to find these calculated data from the
form -
or from the last query?

Much appreciated for help :)

Cindy
Click to expand...


.
Click to expand...
Click to expand...
 
I ended up doing that already - thanks!
Cindy
-----Original Message-----


Ok, what I understand is that you have a query that has, for
illustration, three fields, f1, f2 and f3. Your form, tied to the query,
display f1, f2 and f3, but also makes some computation to display f4. You
want the computed value f4 back in the query or in a table. Right?

I would then suggest to make the computation in the query rather than in
the form. The query would then supply the value for f4, and the form would
have only to display it as usual. Being a computed value, there is no need
to store it in a table, and use the query, which now computes f4, when and
every where you need f4.


Vanderghast, Access MVP


p.s. I do not get null values in the form, my form is
great, I want my query to receive these values from my
form so I can see them in a table, or somewhere else but
my form. The values get calculated and are right.
Thanks!
Cindy
-----Original Message-----
Hi,


In a computation, a NULL value is generally propagated:


? 1 + 0 * NULL


returns

NULL


So, in your formula, if any value is not supplied, it
Click to expand...
is
assumed to be NULL
and the end result (in some cases it may not, but in
Click to expand...
some
cases it does, as
shown) is NULL. To avoid that, replace the NULL with a "substitution" value
:

? 1 + 0 * Nz( myControl, 0 )


so, if myControl Is NULL, the NULL would be change,
Click to expand...
here,
for a zero, and
the result is now 1, instead of the NULL. That is not done by magic, but by
the use of that nice Nz function. Generally a zero is appropriate, for the
substitution, but sometimes, for a date, it can be Now ( ), and sometimes, a
very large negative value is appropriate,... all
Click to expand...
depends
of you situation.
Hoping it may help,
Vanderghast, Access MVP




SELECT First([Cindy Pump Calc Query].Flow) AS Flow, First
([Cindy Pump Calc Query].Temperature) AS Temperature, First
([Cindy Pump Calc Query].Consistency) AS Consistency, Sum
([Cindy Pump Calc Query].[Section Friction Loss]) AS
[Total Dynamic Head]
FROM [Cindy Pump Calc Query];

The [Section Friction Loss] comes up empty because in my
Form!Cindy Pump Calc! which feeds Cindy Pump Calc Query,
it doesn't enter this number.

Section Friction Loss is a long formula that uses
information off my form, it is not an entered number by
the user. Only numbers that are entered in the form are
sent back to the query (table view). I would like a way
to get the calculated values, as [section friction loss]
back into my query, so that when I make a sum of them, I
can receive the answer, send it to a report and everyone's
happy.

I am so sorry I am bad at explaining, Is there something
else specific I should be asking?

Cindy
-----Original Message-----
Your question is a little too vague. Give more detail,
along with whatever
it is that you're referencing in the first paragraph.
Post your SQL
statement if possible.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,
In my query, I have no problems finding
Click to expand...
information
that
isn't calculated. This information automatically gets
entered on the query that is used to create the form.

I am trying to do a new summary query using the first
query. The only information that is entered is
Click to expand...
the
ones
that were entered data and not that had formulas. Is
there a way to find these calculated data from the
form -
or from the last query?

Much appreciated for help :)

Cindy



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top