DLookup on error Form

[Flank]

I have used the DLookup function many times in VBA, but I am trying to use it in a slightly different manner this time and I can't figure out wheat I am missing.

I have a table called MarketRates, it has 2 fields; CCode and Rates
I have a form that I want to look up the rate based on the code and enter it in the box.

Here is my code based on button click

Private Sub CalcRate_Click()
Me.SADM20 = DLookup("[Rate]", "MarketRates", "CCode=" & (SADM20))
End Sub

There error I get is syntax error, missing operator.

If I write it like this:

Private Sub CalcRate_Click()
Me.SADM20 = DLookup("[Rate]", "MarketRates", "CCode=SADM20")
End Sub

It doesn't return the value thats in the rates column, just blank.

 

[boblarson]

You're just slightly off:

Me.SADM20 = DLookup("[Rate]", "MarketRates", "CCode='SADM20'")

You needed the quotes because it is text (see the big red part - made it big so you could see them better).

 

[plog]

I think your making a circular reference. It seems you want to use the value of the control of which you want to assign. You can't use whatever is in SADM20 if its also the place where the updated value is to go.

With that said, you also need to change this:

"CCode=SADM20"

If SADM20 is text, then use this:

"CCode='" & [SADM20] & "'"

If SADM20 is numeric, use this:

"CCode=" & SADM20

 

[Flank]

Ah of course...Completely missed that.

It's always the little things.

Thank you for you help

Cheers

 

[Flank]

Plog,

The table that the form is based on is completely separate. I know the names are the same, I just haven't changed it to what a previous user had there. I am going back and updating the fields to make them more specific to avoid any problems. Thank you for your input on it though, it will definitely prompt me to make the changes sooner rather than later.