Dlookup has two comparisons 1

[MICKI0220]

I need to get a dlookup to work based the job number and phase. This is the format I have right now that is not working.
vara = DLookup("[single benefits]", "qry jobinfo", ("[job number] = [text20]") & ("[phase] = [phase]"))


Thanks for the help

Micki

 

[PWise]

vara = DLookup("[single benefits]", "qry jobinfo", "[job number] = " & [text20] & " And [phase] =" & [phase])

if phase is a string
='" & [phase] & "'")

 

[SeeThru]

Dlookup only takes three strings as arguments - ensure eahc argument calculates to a string before dlookup runs.

If it gets too complicated, use variables to build the strings first, and run dlookup on the variables.

SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk

 

[MICKI0220]

pwise

I tried that code and I got this error:

Run-time error. '2471'
The expression you entered as a query parameter produced this error: 'E'

 

[MICKI0220]

This is the whole code:

Private Sub Combo83_Change()
Dim vara As Variant
Dim varb As Variant

vara = DLookup("[single benefits]", "qry jobinfo", "[job number] = " & [Text20] & " And [phase] =" & [Phase])
varb = DLookup("[NO benefits]", "qry jobinfo", "[job number] = " & [Text20] & " And [phase] =" & [Phase])

If Combo83.Value = "single" Then
Text44.Value = vara
End If
If Combo83.Value = "NONE" Then
Text44.Value = varb
End If
If Combo83.Value = "FAMILY" Then
Text44.Value = "0"
End If
End Sub

 

[MoLaker]

What is [Phase} as in " And [phase] =" & [Phase])? If it is a form control, try me.phase. - " And [phase] =" & me.Phase)

"Retired Programmer". So, please be patient.

 

[MICKI0220]

phase is just a field in the table. It is also the name of the text box on the form. The table that it will look up this value has a combo primary field. The job number and the phase create the primary key. That is why the dlookup needs to get the rate from the combination or else it does what it is doing, which is to use the single rate from the first record it finds with the matching job number.

 

[randy700]

phase is just a field in the table. It is also the name of the text box on the form

Which is a perfect example of why you should use different names - to avoid confusion. Change the name of the text box to something like txtPhase. Also, be consistent with brackets around field names containing spaces. Better yet, get rid of the spaces.

vara = DLookup("[single benefits]", "[COLOR=red][b][[/b][/color]qry jobinfo[COLOR=red][b]][/b][/color]", "[job number] = " & me.Text20 & " And Phase = " & me.txtPhase)

Randy

 

[MICKI0220]

Randy.

I renamed the phase to txtphase and it still gives me that error.

 

[ck1999]

Since you have had no luck so far.

Why not try known values to see if it works

example

vara = DLookup("[single benefits]", "[qry jobinfo]", "[job number] = 100 And [Phase] = 5") replace 100 and 5 with known values that are in the table

then try

vara = DLookup("[single benefits]", "[qry jobinfo]", "[job number] = " & me.Text20 & " And [Phase] = 5") again replace the 5 with known value from table

See what doing this gives you. If you get an error out of the 1st example then i think there may be another problem

ck1999

 

[MICKI0220]

ck1999

I still get the same error with the know variables put in place.

 

[ck1999]

If both looks are string use

vara = DLookup("[single benefits]", "qry jobinfo", "[job number] = '" & [Text20] & "' And [phase] ='" & [Phase] &"'")

if 1 in an integer remove the ' from around the variable

ck1999

 

[MICKI0220]

This is what the values are
vara = DLookup("[single benefits]", "[qry jobinfo]", "[job number] = e-6379 And [Phase] = fp")

 

[ck1999]

change to

vara = DLookup("[single benefits]", "[qry jobinfo]", "[job number] = 'e-6379' And [Phase] = 'fp'")

see my last post

ck1999

 

[randy700]

I still get the same error with the know variables put in place

Verify the accuracy of the names you are using...
Do you have a query named qry jobinfo including the space?
Is there a field within that query named single benefits including the space?
Is there a field within the query named job number including the space?


Randy