default values in queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to have Access enter a default value in queries that have null
values. I've tried NZ but when running the query it asks me for a value.
I'd like to not have to hit return repeatedly when running the query.

Any suggestions?
 
You'll need to give us more info than this... let's start with the SQL
statement of the query that you want to run and identify which
field/parameter is the one in question.
 
The SQL statement is below. Everytime I run it I get a bunch of "Enter
Parameter Value" requests for all the fields that contain null values, even
the ones that I've used NZ on. I'd like to not have to enter "Enter" each
time.

SELECT [qry:Summary Blend Acct].Account, [qry:Summary Blend Acct].[Total
Value] AS Total_Value, Sum(nz([Current_Value],0)) AS Value_of_ETFs,
[qry:Blend Values Fixed].SumOfValue AS Value_of_Bonds, [qry:Blend Values
RYJU/AX/SHY].SumOfValue AS Value_of_Bond_Funds,
nz([Value_of_Bonds],0)+nz([Value_of_Bond_Funds],0) AS Total_Fixed_Income,
[qry:Summary Blend Acct].[Amount Investible] AS Cash_Available,
nz([Value_of_ETFs],0)+nz([Total_Fixed_Income],0)+[Cash_Available] AS
Managed_Funds, [Total_Value]-[Managed_Funds] AS NonManaged_Funds
FROM (([qry:Summary Blend Acct] LEFT JOIN [qry:Blend Values Fixed] ON
[qry:Summary Blend Acct].Account = [qry:Blend Values Fixed].Account) LEFT
JOIN [qry:Blend Values ETF] ON [qry:Summary Blend Acct].Account = [qry:Blend
Values ETF].Account) LEFT JOIN [qry:Blend Values RYJU/AX/SHY] ON [qry:Summary
Blend Acct].Account = [qry:Blend Values RYJU/AX/SHY].Account
GROUP BY [qry:Summary Blend Acct].Account, [qry:Summary Blend Acct].[Total
Value], [qry:Blend Values Fixed].SumOfValue, [qry:Blend Values
RYJU/AX/SHY].SumOfValue, nz([Value_of_Bonds],0)+nz([Value_of_Bond_Funds],0),
[qry:Summary Blend Acct].[Amount Investible],
nz([Value_of_ETFs],0)+nz([Total_Fixed_Income],0)+[Cash_Available],
[Total_Value]-[Managed_Funds];
 
Let me take one parameter as an example. I assume Current Value is one of
these parameters? And I assume that this value is to be solicited from the
user when the query runs? If not, what is the source of Current Value?

If Current Value is not a field in the qry:Summary Blend Acct, qry:Blend
Values Fixed, qry:Blend Values ETF, or qry:Blend Values RYJU/AX/SHY queries,
the Jet will ask the user for the value. Only after the user enters a value
(or clicks OK without entering a value) can Jet then determine if there is a
value or not. And note that a parameter usually returns an empty string, not
Null, if the user clicks OK without entering a value; in which case, Nz is
not going to be useful.


--

Ken Snell
<MS ACCESS MVP>


Jeff B said:
The SQL statement is below. Everytime I run it I get a bunch of "Enter
Parameter Value" requests for all the fields that contain null values,
even
the ones that I've used NZ on. I'd like to not have to enter "Enter" each
time.

SELECT [qry:Summary Blend Acct].Account, [qry:Summary Blend Acct].[Total
Value] AS Total_Value, Sum(nz([Current_Value],0)) AS Value_of_ETFs,
[qry:Blend Values Fixed].SumOfValue AS Value_of_Bonds, [qry:Blend Values
RYJU/AX/SHY].SumOfValue AS Value_of_Bond_Funds,
nz([Value_of_Bonds],0)+nz([Value_of_Bond_Funds],0) AS Total_Fixed_Income,
[qry:Summary Blend Acct].[Amount Investible] AS Cash_Available,
nz([Value_of_ETFs],0)+nz([Total_Fixed_Income],0)+[Cash_Available] AS
Managed_Funds, [Total_Value]-[Managed_Funds] AS NonManaged_Funds
FROM (([qry:Summary Blend Acct] LEFT JOIN [qry:Blend Values Fixed] ON
[qry:Summary Blend Acct].Account = [qry:Blend Values Fixed].Account) LEFT
JOIN [qry:Blend Values ETF] ON [qry:Summary Blend Acct].Account =
[qry:Blend
Values ETF].Account) LEFT JOIN [qry:Blend Values RYJU/AX/SHY] ON
[qry:Summary
Blend Acct].Account = [qry:Blend Values RYJU/AX/SHY].Account
GROUP BY [qry:Summary Blend Acct].Account, [qry:Summary Blend Acct].[Total
Value], [qry:Blend Values Fixed].SumOfValue, [qry:Blend Values
RYJU/AX/SHY].SumOfValue,
nz([Value_of_Bonds],0)+nz([Value_of_Bond_Funds],0),
[qry:Summary Blend Acct].[Amount Investible],
nz([Value_of_ETFs],0)+nz([Total_Fixed_Income],0)+[Cash_Available],
[Total_Value]-[Managed_Funds];


Ken Snell said:
You'll need to give us more info than this... let's start with the SQL
statement of the query that you want to run and identify which
field/parameter is the one in question.
Click to expand...
Click to expand...
 
Sorry about the confusion. I'm new to this....

Let me use a better example - Value_of_Bonds. This comes from qry:Blend
Values Fixed. Not every account has an row in qry:Blend Values Fixed for Bond
Values. Those blank rows come over as a blank/null in the query I'm
concerned with. When I run this query I get prompted for a value for for the
blank/null values. I hit return and the blank/null cells are then blank. All
other cells in the row are filled in with the appropriate values from
qry:Blend Values Fixed.

I'd like blank/null values to be automatically filled with a default value -
zero - without prompting me for input. This would be similar to using a
default value in a table but I can't find a way to do so in a query.

In addition, I can't link this query to an excel spreadsheet. I guess it's
because of the blank/null cells.

Ken Snell said:
Let me take one parameter as an example. I assume Current Value is one of
these parameters? And I assume that this value is to be solicited from the
user when the query runs? If not, what is the source of Current Value?

If Current Value is not a field in the qry:Summary Blend Acct, qry:Blend
Values Fixed, qry:Blend Values ETF, or qry:Blend Values RYJU/AX/SHY queries,
the Jet will ask the user for the value. Only after the user enters a value
(or clicks OK without entering a value) can Jet then determine if there is a
value or not. And note that a parameter usually returns an empty string, not
Null, if the user clicks OK without entering a value; in which case, Nz is
not going to be useful.


--

Ken Snell
<MS ACCESS MVP>


Jeff B said:
The SQL statement is below. Everytime I run it I get a bunch of "Enter
Parameter Value" requests for all the fields that contain null values,
even
the ones that I've used NZ on. I'd like to not have to enter "Enter" each
time.

SELECT [qry:Summary Blend Acct].Account, [qry:Summary Blend Acct].[Total
Value] AS Total_Value, Sum(nz([Current_Value],0)) AS Value_of_ETFs,
[qry:Blend Values Fixed].SumOfValue AS Value_of_Bonds, [qry:Blend Values
RYJU/AX/SHY].SumOfValue AS Value_of_Bond_Funds,
nz([Value_of_Bonds],0)+nz([Value_of_Bond_Funds],0) AS Total_Fixed_Income,
[qry:Summary Blend Acct].[Amount Investible] AS Cash_Available,
nz([Value_of_ETFs],0)+nz([Total_Fixed_Income],0)+[Cash_Available] AS
Managed_Funds, [Total_Value]-[Managed_Funds] AS NonManaged_Funds
FROM (([qry:Summary Blend Acct] LEFT JOIN [qry:Blend Values Fixed] ON
[qry:Summary Blend Acct].Account = [qry:Blend Values Fixed].Account) LEFT
JOIN [qry:Blend Values ETF] ON [qry:Summary Blend Acct].Account =
[qry:Blend
Values ETF].Account) LEFT JOIN [qry:Blend Values RYJU/AX/SHY] ON
[qry:Summary
Blend Acct].Account = [qry:Blend Values RYJU/AX/SHY].Account
GROUP BY [qry:Summary Blend Acct].Account, [qry:Summary Blend Acct].[Total
Value], [qry:Blend Values Fixed].SumOfValue, [qry:Blend Values
RYJU/AX/SHY].SumOfValue,
nz([Value_of_Bonds],0)+nz([Value_of_Bond_Funds],0),
[qry:Summary Blend Acct].[Amount Investible],
nz([Value_of_ETFs],0)+nz([Total_Fixed_Income],0)+[Cash_Available],
[Total_Value]-[Managed_Funds];


Ken Snell said:
You'll need to give us more info than this... let's start with the SQL
statement of the query that you want to run and identify which
field/parameter is the one in question.

--

Ken Snell
<MS ACCESS MVP>

I'm trying to have Access enter a default value in queries that have
null
values. I've tried NZ but when running the query it asks me for a
value.
I'd like to not have to hit return repeatedly when running the query.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
The fact that you're being prompted for a field that is in the underlying
query suggests that you haven't spelled the field name exactly the same in
the query as it is in the underlying table/query. Check the spelling of the
names very carefully.

What you want should occur with your setup -- that is, wrapping Nz around a
field name to replace a Null with a value -- if the field names are spelled
correctly.


--

Ken Snell
<MS ACCESS MVP>

Jeff B said:
Sorry about the confusion. I'm new to this....

Let me use a better example - Value_of_Bonds. This comes from qry:Blend
Values Fixed. Not every account has an row in qry:Blend Values Fixed for
Bond
Values. Those blank rows come over as a blank/null in the query I'm
concerned with. When I run this query I get prompted for a value for for
the
blank/null values. I hit return and the blank/null cells are then blank.
All
other cells in the row are filled in with the appropriate values from
qry:Blend Values Fixed.

I'd like blank/null values to be automatically filled with a default
value -
zero - without prompting me for input. This would be similar to using a
default value in a table but I can't find a way to do so in a query.

In addition, I can't link this query to an excel spreadsheet. I guess it's
because of the blank/null cells.

Ken Snell said:
Let me take one parameter as an example. I assume Current Value is one of
these parameters? And I assume that this value is to be solicited from
the
user when the query runs? If not, what is the source of Current Value?

If Current Value is not a field in the qry:Summary Blend Acct, qry:Blend
Values Fixed, qry:Blend Values ETF, or qry:Blend Values RYJU/AX/SHY
queries,
the Jet will ask the user for the value. Only after the user enters a
value
(or clicks OK without entering a value) can Jet then determine if there
is a
value or not. And note that a parameter usually returns an empty string,
not
Null, if the user clicks OK without entering a value; in which case, Nz
is
not going to be useful.


--

Ken Snell
<MS ACCESS MVP>


Jeff B said:
The SQL statement is below. Everytime I run it I get a bunch of "Enter
Parameter Value" requests for all the fields that contain null values,
even
the ones that I've used NZ on. I'd like to not have to enter "Enter"
each
time.

SELECT [qry:Summary Blend Acct].Account, [qry:Summary Blend
Acct].[Total
Value] AS Total_Value, Sum(nz([Current_Value],0)) AS Value_of_ETFs,
[qry:Blend Values Fixed].SumOfValue AS Value_of_Bonds, [qry:Blend
Values
RYJU/AX/SHY].SumOfValue AS Value_of_Bond_Funds,
nz([Value_of_Bonds],0)+nz([Value_of_Bond_Funds],0) AS
Total_Fixed_Income,
[qry:Summary Blend Acct].[Amount Investible] AS Cash_Available,
nz([Value_of_ETFs],0)+nz([Total_Fixed_Income],0)+[Cash_Available] AS
Managed_Funds, [Total_Value]-[Managed_Funds] AS NonManaged_Funds
FROM (([qry:Summary Blend Acct] LEFT JOIN [qry:Blend Values Fixed] ON
[qry:Summary Blend Acct].Account = [qry:Blend Values Fixed].Account)
LEFT
JOIN [qry:Blend Values ETF] ON [qry:Summary Blend Acct].Account =
[qry:Blend
Values ETF].Account) LEFT JOIN [qry:Blend Values RYJU/AX/SHY] ON
[qry:Summary
Blend Acct].Account = [qry:Blend Values RYJU/AX/SHY].Account
GROUP BY [qry:Summary Blend Acct].Account, [qry:Summary Blend
Acct].[Total
Value], [qry:Blend Values Fixed].SumOfValue, [qry:Blend Values
RYJU/AX/SHY].SumOfValue,
nz([Value_of_Bonds],0)+nz([Value_of_Bond_Funds],0),
[qry:Summary Blend Acct].[Amount Investible],
nz([Value_of_ETFs],0)+nz([Total_Fixed_Income],0)+[Cash_Available],
[Total_Value]-[Managed_Funds];


:

You'll need to give us more info than this... let's start with the SQL
statement of the query that you want to run and identify which
field/parameter is the one in question.

--

Ken Snell
<MS ACCESS MVP>

I'm trying to have Access enter a default value in queries that
have
null
values. I've tried NZ but when running the query it asks me for a
value.
I'd like to not have to hit return repeatedly when running the
query.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
I'm prompted for all fields. I don't know how I could have mispelled the
names since I used "build" to create my query. Also, there appears to be no
mispellings when I check them manually. It seems that NZ only works properly
after I have responded to all the prompts. Any other ideas?
 
I think I've isolated the problem. I only get prompted when I use and
expression than sums fields that used NZ. I'm then prompted for the
constituent fields that were null/bland in the expression. I'm not sure that
is very clear. NZ works fine until I use the field in another expression.
 
I think I've isolated when the problem occurs. I can us NZ without any
problem until I create another field that is the sum of fields that used NZ.
I'm then prompted for a value. It's as if in a sum expression the default (0)
value is not carried over.
 
I'm sure that the problem is related to your "final" query not using the
actual names of the "precursor" queries for those fields.

Open in data sheet view the queries that are your datasources. What are the
names of the fields across the top? Do they match what you're using in the
"final" query?
 
You were right. I was using the aliases that I had assigned to the fields in
the original queries. THANKS!
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Jeff B said:
You were right. I was using the aliases that I had assigned to the fields
in
the original queries. THANKS!
Click to expand...
 
Back
Top