Query Displayes Blanks

Occasional Visitor


I am not a developer, but I have a great new job and have managed to successfully repair a Microsoft Access Database created by my predecessor. There is an existing issue with a query:


1. The query gets the Sales (start and end date from a form) and the Cost (start and end date from a form the same form)


2. When the start date is 05/30/2022 with zero (0.00) sales as the start date, the query returns blanks; it should add the zero sales for the start date to the remaining dates in the range between the start date and the end date.


3. Here is the sql query code:

---SELECT [Query Sales3].[Profit Center], [Query Sales3].SumOfSales, [Query Food Cost3].Cost AS [Food ---Cost], Sum([Query Other Cost3].OCost) AS [Other Cost]
---FROM [Query Sales3], [Query Food Cost3], [Query Other Cost3]
---GROUP BY [Query Sales3].[Profit Center], [Query Sales3].SumOfSales, [Query Food Cost3].Cost
---HAVING ((([Query Sales3].[Profit Center])=[Forms]![Form3]![Profit Center]));


Please help and thank you.

2 Replies
there are 3 queries involved (Cartesian/Cross joined) in your query.
you also need to show the SQL for these 3.


Thank you for your help; here you go:

1. Sales Query is successful:

---SELECT [Sales Broad].[Profit Center], Sum([Sales Broad].Sales) AS SumOfSales
--FROM [Sales Broad]
--WHERE ((([Sales Broad].Date) Between [Forms]![Form3]![StartDate] And [Forms]![Form3]![EndDate]))
---GROUP BY [Sales Broad].[Profit Center];


2. Food Cost Query is successful:

---SELECT Sum([Invoice by Expensiture Type3].SumOfreceive_total) AS Cost
---FROM [Invoice by Expensiture Type3]
---WHERE ((([Invoice by Expensiture Type3].pg_credit)="COGS-No Perpetual Inv" Or ([Invoice by ---Expensiture Type3].pg_credit) Is Null));


3. Other Cost Query is failing:

---SELECT [Invoice by Expensiture Type3].pg_credit, Sum([Invoice by Expensiture ---Type3].SumOfreceive_total) AS OCost
---FROM [Invoice by Expensiture Type3]
---GROUP BY [Invoice by Expensiture Type3].pg_credit
---HAVING ((([Invoice by Expensiture Type3].pg_credit)<>"COGS-No Perpetual Inv" And ([Invoice by ---Expensiture Type3].pg_credit) Is Not Null));