What am i doing wrong?

[Sam Summers]

Hi,

I have attached a screenshot of the error message and the SQL that is throwing the error which will explain my problem to someone who understands my mistake?

Many thanks in advance

Sam

 

[kyo]

hi,
please change having clause to where clause

 

[Sam Summers]

Hi, I tried that and it didnt work?

So i changed it to the SQL below. I no longer get an error message but i get no results?

SELECT Employee.National, Employee.MobDate, Employee.DeMobDate,
DCount("*","[Employee]","[National]=No") AS Expr1
FROM Employee
GROUP BY Employee.National, Employee.MobDate, Employee.DeMobDate,
DCount("*","[Employee]","[National]=No")
HAVING (((Employee.National)=No)
AND ((Employee.MobDate) Between Date() And Date()-7)) OR (((Employee.National)=No)
AND ((Employee.DeMobDate) Between Date() And Date()-7));

 

[plog]

Actually, you probably need to scrap that query entirely and explain what you are trying to do. You've got some inconsistent elements to your query.

1. You are using a DCOUNT in a AGGREGATE query. DCOUNT returns a count of fields in a table, but an AGGREGATE query allows that same functionality. It's probably redundant to use both.

2. All the fields in the GROUP BY clause are in the SELECT clause. And since every Expr1 is going to return the same value, you are just generating a list of unique National values. Usually you use an aggregate function on one or more of the fields in the SELECT clause and not include those fields in the GROUP BY clause.

You're query is really inconsistent, and even when you do get it to work, I bet it won't return the values you expect. So, provide us with some sample data from Employee (include field names) and then provide what you expect as the results from that sample data. Use this format:

Employee
Field1Name, Field2Name, Field3Name
David, 134, 5/15/2011
Larry, 435, 6/12/2008
Sally, 12, 1/1/2004

 

[namliam]

Dont want to be mean or anything, how would you expect to get any results with a:
Between Date() and Date()-7
????

Do take note of Plog's post as well

 

[Sam Summers]

I am SO lost now!

OK, this is the other query i am using that works and gives me the total of all personnel:

SELECT Employee.National, DCount("*","[Employee]","[National]=No") AS Expr1
FROM Employee
GROUP BY Employee.National, DCount("*","[Employee]","[National]=No")
HAVING (((Employee.National)=No));
_____________________________________________________________

What i need now is another query that gives me only those employees that satisfy these parameters:

(((Employee.National)=No)
AND ((Employee.MobDate) Between Date() And Date()-7)) OR (((Employee.National)=No)
AND ((Employee.DeMobDate) Between Date() And Date()-7));
_________________________________________________________________

And then after that i need to create a textbox with the result of the two as a percentage?

As far as the Between Date() And Date()-7) is concerned, how else do i display all dates between the current date and the past week? The report is a weekly report?

Thank you everyone

 

[jdraw]

Sam,

You're lost because you are too close to the problem and
grasping -- in my view.

Here's a site with sample databases and tutorials. Try a few, then get back to your issue - I'm sure you'll benefit.
Try it, and post back.

 

[namliam]

Try this for your first query to total all...
SELECT Employee.National, Count(Employee.National) AS Expr1
FROM Employee
GROUP BY Employee.National, DCount("*","[Employee]","[National]=No")
HAVING (((Employee.National)=No));

well lets try and display your problem a little differently:
Between 10 and 3
or writing it the way the database thinks
>= 10 and <= 3

Perhaps now you can see that this will never return any records.