Ecount help please

[lda via AccessMonster.com]

From my report, Wetlab_Backlog, I am getting the following errors: "syntax
error in from clause" for all three fields were i use ECount.

=ECount("[test]","DE_BACKLOG07-21-2008_BK0094")
=eCount("[samplenumber]","DE_BACKLOG07-21-2008_BK0094","samplenumber",True)
=eCount("[orderid]","DE_BACKLOG07-21-2008_BK0094","samplenumber",True)

I have tried to designate what table the field is coming from like =ECount("
[sampledetails].[test]","DE_BACKLOG07-21-2008_BK0094")

The SQL for the report is:
SELECT SampleDetails.Test, SampleDetails_Params.Param, Tests.TestGroup, Tests.
Description, Tests.BottleType, Tests.Method, SampleDetails.Matrix, Tests.
AnalysisHoldingTime, SampleDetails.OrderID, SampleDetails.SampleNumber,
OrderDetails.CustomerSampleNumber, OrderDetails.Site, SampleDetails.DueDate,
SampleDetails.DueDateFlag, SampleDetails.HasResults, OrderDetails.ReceiveFlag,
([DueDate]-Date()-2) AS TeststillDays, Tests.SamplingMethod, Orders.SignOff
FROM (((SampleDetails LEFT JOIN Tests ON (SampleDetails.Matrix = Tests.Matrix)
AND (SampleDetails.Test = Tests.Test)) LEFT JOIN OrderDetails ON
(SampleDetails.OrderID = OrderDetails.OrderID) AND (SampleDetails.
SampleNumber = OrderDetails.SampleNumber)) LEFT JOIN SampleDetails_Params ON
(SampleDetails.Test = SampleDetails_Params.Test) AND (SampleDetails.
SampleNumber = SampleDetails_Params.SampleNumber) AND (SampleDetails.OrderID
= SampleDetails_Params.OrderID)) LEFT JOIN Orders ON SampleDetails.OrderID =
Orders.OrderID
WHERE (((Tests.BottleType) Like "Wet**") AND ((Orders.SignOff)=True))
ORDER BY SampleDetails.DueDate;

I have used the Ecount function before similar to as above... It was SLOW,
but worked.. why isn't it working now????

Thanks,

LDA

p.s. how can i make calculation faster?

 

[John Spencer MVP]

Try surrounding the table name (2nd argument) with brackets. You have special
characters (dashes) in there that will get interpreted as minus (subtraction)
operators.

=ECount("[test]","[DE_BACKLOG07-21-2008_BK0094]")
=eCount("[samplenumber]","[DE_BACKLOG07-21-2008_BK0094]","samplenumber",True)
=eCount("[orderid]","[DE_BACKLOG07-21-2008_BK0094]","samplenumber",True)


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

 

[LDA via AccessMonster.com]

thanks that worked perfectly!!!!

The way it is written now, am I optimizing the calculation time??? upon
opening, it is taking some time to open reports that have the Ecount
experession has described above


LDA

Try surrounding the table name (2nd argument) with brackets. You have special
characters (dashes) in there that will get interpreted as minus (subtraction)
operators.

=ECount("[test]","[DE_BACKLOG07-21-2008_BK0094]")
=eCount("[samplenumber]","[DE_BACKLOG07-21-2008_BK0094]","samplenumber",True)
=eCount("[orderid]","[DE_BACKLOG07-21-2008_BK0094]","samplenumber",True)

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

From my report, Wetlab_Backlog, I am getting the following errors: "syntax
error in from clause" for all three fields were i use ECount.

[quoted text clipped - 32 lines]

p.s. how can i make calculation faster?

 

[John Spencer MVP]

If Ecount is doing the job this may be as fast as you can get. Make sure you
have indexes on the fields Test, SampleNumber, and OrderID. Lack of indexes
will definitely slow things down.

A query that calculates all the values you need and is joined to your current
query MIGHT be faster, but without having a copy of your database all I can
suggest is you experiment. First thing I would do is make sure that the
ECount is responsible for the delays you are seeing. Make a copy of your
report and remove the ECount from the report - is it significantly faster then
you know you need to work on replacing the ECount (if possible) or optimizing
the Ecount (if possible).

Good Luck

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

thanks that worked perfectly!!!!

The way it is written now, am I optimizing the calculation time??? upon
opening, it is taking some time to open reports that have the Ecount
experession has described above


LDA

Try surrounding the table name (2nd argument) with brackets. You have special
characters (dashes) in there that will get interpreted as minus (subtraction)
operators.

=ECount("[test]","[DE_BACKLOG07-21-2008_BK0094]")
=eCount("[samplenumber]","[DE_BACKLOG07-21-2008_BK0094]","samplenumber",True)
=eCount("[orderid]","[DE_BACKLOG07-21-2008_BK0094]","samplenumber",True)

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

From my report, Wetlab_Backlog, I am getting the following errors: "syntax
error in from clause" for all three fields were i use ECount.

[quoted text clipped - 32 lines]

p.s. how can i make calculation faster?