DLookup Syntax Question

L

Leslie

I'm trying to perform a lookup for a SoftwareName field in the Software Table
and load it into Text Box ControlSource in my UserSoftware table. This is my
syntax. I keep getting "Invalid Control Property" and when I run the lookup,
I get #Name? in the results field.

These are the tables and relevant fields:
Software
SoftwareID
SoftwareName

UserSoftware
SoftwareID
Text Box:Text9

This my syntax:

DLookup("[SoftwareName]", "Software", "[SoftwareID] = "' &
Forms![UserSoftware]!SoftwareID & '")

Any help would be appreciated. I am guessing at the syntax because I don't
understand the difference between " and ' and I don't know the functionality
of the &.

Thanks,
Leslie
 
R

Rick Brandt

Leslie said:
I'm trying to perform a lookup for a SoftwareName field in the
Software Table and load it into Text Box ControlSource in my
UserSoftware table. This is my syntax. I keep getting "Invalid
Control Property" and when I run the lookup, I get #Name? in the
results field.

These are the tables and relevant fields:
Software
SoftwareID
SoftwareName

UserSoftware
SoftwareID
Text Box:Text9

This my syntax:

DLookup("[SoftwareName]", "Software", "[SoftwareID] = "' &
Forms![UserSoftware]!SoftwareID & '")

Any help would be appreciated. I am guessing at the syntax because I
don't understand the difference between " and ' and I don't know the
functionality of the &.

Assuming SoftwareID is a text field you have your quotes wrong. In the
corrected example below I have added spaces between the single and double
quotes for clarity. You would remove those in your actual expression.

DLookup("[SoftwareName]", "Software", "[SoftwareID] = ' " &
Forms![UserSoftware]!SoftwareID & " ' ")
 
K

Klatuu

The & is a concantenation operator for combining 2 or more text strings
together. You will see the + used by some, but that is incorrect, the + in
Access is a math operatior. So X = "ABC" & "XZY" will result in X = "ABCXYZ".
The ' can be used in strings that will be used for querys or sql strings
where normally the " would be. You can combine multiple """ to accomplish
this, but I have never got my head around how many " to use where. In the
case, you want to create a query or where string that will result in
(assuming the value in your control is 83:
[SoftwareID] = '83'
So If you wrote the statement as:
"[SoftwareID] = Forms![UserSoftware]!SoftwareID "
Then you would get exactly that. What you need to do is get the value in
the control, so you use the & to concatenate the stings. This way, the value
of the control is inserted instead of the name of the control:
"[SoftwareID] = "' & Forms![UserSoftware]!SoftwareID & '"
will result in
[SoftwareID] = '83'
So, here is what the problem may be. The way your code is written, it is
translating the value in your control as a string. If, in fact, [SoftwareID]
is numeric, it should be written without the ' That way, the result would be:
[SoftwareID] = 83

I hope this helps.
 
M

Marshall Barton

Leslie said:
I'm trying to perform a lookup for a SoftwareName field in the Software Table
and load it into Text Box ControlSource in my UserSoftware table. This is my
syntax. I keep getting "Invalid Control Property" and when I run the lookup,
I get #Name? in the results field.

These are the tables and relevant fields:
Software
SoftwareID
SoftwareName

UserSoftware
SoftwareID
Text Box:Text9

This my syntax:

DLookup("[SoftwareName]", "Software", "[SoftwareID] = "' &
Forms![UserSoftware]!SoftwareID & '")


If the SoftwareID field is a Text type field, then the 's
need to be enclosed inside "s. Pay very close attention to
the 's and "s in the changes I made.

DLookup("[SoftwareName]", "Software", "[SoftwareID] = '" &
Forms![UserSoftware]!SoftwareID & "'")

OTOH, if SoftwareID is a numeric type field, the 's need to
be omitted:

DLookup("[SoftwareName]", "Software", "[SoftwareID] = " &
Forms![UserSoftware]!SoftwareID)
 
L

Leslie

Thanks Rick,

That did the trick! Can you explain the & and the difference between the "
and the ' ?

Thanks,
Leslie

Rick Brandt said:
Leslie said:
I'm trying to perform a lookup for a SoftwareName field in the
Software Table and load it into Text Box ControlSource in my
UserSoftware table. This is my syntax. I keep getting "Invalid
Control Property" and when I run the lookup, I get #Name? in the
results field.

These are the tables and relevant fields:
Software
SoftwareID
SoftwareName

UserSoftware
SoftwareID
Text Box:Text9

This my syntax:

DLookup("[SoftwareName]", "Software", "[SoftwareID] = "' &
Forms![UserSoftware]!SoftwareID & '")

Any help would be appreciated. I am guessing at the syntax because I
don't understand the difference between " and ' and I don't know the
functionality of the &.

Assuming SoftwareID is a text field you have your quotes wrong. In the
corrected example below I have added spaces between the single and double
quotes for clarity. You would remove those in your actual expression.

DLookup("[SoftwareName]", "Software", "[SoftwareID] = ' " &
Forms![UserSoftware]!SoftwareID & " ' ")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DLookup in a SubForm 2
insert into 7
Report Group Footer DLookUp 0
Error 2046 Save Record isn't Avail now 0
DLookup in a SubForm 15
DLookup Syntex error it is killing me 7
DLookup Help 2
dlookup formula in Form 5

Top