Thanks John, that finally worked. Now, however I need to do the same
thing
but instead of the between/and parameter in the ODATE field, I need the
parameters to be in two separate fields, Field1 [beginning of horizon] and
Field2 [ending of horizon]. I've tried re-writing what you gave me below
to
no avail - grrrrr. Can you help? Thanks much.
This is what I've tried:
SELECT [Model Count By Build Date - Q00].[Accounting Date], [Model Count
By
Build Date - Q00].resourceID, [Model Count By Build Date -
Q00].Start_Horizon, [Model Count By Build Date - Q00].End_Horizon,
DCount("resourceID","[Model Count By Build Date - Q00]","resourceID<=""" &
[resourceID] & """ And Start_Horizon = #" & [Beginning Of Horizon] & "#
And
End_Horizon = #" & [Ending Of Horizon] & "#") AS NumberRecords
FROM [Model Count By Build Date - Q00];
Alex
:
You do need to incorporate the date range in your DCount function. I
think that
the following will return what you want.
DCount("AAD","[AURORA_ACCOUNTING_DAYS]","AAD <=" & [AAD] & " AND ODATE
Between
#" & [begin date] & "# And #" & [end date] &"#") as Rank
You can probably do the same thing using a subquery
SELECT AURORA_ACCOUNTING_DAYS.AAD,
AURORA_ACCOUNTING_DAYS.ODATE,
AURORA_ACCOUNTING_DAYS.WORKDAY,
(SELECT Count(AAD)
FROM AURORA_ACCOUNTING_DAYS] as A
WHERE A.AAD <= AURORA_ACCOUNTING_DAYS.AAD
AND A.ODATE Between [begin date] And [end date]) as Rank
FROM AURORA_ACCOUNTING_DAYS
WHERE AURORA_ACCOUNTING_DAYS.ODATE Between [begin date] And [end date]
Alex wrote:
Below is my code: I'm finally getting a number, but it's the same
number
that is in the AAD field, which is an autonumber field. What I want is
that
if 1/1/06 - 3/31/06 results in 189 records, then I want the Test column
to
number the records 1 - 189. AAD is key field, but do I have to use a
key
field? Thanks
SELECT AURORA_ACCOUNTING_DAYS.AAD, AURORA_ACCOUNTING_DAYS.ODATE,
AURORA_ACCOUNTING_DAYS.WORKDAY,
DCount("AAD","[AURORA_ACCOUNTING_DAYS]","AAD
<=" & [AAD]) AS Test
FROM AURORA_ACCOUNTING_DAYS
WHERE (((AURORA_ACCOUNTING_DAYS.ODATE) Between [begin date] And [end
date]));
:
Post the SQL of the query you are trying to run. You may also need
to post
the Model Build Count, if that is a query.
Troubleshooting.
Just try
DCount("*","[Model Build Count]")
If that errors, then the error has something to do with [Model Build
Count].
Thanks John. This should be so easy and I getting so frustrated.
I tried
your suggestions and I either get #error in every field result or I
got
the
following error msg when running the query, "the expression you
entered as
a
query parameter produced this error, "the object doesn't contain
the
automation object "begin date", which is my parameter. I've tried
adding
the
parameters (begin date & end date) in the query and still get the
same
message.
Maybe I'm not understanding what DCount does. I just want a column
that
numbers the records sequentially. I would assume then, that it
doesn't
matter which field I name in the DCount function, as long as it's
not
null?
Thanks again
:
Since your query or table name has spaces, you have to use []
around the
name.
If ResourceID is a Text field then you need to surround it with
quote
marks
or apostrophes.
Try one of the following.
DCount("resourceID","[Model Build Count]","resourceID <=""" &
[resourceID] &
"""")
DCount("resourceID","[Model Build Count]","resourceID <='" &
[resourceID]
&
"'")
I've tried unsuccessfully, using DCount and a subquery to number
records
in
my query.
In a query called "ModelCount", I'm trying to number the records
returned
from the "Model Build Count" query using the resourceID field,
which is
a
text field. Model Build Count is a paramenter query.
I've tried DCount("resourceID","Model Build Count","resourceID
<=" &
[resourceID]) and other ways of writing this, but I keep getting
error
messages. Any help is appreciated. Thanks.
