handling nullvalues in query

[balu]

dear sir,
i have an field [table1]
fields [a] number
" "
" [c] date/time
all fields have no values
in select query like
sum(nz([field1]![a]),0) like for all number fields. and for date field
will be
like WHERE (between [two pre said values])in QBE
returning even without an blank field
kindly helo me how come over such probloms.

 

[John W. Vinson]

dear sir,
i have an field [table1]
fields [a] number
" "
" [c] date/time
all fields have no values
in select query like
sum(nz([field1]![a]),0) like for all number fields. and for date field
will be
like WHERE (between [two pre said values])in QBE
returning even without an blank field
kindly helo me how come over such probloms.

Please open the Query in SQL view and post it here. The above is not making
much sense - I *GUESS* that by [Field1]![a] you meant [table1].[a] but none of
the rest is coming through.

If you are searching with a criterion of

BETWEEN [start] AND [end]

then NULL values of the date field will certainly not be found. NULL is not a
match for *any* query criterion other than

IS NULL

John W. Vinson [MVP]

 

[balu]

dear sir,
my table is like this
table[ PAYMENT]
FIELDS [ it payment] currency
" [HQS payment] currency
" [employeesalary] currency
" [ misc payment] currency
" [payer code] number
" [ date] date/time
QUERY1,

[Itpayment] [hqs payment] [employeesalary] [miscsalary][ payercode]
[date]
sum sum sum sum
groupby where

Criteria: [p code] between[date1] and [date2]
and i also included sum (all the four payments fields at the last)
note: sir i used nz( ) function with all fields except date and payercode
fields in QBE
NOTE2: sir my table is totaly empty with no records entered in it for any
date
thanks for kid help

dear sir,
i have an field [table1]
fields [a] number
" "
" [c] date/time
all fields have no values
in select query like
sum(nz([field1]![a]),0) like for all number fields. and for date field
will be
like WHERE (between [two pre said values])in QBE
returning even without an blank field
kindly helo me how come over such probloms.

Please open the Query in SQL view and post it here. The above is not making
much sense - I *GUESS* that by [Field1]![a] you meant [table1].[a] but none of
the rest is coming through.

If you are searching with a criterion of

BETWEEN [start] AND [end]

then NULL values of the date field will certainly not be found. NULL is not a
match for *any* query criterion other than

IS NULL

John W. Vinson [MVP]

 

[John W. Vinson]

my table is like this
table[ PAYMENT]
FIELDS [ it payment] currency
" [HQS payment] currency
" [employeesalary] currency
" [ misc payment] currency
" [payer code] number
" [ date] date/time

Then your table is incorrectly designed.

You need a Primary Key - what is this a payment FOR?

You should also probably have *two* tables:

PaymentTypes
Type <Text, Primary Key> <example: "IT", "HQS", "Employee Salary", "Misc">

Payments
PaymentID <Autonumber, Primary Key>
Type <link to PaymentTypes>
Amount <Currency>
PaymentDate <Date/Time>
PayerCode <Number, Long Integer, link to Payers table>
Comments <Text or Memo>

This will let you easily add new types of payments, and make it much easier to
get flexible sums of subsets of the data; it will also allow you to have
different payer codes associated with different payments.

QUERY1,

[Itpayment] [hqs payment] [employeesalary] [miscsalary][ payercode]
[date]
sum sum sum sum
groupby where
Criteria: [p code] between[date1] and [date2]

This query can be created using a Crosstab query if you use my suggested table
redesign.

and i also included sum (all the four payments fields at the last)
note: sir i used nz( ) function with all fields except date and payercode
fields in QBE
NOTE2: sir my table is totaly empty with no records entered in it for any
date
thanks for kid help

If there are no records in the table, then - naturally - the query will not
return any records. A query can't display what isn't there!

John W. Vinson [MVP]

 

[balu]

dearsir,

heartly thanks a lot ,your suggestion has really worked for me.
i would like to know why nz() function will not work at such circum stences
of a table having null values and because of that the entire query will go
null ,at times ,how to over come and what basic princple / trick if any
please advise.
spelling correction:
i apollogise for spelling mistake in my last reply as" kid "insted of" kind "
thanking you

my table is like this
table[ PAYMENT]
FIELDS [ it payment] currency
" [HQS payment] currency
" [employeesalary] currency
" [ misc payment] currency
" [payer code] number
" [ date] date/time

Then your table is incorrectly designed.

You need a Primary Key - what is this a payment FOR?

You should also probably have *two* tables:

PaymentTypes
Type <Text, Primary Key> <example: "IT", "HQS", "Employee Salary", "Misc">

Payments
PaymentID <Autonumber, Primary Key>
Type <link to PaymentTypes>
Amount <Currency>
PaymentDate <Date/Time>
PayerCode <Number, Long Integer, link to Payers table>
Comments <Text or Memo>

This will let you easily add new types of payments, and make it much easier to
get flexible sums of subsets of the data; it will also allow you to have
different payer codes associated with different payments.

QUERY1,

[Itpayment] [hqs payment] [employeesalary] [miscsalary][ payercode]
[date]
sum sum sum sum
groupby where
Criteria: [p code] between[date1] and [date2]

This query can be created using a Crosstab query if you use my suggested table
redesign.

and i also included sum (all the four payments fields at the last)
note: sir i used nz( ) function with all fields except date and payercode
fields in QBE
NOTE2: sir my table is totaly empty with no records entered in it for any
date
thanks for kid help

If there are no records in the table, then - naturally - the query will not
return any records. A query can't display what isn't there!

John W. Vinson [MVP]

 

[John W. Vinson]

heartly thanks a lot ,your suggestion has really worked for me.
i would like to know why nz() function will not work at such circum stences
of a table having null values and because of that the entire query will go
null ,at times ,how to over come and what basic princple / trick if any
please advise.

Glad I was able to help.

The NZ() function will convert a NULL field value in an existing record to a
zero, or to the value of the optional second argument of the function.

If the query - or the table - has *no records at all*, then there is no NULL
value to convert to anything.

It would be like saying "If there is nothing printed on Page 1, interpret it
as This Page Intentionally Left Blank", and showing a book which has no pages
in it. There *is* nothing intentionally left blank because there is nothing
there!

John W. Vinson [MVP]

 

[balu]

dearsir,
i really understood how nz() function and what the null values can be
in detail i would like to have your kind advise for the data base im
creating .
im creating an data base with 16 tables what makes me worried is .
(1) all the tables data is necessery to get final report, but the user will
not enter
any data ina table where there is not necessery . like, in a dabase of an
social welfare organisation which have detailes of donations recevived and
its transaction like
table banktransaction and / or
table income tax paid and
table governament help received
but these tables i have to include in final statement query
but if the organisation not had any transaction / nor received any govt
help normally the user will not touch such forms and inturn it couses the
whole finalreport query to collape
(2) and all my tables have no related fields to establish fine link of
relation ships
most PK fields are generated keys hence i created relationships in QBE
window
(3) how to come out of such herdles
(4) it is necessery for me to give the previes year closing balence and the
present year opening balences and the present yesr closing balences
hence the date criteria as very essential for me to use in every table
query
kindly advise to over come such probloms
thanking you sir,

 

[John W. Vinson]

dearsir,
i really understood how nz() function and what the null values can be
in detail i would like to have your kind advise for the data base im
creating .
im creating an data base with 16 tables what makes me worried is .
(1) all the tables data is necessery to get final report, but the user will
not enter
any data ina table where there is not necessery . like, in a dabase of an
social welfare organisation which have detailes of donations recevived and
its transaction like
table banktransaction and / or
table income tax paid and
table governament help received
but these tables i have to include in final statement query
but if the organisation not had any transaction / nor received any govt
help normally the user will not touch such forms and inturn it couses the
whole finalreport query to collape

You can use "Left Joins" in queries to display records from the "one" side
table even if there are no matching records in the "many" - select the join
line in the query design window and choose Option 2, "show all records in
<main table> and matching records in <child table>". Depending on how your
tables are structured, this can cause other errors; it is the right technique,
but it must be done correctly.

One concern: There is a strong temptation to look at the desired appearance of
the "final report" and let that drive your table design. This is generally
going to give you a set of *very badly designed tables*. The layout of the
Report should come LAST, after the Tables are correctly normalized based on
the logical structure of the data in the tables. You very well may have done
so, I can't tell from this post... but it's a very common error starting out
with Access.

(2) and all my tables have no related fields to establish fine link of
relation ships
most PK fields are generated keys hence i created relationships in QBE
window

If your tables have no related fields to link, *you do not have a
database*!!!!! You have a bunch of unrelated stacks of data! If you are
dealing with an Organization with multiple Transactions, say, then you must -
no option! - include a link to the Organization in the Transactions table in
order for Access to know which organization originated these transactions.

(3) how to come out of such herdles

Design your tables and their relationships correctly, *first*, before starting
to even think about forms.

(4) it is necessery for me to give the previes year closing balence and the
present year opening balences and the present yesr closing balences
hence the date criteria as very essential for me to use in every table
query

That's not a question; that's a true statement. Yes, date criteria are
essential; each table which contains date-sensitive data should have a
required date field. Since I don't know your business model nor the contents
of your tables, nor even what date problems you're having, I can't be more
specific than that.

kindly advise to over come such probloms
thanking you sir,

This is asking a LOT: starting with the basic fundamental table design. I
would really suggest that you try to find a local expert for hire to help you
set this up.

For some tutorials and introduction to relational theory, if you haven't
already done so consider looking at some of the links in:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

John W. Vinson [MVP]

 

[balu]

dear sir,
thanks for yor advises and patience
the left join has worked
thanking you'

 

[balu]

dear sir ,
sorry for disturbing aou once again
i have developed my data base successfully !
i would like to presnt it to you for valluation and further advises before
i could present it to the some one may i have your Email address please
balu,

dear sir,
i have an field [table1]
fields [a] number
" "
" [c] date/time
all fields have no values
in select query like
sum(nz([field1]![a]),0) like for all number fields. and for date field
will be
like WHERE (between [two pre said values])in QBE
returning even without an blank field
kindly helo me how come over such probloms.

Please open the Query in SQL view and post it here. The above is not making
much sense - I *GUESS* that by [Field1]![a] you meant [table1].[a] but none of
the rest is coming through.

If you are searching with a criterion of

BETWEEN [start] AND [end]

then NULL values of the date field will certainly not be found. NULL is not a
match for *any* query criterion other than

IS NULL

John W. Vinson [MVP]

 

[balu]

dear sir,
i have a table name "dutyroster"
field employeename
" onduty
" offduty
" date
now i would like to transfer the query output of emloyeename , onduty ,
offduty , date, to excel file using docmd.outputto method and do calculation
at excelsheet
of totaldutyof employee.
but i need to calculate the totalduty and the other cell formats such as
color and font size etc , as because of the excel file not retaining the
formula and format
each and every time the command runs . how to come out of this problom please
kindly help me.