Dlookup problems

  • Thread starter Thread starter Pam
  • Start date Start date
P

Pam

I'm trying to debug a dlookup problem, and can't find the
problem. I'm hoping someone here can see what I'm missing.
TIA for any help.

In using the immediate window to debug:
? Forms![time input form]![time subform1]!cont_numb
102-E
The above is correct.


? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]='102-
E'")
1.5
The above is correct, too.


But, when I put it all together as shown below, it returns
Null...why???

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= ' "
& Forms![time input form]![time subform1]!cont_numb
& " ' ")
Null
 
cont_numb is a text field. So I should drop which extra
quote marks? (I thought I had to have them if it was
text...)
I'll try using the .Form, too.
THANKS!
-----Original Message-----
What kind of field is cont_numb?
If Text, drop the extra quotes.
It may also help to include the ".Form".

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= " & Forms![time input
form]![time subform1].Form!cont_numb)

More info on DLookup():
http://allenbrowne.com/casu-07.html
and on the .Form bit:
http://allenbrowne.com/casu-04.html

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

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

I'm trying to debug a dlookup problem, and can't find the
problem. I'm hoping someone here can see what I'm missing.
TIA for any help.

In using the immediate window to debug:
? Forms![time input form]![time subform1]!cont_numb
102-E
The above is correct.


? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]='102-
E'")
1.5
The above is correct, too.


But, when I put it all together as shown below, it returns
Null...why???

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= ' "
& Forms![time input form]![time subform1]!cont_numb
& " ' ")
Null
Click to expand...


.
Click to expand...
 
Ok, tried it with .Form and w/o the extra quotation marks
as your example shows. Got runtime error 2471, Access
can't find the name 'E' you entered in the expression.

If I try using .Form and use the quotation marks, it
returns null.
-----Original Message-----
What kind of field is cont_numb?
If Text, drop the extra quotes.
It may also help to include the ".Form".

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= " & Forms![time input
form]![time subform1].Form!cont_numb)

More info on DLookup():
http://allenbrowne.com/casu-07.html
and on the .Form bit:
http://allenbrowne.com/casu-04.html

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

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

I'm trying to debug a dlookup problem, and can't find the
problem. I'm hoping someone here can see what I'm missing.
TIA for any help.

In using the immediate window to debug:
? Forms![time input form]![time subform1]!cont_numb
102-E
The above is correct.


? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]='102-
E'")
1.5
The above is correct, too.


But, when I put it all together as shown below, it returns
Null...why???

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= ' "
& Forms![time input form]![time subform1]!cont_numb
& " ' ")
Null
Click to expand...


.
Click to expand...
 
Sorry: it *needs* the delimiters if it is Text.
You might try double quotes:
? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= """ &
Forms![time input form]![time subform1].Form!cont_numb & """")

If that does not work, try opening the "time input form" in design view.
Right-click the subform, and choose Properties.
What is the Name of the subform control?
It may be different from the SourceObject (the name of the form that is
loaded into it).


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

Reply to group, rather than allenbrowne at mvps dot org.
Ok, tried it with .Form and w/o the extra quotation marks
as your example shows. Got runtime error 2471, Access
can't find the name 'E' you entered in the expression.

If I try using .Form and use the quotation marks, it
returns null.
-----Original Message-----
What kind of field is cont_numb?
If Text, drop the extra quotes.
It may also help to include the ".Form".

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= " & Forms![time input
form]![time subform1].Form!cont_numb)

More info on DLookup():
http://allenbrowne.com/casu-07.html
and on the .Form bit:
http://allenbrowne.com/casu-04.html

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

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

I'm trying to debug a dlookup problem, and can't find the
problem. I'm hoping someone here can see what I'm missing.
TIA for any help.

In using the immediate window to debug:
? Forms![time input form]![time subform1]!cont_numb
102-E
The above is correct.


? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]='102-
E'")
1.5
The above is correct, too.


But, when I put it all together as shown below, it returns
Null...why???

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= ' "
& Forms![time input form]![time subform1]!cont_numb
& " ' ")
Null
Click to expand...


.
Click to expand...
Click to expand...
 
Thank you, thank you! This (double quotes) worked! I was
going crazy trying to find the solution. For future
reference, would you be able to tell me why it needs the
double quotes??
Thanks again-

-----Original Message-----
Sorry: it *needs* the delimiters if it is Text.
You might try double quotes:
? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= """ &
Forms![time input form]![time subform1].Form!cont_numb & """")

If that does not work, try opening the "time input form" in design view.
Right-click the subform, and choose Properties.
What is the Name of the subform control?
It may be different from the SourceObject (the name of the form that is
loaded into it).


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

Reply to group, rather than allenbrowne at mvps dot org.
Ok, tried it with .Form and w/o the extra quotation marks
as your example shows. Got runtime error 2471, Access
can't find the name 'E' you entered in the expression.

If I try using .Form and use the quotation marks, it
returns null.
-----Original Message-----
What kind of field is cont_numb?
If Text, drop the extra quotes.
It may also help to include the ".Form".

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= "
Click to expand...
&
Forms![time input
form]![time subform1].Form!cont_numb)

More info on DLookup():
http://allenbrowne.com/casu-07.html
and on the .Form bit:
http://allenbrowne.com/casu-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I'm trying to debug a dlookup problem, and can't find the
problem. I'm hoping someone here can see what I'm missing.
TIA for any help.

In using the immediate window to debug:
? Forms![time input form]![time subform1]!cont_numb
102-E
The above is correct.


? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb] ='102-
E'")
1.5
The above is correct, too.


But, when I put it all together as shown below, it returns
Null...why???

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb] = ' "
& Forms![time input form]![time subform1]!cont_numb
& " ' ")
Null


.
Click to expand...
Click to expand...


.
Click to expand...
 
It doesn't need to be double quotes. If you go back to
your original example, you have an extra space on either
side of the single quote. Try this...it should work:

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= '"
& Forms![time input form]![time subform1]!cont_numb
& "'")

Let me know...

Chuck
-----Original Message-----
Thank you, thank you! This (double quotes) worked! I was
going crazy trying to find the solution. For future
reference, would you be able to tell me why it needs the
double quotes??
Thanks again-

-----Original Message-----
Sorry: it *needs* the delimiters if it is Text.
You might try double quotes:
? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= """ &
Forms![time input form]![time subform1].Form!cont_numb & """")

If that does not work, try opening the "time input form" in design view.
Right-click the subform, and choose Properties.
What is the Name of the subform control?
It may be different from the SourceObject (the name of the form that is
loaded into it).


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

Reply to group, rather than allenbrowne at mvps dot org.
Ok, tried it with .Form and w/o the extra quotation marks
as your example shows. Got runtime error 2471, Access
can't find the name 'E' you entered in the expression.

If I try using .Form and use the quotation marks, it
returns null.

-----Original Message-----
What kind of field is cont_numb?
If Text, drop the extra quotes.
It may also help to include the ".Form".

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]
Click to expand...
Click to expand...
= "
&
Forms![time input
form]![time subform1].Form!cont_numb)

More info on DLookup():
http://allenbrowne.com/casu-07.html
and on the .Form bit:
http://allenbrowne.com/casu-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
I'm trying to debug a dlookup problem, and can't find
the
problem. I'm hoping someone here can see what I'm
missing.
TIA for any help.

In using the immediate window to debug:
? Forms![time input form]![time subform1]!cont_numb
102-E
The above is correct.


? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb] ='102-
E'")
1.5
The above is correct, too.


But, when I put it all together as shown below, it
returns
Null...why???

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb] = ' "
& Forms![time input form]![time subform1]!cont_numb
& " ' ")
Null


.
Click to expand...


.
Click to expand...
.
Click to expand...
 
The quote marks delimit the string.
If you just put the quote marks inside the string, Access things it has
reached the end of the string, and then can't make sense of the rest of the
line. Doubling them says, "This is not the end of the string; it's a literal
quote in the string."

Examples:
"This string has ""quote marks"" inside"
"This string has them ""also""."
"Surname = ""Smith"""
"Surname = """ & [SomeTextbox] & """"

While it is possible to use single quotes instead of double quotes inside
query statements and clauses, doing that messes up with words that have an
apostrophy, e.g. this works:
"Surname = 'Smith'"
but this does not:
"Surname = 'O'Brien'"

The same problem does occur with the double-quote character as well, but
this happens less often.

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

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

Pam said:
Thank you, thank you! This (double quotes) worked! I was
going crazy trying to find the solution. For future
reference, would you be able to tell me why it needs the
double quotes??
Thanks again-

-----Original Message-----
Sorry: it *needs* the delimiters if it is Text.
You might try double quotes:
? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= """ &
Forms![time input form]![time subform1].Form!cont_numb & """")

If that does not work, try opening the "time input form" in design view.
Right-click the subform, and choose Properties.
What is the Name of the subform control?
It may be different from the SourceObject (the name of the form that is
loaded into it).


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

Reply to group, rather than allenbrowne at mvps dot org.
Ok, tried it with .Form and w/o the extra quotation marks
as your example shows. Got runtime error 2471, Access
can't find the name 'E' you entered in the expression.

If I try using .Form and use the quotation marks, it
returns null.

-----Original Message-----
What kind of field is cont_numb?
If Text, drop the extra quotes.
It may also help to include the ".Form".

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= " &
Forms![time input
form]![time subform1].Form!cont_numb)

More info on DLookup():
http://allenbrowne.com/casu-07.html
and on the .Form bit:
http://allenbrowne.com/casu-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
I'm trying to debug a dlookup problem, and can't find
the
problem. I'm hoping someone here can see what I'm
missing.
TIA for any help.

In using the immediate window to debug:
? Forms![time input form]![time subform1]!cont_numb
102-E
The above is correct.


? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb] ='102-
E'")
1.5
The above is correct, too.


But, when I put it all together as shown below, it
returns
Null...why???

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb] = ' "
& Forms![time input form]![time subform1]!cont_numb
& " ' ")
Null
Click to expand...
Click to expand...
Click to expand...
 
Ok, tried it, and that works. So, no spaces around the
quote, then.
Thanks-

-----Original Message-----
It doesn't need to be double quotes. If you go back to
your original example, you have an extra space on either
side of the single quote. Try this...it should work:

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]= '"
& Forms![time input form]![time subform1]!cont_numb
& "'")

Let me know...

Chuck
-----Original Message-----
Thank you, thank you! This (double quotes) worked! I was
going crazy trying to find the solution. For future
reference, would you be able to tell me why it needs the
double quotes??
Thanks again-

-----Original Message-----
Sorry: it *needs* the delimiters if it is Text.
You might try double quotes:
? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]
Click to expand...
Click to expand...
= """
&
Forms![time input form]![time subform1].Form!cont_numb & """")

If that does not work, try opening the "time input
Click to expand...
form"
in design view.
Right-click the subform, and choose Properties.
What is the Name of the subform control?
It may be different from the SourceObject (the name of the form that is
loaded into it).


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

Reply to group, rather than allenbrowne at mvps dot org.
Ok, tried it with .Form and w/o the extra quotation marks
as your example shows. Got runtime error 2471, Access
can't find the name 'E' you entered in the expression.

If I try using .Form and use the quotation marks, it
returns null.

-----Original Message-----
What kind of field is cont_numb?
If Text, drop the extra quotes.
It may also help to include the ".Form".

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb]
Click to expand...
= "
&
Forms![time input
form]![time subform1].Form!cont_numb)

More info on DLookup():
http://allenbrowne.com/casu-07.html
and on the .Form bit:
http://allenbrowne.com/casu-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
I'm trying to debug a dlookup problem, and can't find
the
problem. I'm hoping someone here can see what I'm
missing.
TIA for any help.

In using the immediate window to debug:
? Forms![time input form]![time subform1]!cont_numb
102-E
The above is correct.


? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb] ='102-
E'")
1.5
The above is correct, too.


But, when I put it all together as shown below, it
returns
Null...why???

? DLookUp("[piece_rate]","[CONTRACT]","[cont_numb] = ' "
& Forms![time input form]![time subform1]!cont_numb
& " ' ")
Null


.



.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I thought I'd add something. With string variable names in the criteria field, I found no combination of double quotes would work and then I got success with single quotes as follows

dlookup("[PLAN NAME]","PLANS","[PLAN NAME] = '" & strplan & "'"

Now that I read the previous examples, it looks like this may have already been stated, though
 
Back
Top