Problems with Dlookup 1

[Maturi]

Hi,

I'm struggling to use DlookUp properly. I've got some VB

Dim Flag As Integer
StField = "AMC 5".
Flag = DLookup("[ProcID]", "tbl_Processes", "[ProcRef] =" & stField)

When I run this I get the error message

Runtime error 3075
Syntax Error (missing operator) in query expression '[ProcRef] = AMC 5'

If I try same thing but with stField = "AMC5" I get the message

Runtime error 2471
The expression you entered as a query parameter produced this error 'This object doesn't contain the Automation object'AMC5'

I've also tried

Flag = DLookup("[ProcID]", "tbl_Processes", "[ProcRef] = 'stField'")

But that gives me "Invalid use of Null"


I've used the Dlookup before in many places but here something is stopping it working - any clues ??

Thanks

 

[jebenson]

Try this:

Dim Flag As Integer
StField = "[red]'[/red]AMC 5[red]'[/red]".
Flag = DLookup("[ProcID]", "tbl_Processes", "[ProcRef] =" & stField)

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[Maturi]

Thanks

But this results in the error message... 'This object doesn't contain the Automation object'AMC5'

What I've noticed is that if I use a numeric criteria, Dlookup works fine but it does not work if there is a String criteria (N.B. ProcID is an integer and ProcRef is a text string)

So if I look up a value of ProfRef where ProcID = an integer... this works.
However, if I try to look up PRocID where ProcRef = text string.... then I get these errors.


Does DLookUp only allow numeric criteria? Does anyone know?

(I've tested this both in my app and also by creating a simple DB with just one table and one Form with one button)

Thanks

 

[Maturi]

Hi

OK I've sorted this out now.

If the criteria is based on a text string then you have to construct the criteria like this.

StField = "AMC 5"
MyCriteria = "[ProcRef] = '" & StField & "'"
Flag = DLookup("[ProcID]", "tbl_Processes", MyCriteria)

It's so obvious now!!!

 

[JerryKlmns]

Maturi,

If what you are looking for, does not exist then you get a NULL from the DLOOKUP and a run-time error 94 of :Invalid use of null.