DLookup Multiple Criteria (Data type error)?

[sergi117]

Hey I was working on a dlookup which gives a result based on multiple criteria, but it seems to be giving me a datatype error. I don't know why both criteria are short text, if anyone has any idea?

Vac: DLookUp("VAC","CALC","[CALC].[AL Corr Std Hours] = '" & [Example].[AL Corr Std Hours] & "' And [calc].[FLSA OT] = '" & [Example].[FLSA OT] & "'")

MY SQL is:

SELECT Example.Employee_ID, Example.Employee_Name, EMPLOYEE_DATA.[Hire Date - Employment Dta], Example.Code, Example.[Eff Date], Example.Action, Example.[AL Status], Example.[FLSA OT], Example.[AL Type], Example.[Rate 1], Example.[Rate 2], Example.Annual, Example.[Std Hours], Example.[AL Std Hours], Example.[AL Corr Std Hours], IIf([Example].[Eff Date]-[employee_data].[Hire Date - Employment Dta]>=5,"6+","<6") AS Years, DLookUp("VAC","CALC","[CALC].[AL Corr Std Hours] = '" & [Example].[AL Corr Std Hours] & "' And [calc].[FLSA OT] = '" & [Example].[FLSA OT] & "'") AS Vac
FROM CALC, Example, EMPLOYEE_DATA
WHERE (((Example.employee_name)=[employee_data].[Name - Personal Dta]))
GROUP BY Example.Employee_ID, Example.Employee_Name, EMPLOYEE_DATA.[Hire Date - Employment Dta], Example.Code, Example.[Eff Date], Example.Action, Example.[AL Status], Example.[FLSA OT], Example.[AL Type], Example.[Rate 1], Example.[Rate 2], Example.Annual, Example.[Std Hours], Example.[AL Std Hours], Example.[AL Corr Std Hours], IIf([Example].[Eff Date]-[employee_data].[Hire Date - Employment Dta]>=5,"6+","<6"), DLookUp("VAC","CALC","[CALC].[AL Corr Std Hours] = '" & [Example].[AL Corr Std Hours] & "' And [calc].[FLSA OT] = '" & [Example].[FLSA OT] & "'");

Thanks everybody!

[CJ_London]

"......[CALC].[AL Corr Std Hours] = '" & [Example].[AL Corr Std Hours] & "'...…...

std hours implies a number - you are supplying a text value by using the single quotes

also your dlookup is being aliased as Vac - the same name as the field in your main query which may be causing a problem.

Why use dlookup anyway, why not just use a join?

using spaces and non alpha numeric characters in field names can also cause problems

[sergi117]

I did set up a set of iif functions to test the field value and give a proper result, but as it stands it is rather tedious with how many rules factor in to the iif statements. I've attempted separating the iif statements out, and union each one removing the nulls. Again it is tedious but it does work. The hours aren't the problem as without the flsa other, the dlookup runs. It seems to be when testing with the flsa ot that the results lead to a data type error.

[CJ_London]

the error message is telling you that you are trying to match a string to a number or date or the other way round. Quite simple. The only field name that I can understand is std hours, no idea what the others are so no idea if they are the right datatypes. I don't know what the query is supposed to do and I don't know why you are grouping it since you are not aggregating any fields.

but you know what the problem is so this

The hours aren't the problem as without the flsa other, the dlookup runs.

should give you a massive clue as to where the problem is.

[sergi117]

I've solved the issue (created a bit of a new database separate just so I can look at some key issues) In this case I used a years category, but it also seems to work for the other one as well. Seemed to be that one had been written as a numeric rather than in terms of a string and vice versa. Though now I seem to get duplicates, and I'm unsure as to why. The dlookup gives the right outcome, but the overall record appears twice.
Simplified example:
Name 1_____Dlookup Value 1
Name 1_____DLookup Value 1
Should only see one of these.

SELECT Query1.EmployeeID, Query1.Name, Query1.[Hire Date - Employment Dta], Query1.[Effective Date], Query1.Action, Query1.[AL Status], Query1.[FLSA OT], Query1.[AL Type], Query1.[Rate 1], Query1.[Rate 2], Query1.Annual, Query1.[Std Hours], Query1.[AL Std Hours], Query1.[Al Cor Std Hours], Query1.Years, DLookUp("Vac","Sheet2","[sheet2].[accr hours]=" & [table1].[Al Cor std Hours] & " AND [sheet2].[years] = '" & [Query1].[Years] & "'") AS Vac
FROM Query1;

DLookUp("Vac","Sheet2","[sheet2].[accr hours]=" & [table1].[Al Cor std Hours] & " AND [sheet2].[years] = '" & [Query1].[Years] & "'")

Anybody ever have this issue before?

[CJ_London]

dlookups can only return one value, you problem is with your query returning more than one record