access forms dlookup field from table with between criteria

[Jims Chan]

Hi all,



keep trying on the dlookup but can't get the results... searching on forums still couldn't solve, hope experts here could help me. thanks in advance

i have a table call loading table

ID	NoOfYear	Loading
1	6	0.00%
2	10	15.00%
3	11	20.00%

while a form data entry, which have [CurrentDate],[YearOfMake],[MotorAge] and [Loading] where [loading] need to lookup from loading table
with criteria motor age determine the loading percentage

Private Sub YearOfMake_AfterUpdate()
Me.MotorAge.Value = Year([CurrentDate]) - [YearOfMake]
Me.Loading.Value = DLookup("[loading]", "loading", "[noofyear]>" & Me.MotorAge & " and [noofyear]<=" & Me.MotorAge & "")
End Sub

[orange]

You have a field in your table with the same name as the table name?
NoOfYear in your post is left justified which signifies a text field.
So in your Dlookup I think you'll need quotes to enclose your variables for proper syntax.
DLookup("[loading]", "loading", "[noofyear]>'" & Me.MotorAge & "' and [noofyear]<='" & Me.MotorAge & "'")

However, I think you are trying to do arithmetic so you'll need numbers.
How are the fields/columns defined in your loading table?

[Jims Chan]

You have a field in your table with the same name as the table name?
NoOfYear in your post is left justified which signifies a text field.
So in your Dlookup I think you'll need quotes to enclose your variables for proper syntax.
DLookup("[loading]", "loading", "[noofyear]>'" & Me.MotorAge & "' and [noofyear]<='" & Me.MotorAge & "'")

However, I think you are trying to do arithmetic so you'll need numbers.
How are the fields/columns defined in your loading table?

hehehe... i still newbie in access so a lot of thing still in learning...
i had try ur advice and there is a RUN TIME ERROR '3464' (DATA TYPE MISMATCH IN CRITERIA EXPRESSION)
so what should i do next???

[orange]

How are the fields/columns defined in your loading table?

I think you are trying to do arithmetic with different data types (text and numeric) and that's why you got the error message.

[CJ_London]

I don't think

a) dlookup is the right function to use
b) your criteria requires NoofYear to be both less than and greater than or equal to motorage - which is impossible

Assuming Noofyear is a number (if it was text then the 'order' would 10,11,6) then if you want the loading for a vehicle aged say 8 years to pick up the 10 year value try

=DMin("[loading]", "tblloading", "[noofyear]>=" & Me.MotorAge)

and if you wanted it to pick up the 6 year value then you would use

=DMax("[loading]", "tblloading", "[noofyear]<=" & Me.MotorAge)

you also need to think about what happens if the motorage is less than 6 or more than 11