Dlookup Problem

[Viv Jones]

I am trying to perform a lookup which is returning an error - attached.

I have a bound text box on the form called RatingNCB, I also have an unbound text box called SILookup on the form as well. SILookup is populated depending on a value entered into another field on the form. This part is working justfine.

I am trying to perform a lookup in a table (attached excek spreadsheet) that uses the value of the RatingNCB field on the form as the column reference or lookup and the SILookup as the criteria or row reference.

For example - if RatingNCB on the form = Rating1 and SILookup on the form = 3, then it should return $3,750.00 but is returning an error instead.

I have a suspicion it is something to do with the syntax, but nothing I try is working.

Any assistance would be greatly appreciated.

Sub CalcPremium()

Dim RatingCode As String

RatingCode = "[" & Me.[RatingNCB] & "]"

Forms!frm_Individual!LookupPrem.Value = dlookup(RatingCode, "tbl_Rates", "ID=Forms!Frm_individual!SILookup")
     
End Sub

[imgnothumb]http://bytes.com/attachments/attachment/6833d1358134695/dlookup-error.jpg[/imgnothumb]

[zmbd]

at first blush, line 7 of the code is malformed.
It appears that you are trying to return a value from your form; however, "ID=Forms!Frm_i ndividual!SILoo kup" will not return the value from the form. Instead you are getting EXACTLY what is between the quotes.

Instead you need something like:
"ID=" & Forms!Frm_indiv idual!SILookup. value)

or if a string:
"ID= '" & Forms!Frm_indiv idual!SILookup. text & "'")

or if a date:
"ID= #" & Forms!Frm_indiv idual!SILookup. text & "#")

It is my preference to always build my strings first for these types of functions or SQL etc... it makes it easier to check that something hasn't messed up in that I can do a debug.print, set a watch, check for missing entries, and so much more.

Speaking of that debug.print... you should insert:
debug.print RatingCode right after line 5 and see what exactly your string is resolving to as that also appears to be suspect.

[NeoPa]

Not a bad attempt at a question Viv. Pretty well expressed. However, you may find the tips at Before Posting (VBA or SQL) Code not only help you post questions even better, but also might help you find some of the more basic problems without even the need to post.