Access query help, expression fields work seperately but not toget

[Accessor]

Hi all. I'm a little frustrated with a query I'm working on. I'll try to keep
this as straightforward as possible. I am returning four fields in it. There
are a query and a table linked. The first two fields, symbol and date of
interest, come from the query. I have expression fields as the next two
fields, both of which work seperately in the query but not together. They
both perform a similar task, they look up a value in the table, based on the
symbol and date of interest fields. The SQL is below. Any suggestions?

SELECT DISTINCT qryEarningsDatesUnique.Symb,
qryEarningsDatesUnique.DateOfInterest, tblStocksPricingVol.OpenPrice AS
OpenDOI, tblStocksPricingVol.ClosePrice AS ClosePriorDOI

FROM qryEarningsDatesUnique INNER JOIN tblStocksPricingVol ON
qryEarningsDatesUnique.Symb = tblStocksPricingVol.Symbol

WHERE (((qryEarningsDatesUnique.DateOfInterest) Between #7/21/2009# And
workday(Date(),-5)) AND ([Symbol]=[Symb] And
[PricingVolWebQueryDate]=CDate([DateOfInterest])) AND ([Symbol]=[Symb] And
[PricingVolWebQueryDate]=workday([DateOfInterest],-1)));

 

[KARL DEWEY]

Where does PricingVolWebQueryDate] come from?

 

[Accessor]

That's a field in the table. So a date is retrieved from the subquery (date
of interest) and that is compared to the field value PricingVolWebQueryDate.
This is stock market pricing data if it's of any help for clarity...

Where does PricingVolWebQueryDate] come from?
--
Build a little, test a little.

Hi all. I'm a little frustrated with a query I'm working on. I'll try to keep
this as straightforward as possible. I am returning four fields in it. There
are a query and a table linked. The first two fields, symbol and date of
interest, come from the query. I have expression fields as the next two
fields, both of which work seperately in the query but not together. They
both perform a similar task, they look up a value in the table, based on the
symbol and date of interest fields. The SQL is below. Any suggestions?

SELECT DISTINCT qryEarningsDatesUnique.Symb,
qryEarningsDatesUnique.DateOfInterest, tblStocksPricingVol.OpenPrice AS
OpenDOI, tblStocksPricingVol.ClosePrice AS ClosePriorDOI

FROM qryEarningsDatesUnique INNER JOIN tblStocksPricingVol ON
qryEarningsDatesUnique.Symb = tblStocksPricingVol.Symbol

WHERE (((qryEarningsDatesUnique.DateOfInterest) Between #7/21/2009# And
workday(Date(),-5)) AND ([Symbol]=[Symb] And
[PricingVolWebQueryDate]=CDate([DateOfInterest])) AND ([Symbol]=[Symb] And
[PricingVolWebQueryDate]=workday([DateOfInterest],-1)));

 

[KARL DEWEY]

I would recommend removing parts of your criteria, piece by piece until it
works and then add back, correcting the problem.
--
Build a little, test a little.

That's a field in the table. So a date is retrieved from the subquery (date
of interest) and that is compared to the field value PricingVolWebQueryDate.
This is stock market pricing data if it's of any help for clarity...

Where does PricingVolWebQueryDate] come from?
--
Build a little, test a little.

Hi all. I'm a little frustrated with a query I'm working on. I'll try to keep
this as straightforward as possible. I am returning four fields in it. There
are a query and a table linked. The first two fields, symbol and date of
interest, come from the query. I have expression fields as the next two
fields, both of which work seperately in the query but not together. They
both perform a similar task, they look up a value in the table, based on the
symbol and date of interest fields. The SQL is below. Any suggestions?

SELECT DISTINCT qryEarningsDatesUnique.Symb,
qryEarningsDatesUnique.DateOfInterest, tblStocksPricingVol.OpenPrice AS
OpenDOI, tblStocksPricingVol.ClosePrice AS ClosePriorDOI

FROM qryEarningsDatesUnique INNER JOIN tblStocksPricingVol ON
qryEarningsDatesUnique.Symb = tblStocksPricingVol.Symbol

WHERE (((qryEarningsDatesUnique.DateOfInterest) Between #7/21/2009# And
workday(Date(),-5)) AND ([Symbol]=[Symb] And
[PricingVolWebQueryDate]=CDate([DateOfInterest])) AND ([Symbol]=[Symb] And
[PricingVolWebQueryDate]=workday([DateOfInterest],-1)));