DLookup questions

[Frederick Chow]

Hi all,

I am a newbie in Access and have some problems in the DLookup function...

Step to replicate the problem.

1. Create a table called "FirstTable" with two text fields, "Username" and
"Password".

2. In the FirstTable, fill in two records like ("Mary", "12345"), ("John",
"45678")

3. Go to VBA Editor and in the immediate window, type:

- ? DLookup("[UserName]", "FirstTable", "[UserName]=Username")
- Result: Mary
-? DLookup("[UserName]", "FirstTable", "[UserName]=Mary")
- Result: Runtime error '2001' - You canceled the previous operation.

My question:

1. Why "[FieldName]=Fieldname" in the criteria part of the function will
return the first record? Shouldn't it return Null instead?
2. I really can't understand the error message in the second case.

Thanks for your help in advance.

Frederick Chow
Hong Kong.

 

[Rick Brandt]

Hi all,

I am a newbie in Access and have some problems in the DLookup function...

Step to replicate the problem.

1. Create a table called "FirstTable" with two text fields, "Username" and
"Password".

2. In the FirstTable, fill in two records like ("Mary", "12345"), ("John",
"45678")

3. Go to VBA Editor and in the immediate window, type:

- ? DLookup("[UserName]", "FirstTable", "[UserName]=Username")
- Result: Mary
-? DLookup("[UserName]", "FirstTable", "[UserName]=Mary")
- Result: Runtime error '2001' - You canceled the previous operation.

My question:

1. Why "[FieldName]=Fieldname" in the criteria part of the function will
return the first record? Shouldn't it return Null instead?
2. I really can't understand the error message in the second case.

All Records satisfy the first one and DLookup (per the help file) returns the
first record that satisfies the criteria. The second one is syntactically
incorrect because you need to have quotes around a text value.

? DLookup("[UserName]", "FirstTable", "[UserName] = 'Mary'")
- Result: 12345

 

[Frederick Chow]

Thanks very much for the response. At least you have clarified my problem.

By the way, if in the immediate window I typed the following:

A = "Mary"
? A
=> Mary
DLookup(["Username"], "FirstTable", "[Username]=" & A)
=> Runtime error '2001' - You canceled the previous operation

Why is that? Is there something wrong with the concatenation operator?
Please advise.

Frederick Chow
Hong Kong

Hi all,

I am a newbie in Access and have some problems in the DLookup function...

Step to replicate the problem.

1. Create a table called "FirstTable" with two text fields, "Username"
and "Password".

2. In the FirstTable, fill in two records like ("Mary", "12345"),
("John", "45678")

3. Go to VBA Editor and in the immediate window, type:

- ? DLookup("[UserName]", "FirstTable", "[UserName]=Username")
- Result: Mary
-? DLookup("[UserName]", "FirstTable", "[UserName]=Mary")
- Result: Runtime error '2001' - You canceled the previous operation.

My question:

1. Why "[FieldName]=Fieldname" in the criteria part of the function will
return the first record? Shouldn't it return Null instead?
2. I really can't understand the error message in the second case.

All Records satisfy the first one and DLookup (per the help file) returns
the first record that satisfies the criteria. The second one is
syntactically incorrect because you need to have quotes around a text
value.

? DLookup("[UserName]", "FirstTable", "[UserName] = 'Mary'")
- Result: 12345

 

[Rick Brandt]

Thanks very much for the response. At least you have clarified my
problem.
By the way, if in the immediate window I typed the following:

A = "Mary"
? A
=> Mary
DLookup(["Username"], "FirstTable", "[Username]=" & A)
=> Runtime error '2001' - You canceled the previous operation

Why is that? Is there something wrong with the concatenation operator?
Please advise.

Even though A is a string variable the expression still needs quotes around it.