PROBLEM WITH REPEATED (Null) VALUE IN MULTIPLE QUERY

[johnniewalkeresp]

Hi,

I'm having trouble with the following:
I have a portfolio table with a field called "Bid/Offer" which is filled in by selecting Bid or Offer from a combo box.
Now I need to create a query that separates the bids in one field and the offers in another one (plus many other fields). I have created 2 queries, one that shows all the bids for all portfolios and another one that shows all the offers.
The problem comes when I try to create the third and last query, so it can show me all the bids and offers for each portfolio (I need it to be separate because each bid an offer have respective fields called price, that show the bid price and the offer price for a particular portfolio). There are some times when a portfolio has 4 bids, but only 1 offer, so when I run the query, the bid values are correct, but the offer ones are not because it fills the null values with the last it filled in.
For example here are the bids and offers of the same portfolio in different dates.

Date ClientB Bid Price Price Offer ClientO
03-04-07 DR Bid 34 45 Offer GD
05-03-07 ER Bid 14 (no offer on this date)
07-09-07 AF Bid 45 89 Offer KL

My problem is that the fields that are supposed to turn out in blank because they are null, repeat the info from the last cell. For example, for 05-03-07, on the Price field it will say 45 and then Offer, like in the previous date.

how can I change this so it leaves the field blank or at least puts a zero or something?

Thank you very much for your help!

[Scott Price]

Hi,

I'm having trouble with the following:
I have a portfolio table with a field called "Bid/Offer" which is filled in by selecting Bid or Offer from a combo box.
Now I need to create a query that separates the bids in one field and the offers in another one (plus many other fields). I have created 2 queries, one that shows all the bids for all portfolios and another one that shows all the offers.
The problem comes when I try to create the third and last query, so it can show me all the bids and offers for each portfolio (I need it to be separate because each bid an offer have respective fields called price, that show the bid price and the offer price for a particular portfolio). There are some times when a portfolio has 4 bids, but only 1 offer, so when I run the query, the bid values are correct, but the offer ones are not because it fills the null values with the last it filled in.
For example here are the bids and offers of the same portfolio in different dates.

Date ClientB Bid Price Price Offer ClientO
03-04-07 DR Bid 34 45 Offer GD
05-03-07 ER Bid 14 (no offer on this date)
07-09-07 AF Bid 45 89 Offer KL

My problem is that the fields that are supposed to turn out in blank because they are null, repeat the info from the last cell. For example, for 05-03-07, on the Price field it will say 45 and then Offer, like in the previous date.

how can I change this so it leaves the field blank or at least puts a zero or something?

Thank you very much for your help!

Shall we take a peek at the SQL for your third query? Just copy and paste it in here, then wrap it in the code tags by selecting it all, clicking on the # button on the top of this reply window. Then you can modify the first code tag to read like this: [code=sql] just like that!

Thanks,
Regards,
Scott