string in dlookup : how many quotes ?

  • Thread starter Thread starter W
  • Start date Start date
W

W

Hi all,

I use a dlookup function to determine whether a record has already been
encoded or not.
This does it all right :

DLookup("Id", "tblOrders", "[Customer] = " & lngCustomer & _
" and [OrderDate] = #" & dOrderDate & "#" & _
" and [Branch] = """ & cBranch & """")

However I have to add one more condition, and here it all goes wrong : I
have to test for :

And [City] = “â€â€ & cCity & “â€â€â€

I don’t manage to add this condition after “and [Branch] “â€â€ & cBranch & “â€â€â€

Any help will be appreciated.

W
 
It depends on the data type of the field in the Criteria string.

If [Branch] is a Text field, use " as the delimiter around the value.
If [OrderDate] is a Date/Time field, use # as the delimiter.
If [Customer is a Number type field, use no delimiter.

Now the question arises as to how to get the " inside a string, when the
same character indicates where the string starts and ends. To answer that
question, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html
 
Ciao said:
Hi all,

I use a dlookup function to determine whether a record has already
been encoded or not.
This does it all right :
Click to expand...

DLookup("Id", "tblOrders", "[Customer] = " & lngCustomer & _
" and [OrderDate] = #" & dOrderDate & "#" & _
" and [Branch] = """ & cBranch & """And [City] = """ & cCity & """")
However I have to add one more condition, and here it all goes wrong
: I have to test for :

And [City] = """ & cCity & """"

I don't manage to add this condition after "and [Branch] """ &
cBranch & """"

Any help will be appreciated.

W
Click to expand...
 
Hi Allen,

I used the old notation (I've learnt in the late 70ties when doing
C-programming) : cVariable for a string (character variable) , dVariable for
a date, and so on.

So cBranch is a string, dOrderDate is a date, lngCustomer is a long integer.
The problem is in having two strings as a condition : I got lost with the
number of single and or double quote.

I'll now have a look at your site.

Thank you,

W
 
Hi again,

I had read both articles before posting my question. Although I find both
(quotations and dlookup) very good, they did not help me in solving my
problem.

Very good articles, though.

W
 
Hi Geppo,

I corrected one small error (typo of mine I guess) and it works fine now.
Thank you,

W
 
Perhaps you need to include the = operator:
AND ([Branch] = """ & cBranch & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

W said:
Hi again,

I had read both articles before posting my question. Although I find both
(quotations and dlookup) very good, they did not help me in solving my
problem.

Very good articles, though.

W

Allen Browne said:
It depends on the data type of the field in the Criteria string.

If [Branch] is a Text field, use " as the delimiter around the value.
If [OrderDate] is a Date/Time field, use # as the delimiter.
If [Customer is a Number type field, use no delimiter.

Now the question arises as to how to get the " inside a string, when the
same character indicates where the string starts and ends. To answer that
question, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html
Click to expand...
Click to expand...
 
Back
Top