I am working on a database in which I must check the records in one table against the records of another table, and return a list of the one's that don't match. The tables contain salary accounting information. For each employee, they can have up to 5 fund/orgn/acct combinations, with a different percentage of their salary coming from each of the fund/orgn/acct combinations. In one table (call it Table A), there is just 1 record for each employee, with 5 sets of columns for the fund/orgn/acct. In the other table (call it Table B), there is 1 record for each fund/orgn/acct combo for each employee.
After thinking through how I could do this, the easiest way I could think of was to write a VBA function that is passed the employee ID and the fund/orgn/acct combos from Table B, and uses DCount() to check this against each of the 5 sets of columns for the fund/orgn/acct combos. If it finds a match, the function returns true; if, after checking all 5 sets of columns, it fails to find a match, the function returns false. In my query, I would like to include this as the criteria - I want to view all records where this query returns false. However, anytime I set I enter criteria on this function, Access gives me a "Data type mismatch" error. I've spent the last several hours trying a variety of things to trouble shoot this and I can't figure it out. I've switched the function's return type to integer (rather than boolean, since that returns either a 0 or -1 anyway), and sure enough, it returns an integer. If I do a make table out of my query, the resulting field is of type integer. Within my query, I can add numbers to the function's return - so clearly, it is returning an integer. However, no matter how I type in my criteria (i.e. 0, = 0, '0', = '0', "0", = "0"), I still get a datatype mismatch error. I've tried turning this into a multiple-step query as well, but I'm still having no luck.
Does anyone have any ideas for what is causing this?
After thinking through how I could do this, the easiest way I could think of was to write a VBA function that is passed the employee ID and the fund/orgn/acct combos from Table B, and uses DCount() to check this against each of the 5 sets of columns for the fund/orgn/acct combos. If it finds a match, the function returns true; if, after checking all 5 sets of columns, it fails to find a match, the function returns false. In my query, I would like to include this as the criteria - I want to view all records where this query returns false. However, anytime I set I enter criteria on this function, Access gives me a "Data type mismatch" error. I've spent the last several hours trying a variety of things to trouble shoot this and I can't figure it out. I've switched the function's return type to integer (rather than boolean, since that returns either a 0 or -1 anyway), and sure enough, it returns an integer. If I do a make table out of my query, the resulting field is of type integer. Within my query, I can add numbers to the function's return - so clearly, it is returning an integer. However, no matter how I type in my criteria (i.e. 0, = 0, '0', = '0', "0", = "0"), I still get a datatype mismatch error. I've tried turning this into a multiple-step query as well, but I'm still having no luck.
Does anyone have any ideas for what is causing this?
