DCount syntax problem (1 Viewer)

[stretch66]

Hi,

Having a problem expanding this expression:

ContractA: DCount("[EmpKey]","tblEmployees","[StartDate]<#01/05/05#" & " AND [LeftDate] Is Null")

That works great but when do the expression below says syntax wrong......WHY?


ContractB: DCount("[EmpKey]","tblEmployees","([StartDate]>=#01/05/05# AND <=#31/12/05#) " & " AND [LeftDate] Is Null")

Really need to work on my SQL!!!!!!!!

 

[RuralGuy]

Either:
ContractB: DCount("[EmpKey]","tblEmployees","([StartDate] Between #01/05/05# AND #31/12/05#) AND [LeftDate] Is Null")
OR:
ContractB: DCount("[EmpKey]","tblEmployees","(([StartDate] >= #01/05/05#) AND ([StartDate] <=#31/12/05#)) AND [LeftDate] Is Null")

 

[stretch66]

Am just getting a zero on the count for ContractB ?

 

[Rod D]

Hi Stretch

I think your problem may lie in the ...AND [LeftDate] is Null statement.
To test this idea, delete this part and see if you get a value returned, if it does, then try using ...AND ISNull([LeftDate]) instead.

Access can be very "picky" when using Null.

HTH
Rod

 

[stretch66]

Hi Rod,

Took the Is Null out like you said and did come up with a figure. So I have:

ContractA: DCount("[EmpKey]","tblEmployees","[StartDate]<#01/05/05#" & " AND [LeftDate] Is Null")

ContractB: DCount("[EmpKey]","tblEmployees","(([StartDate] >= #01/05/05#) AND ([StartDate] <=#31/12/05#)) AND [LeftDate] ")

ContractC: DCount("[EmpKey]","tblEmployees","[StartDate]>=#01/01/06#" & " AND [LeftDate] Is Null")


The complete SQL is:

SELECT tblEmployees.EmployeeID, tblDepartment.DepartmentID, tblDepartment.DepartmentName, tblEmployees.StartDate, Count(tblEmployees.EmpKey) AS CountOfempKey, tblPosition.Position, tblGrade.Grade, tblEmployees.Last_Name, tblEmployees.LeftDate, tblEmployees.First_Name, tblEmployees.Last_Name, Date() AS [Date], DCount("[EmpKey]","tblEmployees","[StartDate]<#01/05/05#" & " AND [LeftDate] Is Null") AS ContractA, DCount("[EmpKey]","tblEmployees","(([StartDate] >= #01/05/05#) AND ([StartDate] <=#31/12/05#)) AND [LeftDate] ") AS ContractB, DCount("[EmpKey]","tblEmployees","[StartDate]>=#01/01/06#" & " AND [LeftDate] Is Null") AS ContractC
FROM tblGrade INNER JOIN (tblPosition INNER JOIN (tblDepartment INNER JOIN (tblEmployees INNER JOIN tblJobStatus ON tblEmployees.Job_Status = tblJobStatus.JobStatusNumber) ON tblDepartment.DepartmentID = tblEmployees.DepartmentID) ON tblPosition.PositionID = tblEmployees.PositionID) ON tblGrade.GradeID = tblEmployees.GradeID
GROUP BY tblEmployees.EmployeeID, tblDepartment.DepartmentID, tblDepartment.DepartmentName, tblEmployees.StartDate, tblPosition.Position, tblGrade.Grade, tblEmployees.Last_Name, tblEmployees.LeftDate, tblEmployees.First_Name, tblEmployees.Last_Name
HAVING (((tblEmployees.LeftDate) Is Null))
ORDER BY tblEmployees.Last_Name, tblEmployees.Last_Name;

Still have a syntax problem putting the Is Null in front of the [LeftDate]

What gets me is why nothing is happening when Is Null is added as a criteria in the SQL Table??

 

[Rod D]

Hi Stretch
Sorry to hear you're still having problems -
Without a copy of the DB it is impossible to test the SQL, but some things I have noticed...

The SQL ..HAVING (((tblEmployees.LeftDate) Is Null))... part of the
statement restricts the recordset to employees that have not left yet (which I assume is correct).

When you get the syntax error, does the statement read :

...DCount("[EmpKey]","tblEmployees","(([StartDate] >= #01/05/05#) AND ([StartDate] <=#31/12/05#)) AND IsNull([LeftDate])") AS ContractB...

As your post quotes this section as

...DCount("[EmpKey]","tblEmployees","(([StartDate] >= #01/05/05#) AND ([StartDate] <=#31/12/05#)) AND [LeftDate] ") AS ContractB...

which will not work as the SQL is expecting ...AND [LeftDate] = Something and hence the error.

Please make the change and let us know what happens.
Regards
Rod

 

[stretch66]

Have attached a test DB with what I'm trying to achieve if that helps anyone please?!

 

[stretch66]

Rod,

Sorry posted without reading. Have had a number of errors including syntax and calculation, now getting zero for contract B using both codes.

 

[stretch66]

This worked ....the only difference from RuralGuys example is the double brackets around the between statement.

DCount("[EmpKey]","tblEmployees","(([StartDate] Between #05/01/05# AND #12/31/05#)) AND [LeftDate] Is Null ")AS ContractB

 

[Rod D]

Glad to hear you got it sorted out.

 

[RuralGuy]

Excellent Stretch66! Good show.

 

[stretch66]

Actually found out this morning it doesn't work, just that the dates were wrong and so the figures actually looked about right.

 

[stretch66]

Arrrrgghhh!!!!

Please can someone put me out of my misery have been on this for three days and can't get on with anything else until solved.

Have revised the test database that will run the type of report that I am looking for. As you will see have two examples of each critera in the table but is still not picking up the ContractB person.

 

[RuralGuy]

Here's a couple of links you should find useful.
International Dates in Access
Return Dates in US #mm/dd/yyyy# form