default values and queries

[Kate]

I have a query that has two fields in it. The first is a batch number,
and the second is the quantity of that batch issued. The second field
is sometimes empty. This is because the main form has a subform which
keeps track each time the part is issued. The second field in my query
is based on the table belonging to the subform, so that if a part has
not been issued, it won't be in the subform table, and therefore won't
appear in the second field in my query.

Currently I have this:
field: Batch QtyIssued: QtyIssued
table: Batch BatchIssue
total: GroupBy Sum

The second field appears on a separate subform on the main form, and if
it is empty, I want '0' to appear on the form (and on the query
preferably). But I'm having trouble setting this as a default. It
doesnt seem to work if I do it on the form and I'm not quite sure how
to do it from the query. Maybe i need to turn the query into an update
query, create a new table to update, and base my subform off that??

Help!

 

[Tom Wickerath]

Hi Kate,

Will using the Nz function work for you? This function can be used to
convert null to 0 (or any other number or string). You can use this in the
query or in the control source of a textbox on a form. To use in a query, try
something like this:

Qty Issued: Nz([QtyIssued],0)

Note that the field alias (the part on the left hand side of the colon) is
different from the fieldname. If you try to use the same exact name, you will
get a circular reference error.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a query that has two fields in it. The first is a batch number,
and the second is the quantity of that batch issued. The second field
is sometimes empty. This is because the main form has a subform which
keeps track each time the part is issued. The second field in my query
is based on the table belonging to the subform, so that if a part has
not been issued, it won't be in the subform table, and therefore won't
appear in the second field in my query.

Currently I have this:
field: Batch QtyIssued: QtyIssued
table: Batch BatchIssue
total: GroupBy Sum

The second field appears on a separate subform on the main form, and if
it is empty, I want '0' to appear on the form (and on the query
preferably). But I'm having trouble setting this as a default. It
doesnt seem to work if I do it on the form and I'm not quite sure how
to do it from the query. Maybe i need to turn the query into an update
query, create a new table to update, and base my subform off that??

Help!

 

[Kate]

When I opened the database today, in the places where yesterday there
was null, today the whole record is not shown!

The default value for QtyIssued in the subform is 0, but it doesn't
recognise the zero unless you type it in yourself. ie if you start a
new record and haven't issued anything yet, that record doesn't exist
in the subforms table. However, if you type '0' over the existing
default 0 in the QtyIssued field in the subform, it does appear in the
subform table! How do I make it recognize the default zero?

 

[Tom Wickerath]

Hi Kate,

You might have a corrupt database if you are certain that records with nulls
have disappeared. I'm not sure without seeing the actual database.

The default value for QtyIssued in the subform is 0, but it doesn't
recognise the zero unless you type it in yourself. ie if you start a
new record and haven't issued anything yet, that record doesn't exist
in the subforms table.

Correct. This is by design.

However, if you type '0' over the existing default 0 in the QtyIssued field
in the subform, it does appear in the subform table!

Again correct, and by design. I'm not sure why this observation would be so
surprising. A default value is simply the value that will be inserted if the
user skips that field when adding a new record. If the user doesn't add the
record, then the record won't exist...

How do I make it recognize the default zero?

It sounds to me as if it is recognizing the default zero. If you make an
entry in the same record, but in some other field, and then open the table,
you should notice that the default value of zero was stored in the correct
field.

Are you trying to get a result of zero in your query where no related
records exist?

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

When I opened the database today, in the places where yesterday there
was null, today the whole record is not shown!

The default value for QtyIssued in the subform is 0, but it doesn't
recognise the zero unless you type it in yourself. ie if you start a
new record and haven't issued anything yet, that record doesn't exist
in the subforms table. However, if you type '0' over the existing
default 0 in the QtyIssued field in the subform, it does appear in the
subform table! How do I make it recognize the default zero?

 

[Kate]

Yes I am trying to get a result of zero in my query where no related
query exists. This is because a different subform sums the total
number of products issued, and I want it to say zero if none have been
issued. If the record doesnt exist, it cant say zero. The other
reason i need it to say zero, is because i run a calculation off that
value. If it doesnt exist, I can't show the calculation...

 

[Tom Wickerath]

Hi Kate,

Yes I am trying to get a result of zero in my query where no related
query exists.

I assume you meant "where no related record exists". Is this correct?
Without knowing the design of your database, I'm going to suggest that you
try a tutorial that I have submitted to a friend of mine for publishing on a
web site, but, at this time has not yet been published. I can send you a copy
of this tutorial (Word document and sample database) if you send me a private
e-mail message with a valid reply address.

As written, it was not bringing forward a value of zero from a subform where
no records existed, but I was able to add the Nz function to provide this
capablility. I've posted a slightly obscured version of my e-mail address
below. Whatever you do, please do not post your e-mail address in a reply to
any newsgroup (unless you happen to love the spam that will follow).

Tom

QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Yes I am trying to get a result of zero in my query where no related
query exists. This is because a different subform sums the total
number of products issued, and I want it to say zero if none have been
issued. If the record doesnt exist, it cant say zero. The other
reason i need it to say zero, is because i run a calculation off that
value. If it doesnt exist, I can't show the calculation...