Strange DCount issue

[midmented]

Upgraded from Access 2003 to Access 2010. My DCount query was working fine in Access 2003 for many years but now I am having issues. Everything is written in VBA.

Routine:
1.) Insert records into table tblEmployee
2.) Query table tblEmployee to count how many employees are in each department using DCount.

Issue:
DCount returns 0 for all Departments.

Findings:
If I manually open table tblEmployee and edit an employees department (I actually named it the same department), DCount will then find 1 person in that department (the record I manually edited). It seems a manually edited record will be counted but anything written by the insert query is ignored.

 

[vbaInet]

Can you show us how you do 1)? And also the exact DCount() code for 2)

 

[midmented]

All of the records are successfully being inserted in step 1. The Insert query is written using VBA based upon user selection from checkboxes on a form (AKA: lengthy)

step 2, all of the DCounts work except for Department

DCount("[Department]", "tblEmployee", "[Department]='Central Supply'")

the above dcount always returns 0 for every department even though the department field does have data for each employee. If I physically open the table and edit an employees department from Central Supply to Central Supply and close the table, DCount will then count that 1 record I changed even though there are 3 employees in Central Supply. The other 2 employees in Central Supply are ignored by DCount unless I manually open the table and change their department from Central Supply to Central supply.

Understand?

 

[midmented]

DCount works but only if I manually update the department.

 

[vbaInet]

All of the records are successfully being inserted in step 1. The Insert query is written using VBA based upon user selection from checkboxes on a form (AKA: lengthy)

Let's see the resultant SQL string that's built up and which methods you use to execute the query, i.e. Execute() or RunSQL(). Please enclose them in code tags... see here for more.

 

[midmented]

It's an update query

strSQl = "UPDATE tblStaff INNER JOIN tblEmployee ON tblStaff.SSnbr = tblEmployee.SSNbr SET tblEmployee.Department = [tblStaff].[Department];"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQl)
DoCmd.SetWarnings True

 

[vbaInet]

You mentioned that the update is based on a user selection, but I don't see this criteria in your SQL. Can you explain?

Also try this instead:

strSQl = "UPDATE tblStaff INNER JOIN tblEmployee ON tblStaff.SSnbr = tblEmployee.SSNbr SET tblEmployee.Department = [tblStaff].[Department];"

CurrentDb.Execute strSQl, dbFailOnError

Or even better, put that in a query and Execute the query.

 

[midmented]

If I run:

?dcount("*","tblEmployee","[Department]='Central Supply'")

in the immediate window, it returns as 0, yet there are 3 records in the Department Field with "Central Supply".
If I physically open tblEmployee, and change Department on 1 of those "Central Supply" records, close tblEmployee. Then run the same code above in the immediate window, it returns 1.

It makes no sense to me.

 

[vbaInet]

I think you missed my last post, please have a look.

Also, do you have Relationships set up?

 

[midmented]

Sorry, I ran everything manually using query wizard, leaving out all of the code for the criteria selection just in case something was messed up in the criteria code.
How: Deleted all the records in tblEmployee, and simply added 3 unique ID's under SSNbr field leaving all other fields blank. Ran the update query I posted. Easier than running through all of that code.

Same result. DCount not counting records when I use a query to update records yet working when I manually change department.

 

[vbaInet]

Also, do you have Relationships set up?

And relationships?

I would imagine that you will encounter the same problem with any of the domain aggregate functions because, I suspect that your database just hasn't been refreshed after those records were updated.

 

[midmented]

I just checked relationships. There is a relationship from SSNbr to SSNbr between tblStaff and tblEmployee.

 

[vbaInet]

And you've tried all the methods proposed, i.e. executing a query instead of the SQL statement?

Is this a split db?

 

[midmented]

Yes, I deleted all of the records in tblEmployee, manually added 3 records (unique ID's only), then ran an actual update query (not SQl statement). The 3 records updated to the correct department. I ran the DCount in the immediate window, and it still came back as 0 yet there are 3 records in the table matching the criteria.
I opened the table manually, edited 1 records department to the same exact text, ran the DCount in the immediate window, and it returned 1.

No, it is not a split database (yet). L

 

[vbaInet]

Strange occurrence.

Can you upload a cut down version so I can have a look? Include some test (fictitious) data and only include the relevant bits.

 

[midmented]

oh my!
Guess what, there IS a space at the beginning of every single department. Dang, so sorry.
It is an Excel Import so it looks as if I will need to write code to get rid of the blank space.
I was looking for blank space at the end this whole time.
Hours wasted and it was right in front of my face..........

thanks!

 

[midmented]

Gave you the approved rep......
thanks so much for thinking it through with me.

 

[vbaInet]

I should have thought about that, but I just thought that it was too trivial to miss

Sometimes, it's the easiest things that we miss. Glad you figured it out.