Dlookup with multiple criteria including dates

[jtolsma]

I am stuck on the following code. I keep trying to make changes to it and continue to get errors. The code listed below gives me a type mismatch error. (I have seen syntex errors also)
Any suggestions on what I am doing wrong? (The Code is text, EmpNum is a number and the other is a date. I have checked the tables this relates to and the formatting matches)



Code1 = DLookup("[Code]", "Attendance Data", "[EmpNum]= " & Forms![Warning Data]!EmpNum & """" And "[AttendanceDate]= #" & Forms![Warning Data]![AttendanceDate1] & "#")

Thanks for all of your help
Jessica

[pbaldy]

See if the multi-criteria at the end of this helps:

DLookup Usage Samples

FYI, the data type of Code is irrelevant. You've got a bunch of extraneous quotes there.

[jtolsma]

I have referred to that link a number of times, even when trying to put this command together and still can't seem to get it right. You mentioned that I have a bunch of extraneous quotes so I tried some modifications, listed below, but they still aren't correct.
Code1 = DLookup("[Code]", "Attendance Data", "[EmpNum]= " & Forms![Warning Data]!EmpNum And "[AttendanceDate]= #" & Forms![Warning Data]![AttendanceDate1] & "#") RESULT - Error 13
Code1 = DLookup("[Code]", "Attendance Data", "[EmpNum]= " & Forms![Warning Data]!EmpNum And "[AttendanceDate]= #" & Forms![Warning Data]![AttendanceDate1]) RESULT - Error 13
Code1 = DLookup("[Code]", "Attendance Data", "[EmpNum]= " & Forms![Warning Data]!EmpNum & """" And "[AttendanceDate]= #" & Forms![Warning Data]![AttendanceDate1]) RESULT - Error 13
Code1 = DLookup("[Code]", "Attendance Data", "[EmpNum]= " & Forms![Warning Data]!EmpNum & "" And "[AttendanceDate]= #" & Forms![Warning Data]![AttendanceDate1]) RESULT - Error 13
Code1 = DLookup("[Code]", "Attendance Data", "[EmpNum]= " & Forms![Warning Data]!EmpNum & "" And "[AttendanceDate]= #" & Forms![Warning Data]![AttendanceDate1] & "#") RESULT - Error 13

I feel like I am missing something simple, which is why I am looking for an extra set of eyes for help.
Thanks
Jessica

[pbaldy]

Always too many or not enough. Try this:

DLookup("[Code]", "Attendance Data", "[EmpNum]= " & Forms![Warning Data]!EmpNum & " And [AttendanceDate]= #" & Forms![Warning Data]![AttendanceDate1] & "#")

which assumes EmpNum has a numeric data type.

[jtolsma]

I tried that and I get a Syntax error

[pbaldy]

Can you post the db here?

[jtolsma]

I'm sorry, I am not sure what you are asking.

[pbaldy]

I'm asking if you can attach your database to a post here so I can debug what's going wrong with it.

[jtolsma]

Sorry...I am having a brain dead day
I had to take alot of data out to make it small enough, but I think this contains everything you need.

Database3.accdb

I hope I attached that correctly
Thanks

[pbaldy]

Sorry, I was just correcting the DLookup. You still need the "Code1 = " part, or whatever it is you want to do with the value.

By the way, your data is not normalized, with the repeating fields (code1, code2, etc). Typically that should be in a related table as records rather than repeating fields.

[jtolsma]

Thank you so very much. You solved everything!
I am not thrilled with the way I have to set this database (I only sent you a very small portion of it), but I have to do it in a way my boss can understand.
Thank you again

[pbaldy]

Happy to help. That design will be difficult to work with I'm guessing. Thank goodness I work for a boss that doesn't worry about "how", just that the app does what it's supposed to do. If the boss doesn't understand proper design, they shouldn't be involved in it.