Dlookup to match two criteria and return value

[randolphoralph]

I have created some code and can not seem to get it to work correctly.



If the Ref Workload field is <> 0 the code is suppose to return the Ref Workload value from the Employee Table where the Employee ID and Date of Work field matches the same as the currrent record.

Employee ID: Number field
Date of Work: Date/Time field
Ref Workload: Number field

 
Private Sub Employee_ID Change()
Dim RefWorkload As Integer
RefWorkload = Nz(DLookup("Ref_Workload", "Employee Table", "[Employee ID] = " & Forms![Employee Table]![Employee ID] & " AND [Date of Work] =#" & Forms![Employee Table]![Date of Work] & "#"), 0)
If Len(Me.Ref_Workload & "") <> 0 Then
Me.Ref_Workload = RefWorkload
End If
End Sub

[pbaldy]

The table name may need to be bracketed due to the inadvisable space.

[randolphoralph]

I have put the table name in brackets....I am getting a Run Time Error 13 (Type Mismatch) on the line I bolded below.

 
Private Sub Employee_ID Change()
Dim RefWorkload As Integer
RefWorkload = Nz(DLookup("Ref_Workload", "[Employee Table]", "[Employee ID] = " & Forms![Employee Table]![Employee ID] & " AND [Date of Work] =#" & Forms![Employee Table]![Date of Work] & "#"), 0)
If Len(Me.Ref_Workload & "") <> 0 Then
Me.Ref_Workload = RefWorkload
End If
End Sub

[pbaldy]

You're sure of the data types in the table? Can you post the db?

[randolphoralph]

pbaldy,

I have double checked the data types again to be sure that I had posted them correct. I would upload the DB for you but I am currently at work and can not upload due to admin security preventing uploads. If you have any other thoughts I can try if not then I can upload when I get home.

[pbaldy]

Well, the syntax looks correct for those data types, and that error usually indicates a data type mismatch. What kind of value does Ref_Workload contain? Is it possible it's too large for Integer (roughly 32k)?

[randolphoralph]

Ref_Workload contains numbers ranging between 1 and 500.

[randolphoralph]

I looked at Ref_Workload in the table and it is set to Number (Long Integer) not sure if that makes a difference.

[pbaldy]

I doubt it matters but you can try changing this to match:

Dim RefWorkload As Long

Other than that I'm stumped, so if you can post the db later it usually helps to be able to "put your hands on it".

[randolphoralph]

Ok after I added the brackets and did a compact and repair I am no longer getting the error message....but it appears that RefWorkload is being returned as 0 results matching....even when I have records that have the same Employee ID and Date of Work. I am so lost.

[pbaldy]

I would set a breakpoint and make sure the values coming from the form are what you expect:

http://www.baldyweb.com/Debugging.htm

[randolphoralph]

I have not used the Locals Window before and the only thing I saw was the value for RefWorkload = 0.

I could not find the Employee ID and Date of Work.

[pbaldy]

If you set a breakpoint so that the code stops during execution, you should be able to hover over those with the mouse and see the value, or type things like this in the Immediate window:

?Forms![Employee Table]![Employee ID]

which should return the value from the form.

[randolphoralph]

Ok I have found a computer I can upload the DB from.....

I also, noticed that if I enter the Employee ID and then go back and change it I still get the Data Type Mismatch.

[pbaldy]

It seems to be returning correct results for me, but I did change one thing. You have it in the change event, which fires with each keystroke and unless you specify the .Text property, uses the previous value in the control. You want the after update event. Try that and see where we are.