distinct DCount - so close! or so close?

  • Thread starter Thread starter Murph
  • Start date Start date
M

Murph

Hi all

I've looked through the messages to try and find my answer but so far have
not been able to put all the pieces together.

Essentially, I need to count the number of locations showing sales by item
in that week or before. Query qryLocationNum is a SELECT DISTINCT Query on
SalesDetail query to get me distinct location numbers.

Here's the function:

LocCountToDate: DCount("*","qryLocationNum","[LocationNum] IN (SELECT
[LocationNumber] FROM [SalesDetail] WHERE [WeekDate] <='" &
[SalesDetail]!WeekDate & "' and [ItemNumber] = '" & [SalesDetail]!ItemNumber
& "')")

I'm new to this functionality in Access, but I think I'm close to a
solution. The results seem to be counting all locations no matter what.

I'm not one to normally ask, but am out of ideas.

Thanks in advance to anyone who might be able to assist!
 
Thanks June7 this seemed to fix the problem. I have to roll it out to live
data to be sure, but am fairly confident this is what was lacking.

It wouldn't be referencing every record as there are also dates in the table
greater than the current record's date.

I should note that I did try and use Allen's solution ecount() before this
and it was generating an error. I thought it was a DAO/ADO reference issue,
and could still be I suppose, but I don't have the background to test things
in the time I would need to get it working on my set-up. I checked to make
sure DAO had preference over ADO, but that was as far as I looked. The error
(3131) said something about an issue with the FROM clause and was hanging on
the next to last line of code. I know others have had much success and his
solution is well documented, I just ran out of time to figure out why my db
couldn't run it. (Probably user error on me, something like the Douglas Adams
quote (paraphrased) "A common mistake that people make when trying to design
something completely foolproof is to underestimate the ingenuity of complete
fools.)

June7 via AccessMonster.com said:
See if replacing the apostrophe delimiters for the date values with # symbol
helps. Not sure if the asterisk will work like this, never tried anything
like that. Perhaps need to use specific field name.
Also troubles me that you use as a parameters ([SalesDetail]!WeekDate and
[SalesDetail]!ItemNumber) the same fields that Where condition is filtering
on. Wouldn't every record meet this condition? Twists my brain trying to
conceptualize.
Hi all

I've looked through the messages to try and find my answer but so far have
not been able to put all the pieces together.

Essentially, I need to count the number of locations showing sales by item
in that week or before. Query qryLocationNum is a SELECT DISTINCT Query on
SalesDetail query to get me distinct location numbers.

Here's the function:

LocCountToDate: DCount("*","qryLocationNum","[LocationNum] IN (SELECT
[LocationNumber] FROM [SalesDetail] WHERE [WeekDate] <='" &
[SalesDetail]!WeekDate & "' and [ItemNumber] = '" & [SalesDetail]!ItemNumber
& "')")

I'm new to this functionality in Access, but I think I'm close to a
solution. The results seem to be counting all locations no matter what.

I'm not one to normally ask, but am out of ideas.

Thanks in advance to anyone who might be able to assist!
Click to expand...
Click to expand...
 
Back
Top