DCount Not Working Properly

[jskernahan]

Hi, I've got a problem that i've been trying to solve for quite some
time now and I can't figure it out. I'm using a DCount function in a
query to count the number of records in a table relating to a
displayed date. the SQL looks like this:

SELECT ProductionReport_Summary.Date,
DCount("UWI","FacilityData","Est_TieIn=#" & [Date] & "#") AS
TieInCount
FROM ProductionReport_Summary
WHERE (((Year([Date]))>=Year(Date())));

I am able to run the query and no errors come up, but the DCount is
returning the wrong count!! it's very frustrating. For example, the
DCount will return a count of 0 when the [Date] field = #01-Sep-08#
BUT when i go into the query and type in:

SELECT ProductionReport_Summary.Date,
DCount("UWI","FacilityData","Est_TieIn=#01-Sep-08#") AS TieInCount
FROM ProductionReport_Summary
WHERE (((Year([Date]))>=Year(Date())));

it returns the proper value. WHAT IS GOING ON?!?!

 

[John Spencer]

Try specifying the field by using the table name also.

SELECT ProductionReport_Summary.Date,
DCount("UWI","FacilityData","Est_TieIn=#" & ProductionReport_Summary.[Date] &
"#") AS TieInCount
FROM ProductionReport_Summary
WHERE (((Year([Date]))>=Year(Date())));

Date is a poor choice for a field namd as it is a reserved word. That means
that you could end up with some confusion on whether or not Access sees [Date]
the field or Date() the function as a function or as a field.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

Date is a reserved word in Access, and should not be used as the name of a
field, if you can help it. It's a little difficult to determine what you are
trying to do, because you reference date in three different ways
(ProductionReport_Summary.date, [Date], and Date()).

It looks like you are trying to select all of the date values from the
ProductionReport_Summary, where the production year is greater than todays
year (but I'm not sure how you get a production dates greater than today's
date, unless these are expected production values). And along with that date
(or list of date), you wan to determine how many records in the FacilityData
table have Est_TieIn dates that are the same as the dates in the
ProductionReport_Summary. If that assessment is correct, try:

SELECT PRS.[Date] as ProdDate
DCount("UWI","FacilityData","Est_TieIn=#" & PRS.[Date] & "#") AS
TieInCount
FROM ProductionReport_Summary
WHERE Year(PRS.[Date]))>=Year(Date())

However, if you are using [Date] as a date parameter that you want to popup
whenever the query is run, try:

Parameters [Enter date!] DateTime;
SELECT PRS.[Date],
DCOUNT("UWI", "FacilityData", "Est_TieIn =#" & [Enter date!] & "#") as
TieInCount
FROM ProductionReport_Summary as PRS
WHERE Year([Enter date!]) >= Year(Date())

IF neither of these gets what you want, maybe you could explain what you are
trying to accomplish, like I did above.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[jskernahan]

I know date is a reserved word and a poor choice of field name but
ProductionReport_Summary is a linked table and I am unable to change
the name of the field.

I tried your suggestions and they didn't work, here's the SQL i tried:

SELECT ProductionReport_Summary.Date,
DCount("UWI","FacilityData","Est_TieIn=#" & ProductionReport_Summary.
[Date] &
"#") AS TieInCount
FROM ProductionReport_Summary
WHERE (((Year([Date]))>=Year(Date())));

And Dale, I'm attempting to perfrom the task of the first thing you
explained, not the second. That is, I want to determine how many
records in the FacilityData table have Est_TieIn dates that are the
same as the dates in the ProductionReport_Summary table. And yes, they
are expected production values.

I have tried changing the name of the [Date] field by running
ProductionReport_Summary through a query and then running the DCount
query off that, but it didn't work. I have also further looked into
where the issue is and it appears that the DCount is having problems
counting dates that begin in "01". For example, it can count the date
#30/09/2008# but cannot count the date #01/09/2008#. This is very
strange, any thoughts?

James

 

[Dale Fye]

Are your [Date] and [Est_TieIn] fields text, or date data types?

Change the WHERE clause to:

WHERE Year(ProductionReport_Summary.[Date]) > Year(Date())

You also might want to try:

"Est_TieIn = #" & cDate(ProductionReport_Summary.[Date]) & "#"

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[jskernahan]

They're both date data types. I tried both of your suggestions and it
still doesn't work. I think I might try and write a VB work around
because this seems hopeless.

 

[John Spencer]

AHA! International Dates Problem.

See Allen Browne's article International Dates in Access at:
http://allenbrowne.com/ser-36.html

Try
DCount("UWI","FacilityData","Est_TieIn=#" &
Format(ProductionReport_Summary.[Date],"yyyy-mm-dd") & "#") AS TieInCount


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[jskernahan]

VICTORY!! you're a brilliant man John! Thanks.