DLookup criteria

[paul25_uk]

Hi, i know there are loads of posts for this problem but no matter how many I read it just doesn't seem to work!

Basically I have a form and i need to have a text box which shows a value of business placed into a specific hotel by a specific client. The table where that data comes from is [RN04_CLIENT] and the field with the value is [RN] and the field with the client name is [RESCOMPANY].

There are two fields on the form where this would link, the first is a field called [txt_HOTEL] and the other is a created dlookup field called [look_CLIENT]. This looks at a code to return the Client name that would match the [RESCOMPANY] field in the table.

If i try to do the dlookup with either criteria on it's own it works fine, but i'm very new to this stuff and would appreciate any help you can on this?

FYI With the 2 criteria in only 1 record would ever return, am I right in assuming that if more than 1 would return the dsum would also work?

Many thanks, anyone from M'cr helps i'll get you a beer!

 

[WayneRyan]

Paul,

Not following all the field names, but here's the syntax:

DLookUp("[RN]", _
        "RN04_CLIENT", _
        "[txt_HOTEL] = '" & Me.txtHotel & "' And " & _
        "[RESCOMPANY] = '" & Me.ClientName & "'")

Wayne

 

[paul25_uk]

Additional Info

Can you tell me why there is an additional ' surrounding some references?

I'm very new to all this and it's probably that what has caused me to get this wrong. Any help/advice is well received!

Thanks

 

[WayneRyan]

Paul,

SQL thinks like this:

SomeDate = #9/14/2004#
SomeString = 'SomeString'
SomeNumber = 123

In code this looks like:

..."SomeDate = #" & Me.SomeDate & "#"
..."SomeString = '" & Me.SomeString & "'"
..."SomeNumber = " & Me.SomeNumber

You just have to "think" like the compiler and present the proper strings
to it.

Wayne

 

[paul25_uk]

Thanks

Superstar,

Thanks a bunch, much appreciated