Filling empty query fields with data

[jja]

Hi,

I have a query which gives me a results of balance info by warehouse by dates. if the balance is 0 then it is not shown in the table but in a query i use NZ function to show a zero value, but for the rest fields(StockCode and Wh) i do not know how to determin a right value because the fiellds can have a lot of different values. Now i use only one stockcode in one Wh. I another query i have a criterias in which i can define what StockCodes and Wh i will analyze and it can be a lot of them. Any Ideas how to solve this?

 

[vbaInet]

I would imagine that StockCode is in another table in that query. Use the StockCode and Wh from the other table. I think you've got a Left or Right join there.

 

[jja]

Yes StockCodes and Wh is stored in another query but the problem is that i need it date by date and in a query that I store stockCodes and wh it is joined with a table that has everyday data, That Table is joined with the data in the query i store stockcodes and if in that date there was no entry then it gives me null values in a query. The problem is here.
See the jpg. After that join i get the previouses picture.

 

[vbaInet]

Let me see a screenshot of your query in design view. I want to see a full list of the fields plus how it's joined.

Also, copy and paste the sql statement of the query as well.

 

[jja]

Hi,
There it is:
SELECT Trim(Query2!StockCode) AS StockCode, Query2.Wh, Nz([FreeBal],0) AS FreeBal1, Date.Date
FROM [Date] LEFT JOIN Query2 ON Date.Date=Query2.Date
WHERE (((Date.Date) Between "2010.05.01" And "2010.05.31"));

 

[vbaInet]

Why are you pointing the (relationship) arrow towards Query2? Shouldn't it be the other way round?

If query2 does not contain records within that date range then you simply cannot return a StockCode or Wh from query2.

 

[jja]

Yes i know this is wrong but thought it might be some kind if sql technik or something to avoid that. See i have made this sollution in another way using VBA but it takes a lot of time to calculate the results using vba. The point if this is to count the number of intervals the stockcode was not in wh. As you see from picture 2q in query 2 there is a gap from 2010.05.22 to 2010.05.24 so it means that 22,23,24 days of that month stock balance was 0 and that means that i had 3 consequtive days of missing something in stock. so it means in that month it was 1 interval of 0 balance in stock. As i have told i made the sollution with vba but it has to look at too many parameters and so on on the function works sometimes for 30 minutes to count the results.

 

[vbaInet]

Show me a screenshot of your RELATIONSHIPS.

 

[jja]

I don't use relationShips.

 

[vbaInet]

That's the most important part of a db. How are your tables related if you don't use relationships?

This would probably explain why it's taking 30 mins to run your function.