Query trouble with nulls! 2

[CTOROCK]

I have a query that pulls a count of entries from another query. I later merge that with other queries, but since one of the fields in null I don't get any data at all. I try to prevent this by typing this:

Insp Count: IIf(IsNull([InspCount]),"0",[InspCount])


I type this in the query that it comes from but I still don't get a thing. Can anyone help me turn the value to a zero if null?
thanks!

Eric

 

[scriverb]

The NZ Function should work just fine.

InspCount: NZ([InspCount],0)

If this doesn't do it could you copy the SQL from your two queries and post so that we may look at the specifics of the nested queries?

Also, I noted an extra space in the Insp Count. Was this a typo?

Bob Scriver

 

[CTOROCK]

It still doesn't work, here is SQL from the first:

SELECT Tbl_Fundraiser.Campaign, Count(Tbl_Fundraiser.[Fund Type]) AS InspCount, Tbl_Fundraiser.[Fund Type]
FROM Tbl_Fundraiser
GROUP BY Tbl_Fundraiser.Campaign, Tbl_Fundraiser.[Fund Type]
HAVING (((Tbl_Fundraiser.Campaign)="10&quot AND ((Tbl_Fundraiser.[Fund Type])="inspirational&quot);

Here is Sql from the Query I try to merge this with:

SELECT Tbl_Fundraiser.Campaign, [Q_Count - Mini].[Mini Count], [Q_Count - Full].FullCount, Sum(Tbl_Fundraiser.[Net Sales]) AS [SumOfNet Sales], Avg(Tbl_Fundraiser.[Net Sales]) AS [AvgOfNet Sales]
FROM Tbl_Fundraiser, [Q_Count - Mini], [Q_Count - Full]
GROUP BY Tbl_Fundraiser.Campaign, [Q_Count - Mini].[Mini Count], [Q_Count - Full].FullCount;



I left a space in Insp Count because otherwise in this query it will give me a Circular reference error.

Thanks!

 

[scriverb]

CTOROCK: I am a little confused here. The first SQL listed. What is the name of that query? You do not seem to be making any reference to that query in the second query.

Secondly, if you execute the first query are you returning any records?

The second SQL you say you are trying to Merge with it. There doesn't seem to be any relationship between any of the tables, queries here. Table Tbl_Fundraiser seems to be the common link here but you have not establish either a Join between this table and I take it the two queries [Q_Count - Mini] and [Q_Count - Full] or you have to have a Where or Having statement to select the records to be displayed.

Please provide names for the two queries and if there is another please display that SQL also.

Will wait for your response.

Bob scriver

 

[CTOROCK]

Thanks A lot, the Final query is called Q_Ytd FundRaisers By Camp. It has data comming from three sources. 1) Tbl_Fundraiser 2)Q_Count-Mini 3) Q_Count-Full

this is the SQL for it:
SELECT DivDist.Division, NewFundraiserDATAENTRY.District, NewFundraiserDATAENTRY.AcctNo, Sum(NewFundraiserDATAENTRY.OrderAmt) AS SumOfOrderAmt, NewFundraiserDATAENTRY.Camp
FROM DivDist INNER JOIN NewFundraiserDATAENTRY ON DivDist.District = NewFundraiserDATAENTRY.District
GROUP BY DivDist.Division, NewFundraiserDATAENTRY.District, NewFundraiserDATAENTRY.AcctNo, NewFundraiserDATAENTRY.Camp;

There are no relationships because I just want the straight values in one line from the sources. Now, I would like to add one more query, but it is null and screws everything up! I want to add Q_Count-Single. The Q_count-full & Mini both return values so it is ok. THis is the SQL to the Q_count-Single:

SELECT Tbl_Fundraiser.Campaign, Count(Tbl_Fundraiser.[Fund Type]) AS SngCount, Tbl_Fundraiser.[Fund Type]
FROM Tbl_Fundraiser
GROUP BY Tbl_Fundraiser.Campaign, Tbl_Fundraiser.[Fund Type]
HAVING (((Tbl_Fundraiser.Campaign)="10&quot AND ((Tbl_Fundraiser.[Fund Type])="single&quot);

The full & mini queries are the same thing. So I need to have the field SngCount to return a 0 if null.
Hope this is a little better explination...Thanks a lot

eric

 

[scriverb]

The query Q_count-Single with SQL will never return a row of data when there are no Fund Type = "Single". When you have no records that meet the HAVING portion of the query there will be no records displayed/returned.

SELECT Tbl_Fundraiser.Campaign, Count(Tbl_Fundraiser.[Fund Type]) AS SngCount, Tbl_Fundraiser.[Fund Type]
FROM Tbl_Fundraiser
GROUP BY Tbl_Fundraiser.Campaign, Tbl_Fundraiser.[Fund Type]
HAVING (((Tbl_Fundraiser.Campaign)="10&quot AND ((Tbl_Fundraiser.[Fund Type])="single&quot);

If you require a record to be returned even if the count of the Fund Type = 0 then you could do perform a DCount of the number of Fund Type = "single" prior to running this query. If the number is 0 then run a different query where you force a zero(0) value in for that column:

If DCount("*", "Tbl_Fundraiser","[Fund Type] = 'single'&quot > 0 then
docmd.runQuery "Q_count-Single"
else
docmd.runquery "Q_count-Single_Zero"
end if

Q_count-Single_Zero

SELECT Tbl_Fundraiser.Campaign, 0 AS SngCount, "single" as Fund_Type
FROM Tbl_Fundraiser
GROUP BY Tbl_Fundraiser.Campaign, Fund_Type
HAVING Tbl_Fundraiser.Campaign="10";

Will this somehow work in your situation?

Bob Scriver

 

[dawnd3]

I didn't read this whole thread, but thought I would post incase this helps. I have quite often gotten results by replacing null with an empty string "". Dawn

http://www.calliopeconsulting.net

 

[CTOROCK]

Thanks, Scriverb. I like the idea of doing the DCount to see if there are any "Singles", but I would like to see if there are any "singles" in a certain campaign. If I do it the way you specified, then I certainly would get a value > 0. is there a way mabey I could create a domain to do a dlookup in. I would setup an input box where they specify a campaign then I would build a domain with only that campaign, then do the Dlookup with those records, and produce the reports where the campaign is the value of the input box.

But can I make a query-like domain at runtime? Any suggestions? Thanks,

Eric

 

[scriverb]

Sure you can start with this type of Inputbox prompting to get the Campaign designation and check for the existence of records for Campaign and Fund Type values. Then the queries can also be dynamically created and updated with specific code to reflect the "domain" as you called it. This would be done by updating the .SQL property of a query.
I can help with that also.

Dim vResponse as string
vResponse = InputBox("Enter Campaign code: &quot
If DCount("*", "Tbl_Fundraiser","[Campaign]= '" & vResponse & "' and [Fund Type] = 'single'&quot > 0 then
docmd.runQuery "Q_count-Single"
else
docmd.runquery "Q_count-Single_Zero"
end if

Here is an example of dynamic SQL development:
Dim db as database
Set db = currentdb
db.QueryDefs("Query_Count-single&quot.SQL = "SELECT Tbl_Fundraiser.Campaign, Count(Tbl_Fundraiser.[Fund Type] AS SngCount, Tbl_Fundraiser.[Fund Type] FROM Tbl_Fundraiser
GROUP BY Tbl_Fundraiser.Campaign, Tbl_Fundraiser.[Fund Type]
HAVING (((Tbl_Fundraiser.Campaign)='" & vResponse & "') AND ((Tbl_Fundraiser.[Fund Type])='single'));"
db.close

Is this the kind of dynamic coding of a SQL query that you are looking for.

 

[CTOROCK]

Thanks A lot!