DLookup Syntax Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 &.
Click to expand...

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 & " ' ")
 
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.
 
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 & '")
Click to expand...


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)
 
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 &.
Click to expand...

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 & " ' ")
Click to expand...
 
Back
Top