Form but actually Dlookup.... (1 Viewer)

[TajikBoy]

Hi Gurus,

Me again, trouble maker ....

Yet another Dlookup related problem...

This is what I am trying to achieve...A table with propertyID/Year/Department/Position/Salary details to compare 3 property's salary details by department and Position. Created the form, in header you select the property/department and year and detail lists the available department,positions and salaries based on Main selection, and next 2 fields should pickup from the same table based on the variables selected in the header other salaries for the same department and positions....But they dont.....

What Am I doing wrong? Dlookup formula is based on the same one you guys kindly corrected (and caused my self a concussion and a broken KB..)

Much appreciate your help once again, and million thanks in advance,

DB attached

 

[Pat Hartman]

The criteria for your query needs to be
Where (Department = [Forms]![propSalaryScale]![Department1] AND strYear = [Forms]![propSalaryScale]![Year1])
AND ( Properid = [Forms]![propSalaryScale]![Property1] OR PropertyID = [Forms]![propSalaryScale]![Property2] OR PropertyID = [Forms]![propSalaryScale]![Property3]

Save this query and create a crosstab from it to so that you can see the values side by side for each Property.

 

[bob fitz]

For Salary2, try:
=DLookUp("Salary","tblSalaryScale","Department = '" & [Department] & "' And Position = '" & [Position] & "' And strYear = " & [Year2] & " And PropertyID = " & [Property2])

 

[TajikBoy]

For Salary2, try:
=DLookUp("Salary","tblSalaryScale","Department = '" & [Department] & "' And Position = '" & [Position] & "' And strYear = " & [Year2] & " And PropertyID = " & [Property2])

Bob, worked like a charm....

and again quote marks...I need to study and learn this quotes.....but these days feel like Homer SImpson, when I learn somethig new, old stuff gets pushed out

 

[TajikBoy]

The criteria for your query needs to be
Where (Department = [Forms]![propSalaryScale]![Department1] AND strYear = [Forms]![propSalaryScale]![Year1])
AND ( Properid = [Forms]![propSalaryScale]![Property1] OR PropertyID = [Forms]![propSalaryScale]![Property2] OR PropertyID = [Forms]![propSalaryScale]![Property3]

Save this query and create a crosstab from it to so that you can see the values side by side for each Property.

Hi Pat,

Would you mind explaining above as if you would do to a kid? Queries/SQL etc are my very weak points, and although I understand th eprinciple behind, in practise no idea

 

[Pat Hartman]

Domain functions each execute a separate query. That makes them inefficient when used in queries or loops.

As long as Bob fixed the error, you probably can go with your current solution.

 

[TajikBoy]

Domain functions each execute a separate query. That makes them inefficient when used in queries or loops.

As long as Bob fixed the error, you probably can go with your current solution.

Continuing on this thread, I think I managed to create the crosstab query Pat was suggesting, DB attached

Now, issue is the column headings and filtering

I have inserted the criteria based on the form variables, and fixed the column headings (only way I managed to make it work..) - when I chose from dropdowns, query window disappears and comes back with nothing (I know there's data to display)

Also, column headings are driven by PropertyID, which has corresponding property name in tblPropertyRegistrations, is there anyway we can link those? (this is not a biggy, ideallly I want to display Property 1,2,3 and limit to the selection above in dropdowns)

I appreciate your time and assistance on this and million thanks