Syntax problem with a DLookup function call

[Isis]

I am having problems with my syntax I think.

I have a combo box on a form called 'ListType' - It can be set to one of
three values (values present in the [TYPE] field of the 'Colours' Table;
'Standard'
'Brown'
'Green'

I am using DLookup to get the right value from a Table based on the value
set in the 'ListType' Control like this;


strColour = DLookup([strFieldName], "Colours", "[Type] = Me.ListType")

This is failing on the "[TYPE] =" part of the DLookup - The [TYPE] field
in the Colours Table is a Text field but if I change it to a Numeric
field and substitute [TYPE] = 1 then the DLookup works as I expect so I
think my syntax is out somehow when the field is a Text field.

What should my DLookup function read given that [TYPE] in the 'Colours'
file is a Text Field ! I think I am using or not using ("") or ('')
wrongly in the "[Type] = Me.ListType" section.

I hope this makes some sense.

Any info appreciated

Regards

 

[fredg]

I am having problems with my syntax I think.

I have a combo box on a form called 'ListType' - It can be set to one of
three values (values present in the [TYPE] field of the 'Colours' Table;
'Standard'
'Brown'
'Green'

I am using DLookup to get the right value from a Table based on the value
set in the 'ListType' Control like this;

strColour = DLookup([strFieldName], "Colours", "[Type] = Me.ListType")

This is failing on the "[TYPE] =" part of the DLookup - The [TYPE] field
in the Colours Table is a Text field but if I change it to a Numeric
field and substitute [TYPE] = 1 then the DLookup works as I expect so I
think my syntax is out somehow when the field is a Text field.

What should my DLookup function read given that [TYPE] in the 'Colours'
file is a Text Field ! I think I am using or not using ("") or ('')
wrongly in the "[Type] = Me.ListType" section.

I hope this makes some sense.

Any info appreciated

Regards

Each argument of the DLookUp must be a string (enclosed within
quotes).
The Where clause string must be concatenated with the value of the
control on the form, not the name of the control as you have it.
Also, if the Criteria field is a text datatype, the value must be
enclosed within single quotes, i.e.
DLookUp("[AField]","TableName","[CriteriaField] = 'Brown'")

If [Type] is a Number datatype field, then:
strColour = DLookup("[strFieldName]", "Colours", "[Type] = '" &
Me.ListType & "'")

If [Type] had been a Number datatype, then:
strColour = DLookup("[strFieldName]", "Colours", "[Type] = " &
Me.ListType)

 

[John Spencer]

I would Try

strColour = DLookup([strFieldName], "Colours", "[Type] =" & Me.ListType)

And normally, I would expect to see the field name in quotes, but if your code
is working thatn strFieldName must contain a field name that is in the Colours table.