Querie return nullS when id like 0'S

tinker

Registered User.
Local time
Today, 20:43
Joined
Aug 20, 2001
Messages
25
I have a table which has a list of countries, and another with details of which hotels within these countries have paid money. I have grouped this table so that i have a list of countries which have paid money. I then created a query with both tables, which have a relationship based on Country names, the problem is where a country hasnt contributed, it returns a blank where id like 0's
 
I did not explain properly

I have a table which has a list of countries, and another with details of which hotels within these countries have paid money (e.g amount paid). I have grouped this table so that i have a list of countries which have paid money. I then created a query with both tables, which displays the country name and how much they have paid, the problem is where a country hasnt contributed, it returns a blank where id like 0's (This is the amount paid field which displays a blank)
 
Have you tried in your query parameter - Not is null or > 0 ?


I am assuming that you have these two tables:

1stTABLE

Country
USA
UK

Country____Hotel____Amount Paid
__USA_______H1_________200
__USA_______H2___________0
__UK________H3_________300
__UK_______ H4___________0
__UK_______ H5___________0


I am also assuming that you have the field "Country" in your second table.

Grouping the second table:

You can have

USA____H1__200
UK_____H3__300

Or you can have

USA 200
UK 300

If you do not have "Country" as one of your fields in the second table, how are you joining the two tables in a query?




[This message has been edited by Liv Manto (edited 08-20-2001).]
 
Use either an Iif IsNull statement or lookup the Nz function, both should give the result you are looking for.
HTH
 
I ve managed to solve this problem. I couldnt seem to get it to work with any of the suggestions (possibly because the code is not compatible with the Sql statement behind the query which is where i placed the code). Instead, in the SQL i used a Switch statement as follows

SELECT QryGroupNames.[CH Group Name], SWITCH ( Sum([tblContributors Invoices].[Sterling Amount])<>null,Sum([tblContributors Invoices].[Sterling Amount]), not( Sum([tblContributors Invoices].[Sterling Amount])<>null), 0) AS [SumOfSterling Amount]

In otherwords if Amount is Not Not equal to Null (= Null) then switch it with a 0.

Its a long way around it but seems to work

Thanks
 
One of the problems I had with a query that could sometimes return nulls was solved when I added a hidden field (Expression}

IsNull([Field]) in the query matrix, and then put "FALSE" as a selection criterion.


Make it a SEPARATE COPY of the field. Works better that way.
 

Users who are viewing this thread

Back
Top Bottom