Three criteria in Dlookup not working?

[dcdimon]

I'm trying to get a dlookup to work with three criteria. if I remove one (any one) it works. The minute I insert the third back into the script (vb) it won't pull up the data. My dlookup is below:

ctl.ControlSource = DLookup("ControlSource", "UFO_CIL_selectedFieldListArchiveT", "FieldNumber = " & i & " and EquipmentNumber = '" & Forms!UFO_OperatorFrameF!BlankPageF.Form!SelectedEquipmentTxt & "' and shift = 'all'")

I'm also getting an error that makes no sense to me:

Syntax error (missing operator) in query expression 'FieldNumber = 1 and EquipmentNumber = '107309' and shift = 'all'

I've been banging my head against the monitor long enough I need to phone a friend. Can anyone tell me what I'm doing wrong?

Thanks,
DD

[RuralGuy]

Is EquipmentNumber a Text value or a Number?

[dcdimon]

It's text, the only number/integer is the string i.

[RuralGuy]

It may be a timing issue. Try adding a Command Button to the form that will display the same DLookup() value when you press it.

[dcdimon]

Okay I figured it out.

My script is/was working. I had a problem with a query that was being used prior to the table the DLookup is using and the script wasn't able to pull out data because it was being asked for something that didn't exist. Once I fixed my query then all was golden.

So, end result: Access was doing what it was asked. User had a brain cramp.

[RuralGuy]

Thanks for the update.