IF statement

[rupes_mahal]

hi..

I am trying to lookup values from another field according to the what is typed into the form.

Code used:

Private Sub SIscore_Click()

Dim varX As Variant
Dim var As Variant

var = Me!Bodysys

('bodysys is the field on the form where the user inserts code)

If var = BodySysCode Then

('BodySysCode is the field in the Systemscore table with codes and values in it)

varX = DLookup("[Primval]","Systemscore", "[Clin1]=[PrimCode]")

Me!SIscore = varx
end if
end sub

What i want to do is:

[Bodysys][Clin1] = [BodySysCode][PrimCode]

instead of [Clin1] = [PrimCode] as u can see above.

It looks at the 1st column to find a match with the Bodysys and BodySysCode...once found it then should look for a match in the 2nd column for Clin1 and Primcode.

e.g

[bodysyscode] [primcode] [PrimVal]
BL, SCC 2
BL, TXT 3
BL, POL 2
CN, SCC 4

(commas = next column)

it has to match both columns before returning back the PrimVal. AT the moment, with the code above, it is only looking at the [PrimCode] column and returning back the first match, instead of matching both columns and returing back a correct result.

please please please please help.....i am so stuck.....

thanku

ruby

 

[charityg]

I'm not sure exactly what you want, but if you are looking to set more than one criteria in a Dlookup function, just use AND

like

varX = DLookup("[Primval]","Systemscore", "[Clin1]=[PrimCode] and [Bodysys]= [BodySysCode]")

 

[rupes_mahal]

hi Charityg...

Thankyou for your reply and your advise is helping me very much. I now think I am on the way of developing it further.

Thanku again

Ruby