Dlookup and Conditional Formatting

[Ixtreme]

Somehow I just can't get this to work:

In Access 2002 I have a form and a subform with related records. On
the subform I want to verify if a field is present in a specific table
via dlookup. I am only interested in the first 8 characters. The 8
letter string must be looked up in this table. If not present, color
the field indicating a value that is not present. I tried everything.
What I have so far:

IsNull(DLookUp("[Country]"~"SEPA"~"[BIC] = " & Left(Forms![FOT]!
[FOTP].Form![Party Identifier]~8)")

The table 'SEPA' contains the fields 'Country', 'Name' and 'BIC'
Example: NETHERLANDS~JP MORGAN CHASE BANK AMSTERDAM~CHASNL2X

The subform contains a field 'Party Identifier'
Example: 'CHASNL2XXX'

If CHASNL2X is not in table SEPA I would like to have field Party
Identifier to be in red font.

 

[John W. Vinson]

Somehow I just can't get this to work:

In Access 2002 I have a form and a subform with related records. On
the subform I want to verify if a field is present in a specific table
via dlookup. I am only interested in the first 8 characters. The 8
letter string must be looked up in this table. If not present, color
the field indicating a value that is not present. I tried everything.
What I have so far:

IsNull(DLookUp("[Country]"~"SEPA"~"[BIC] = " & Left(Forms![FOT]!
[FOTP].Form![Party Identifier]~8)")

The table 'SEPA' contains the fields 'Country', 'Name' and 'BIC'
Example: NETHERLANDS~JP MORGAN CHASE BANK AMSTERDAM~CHASNL2X

The subform contains a field 'Party Identifier'
Example: 'CHASNL2XXX'

If CHASNL2X is not in table SEPA I would like to have field Party
Identifier to be in red font.

Your DLookUp expression is incorrect, and I have no idea what you intend.
DLookUp has three arguments: the first is the name of the field to be looked
up; the second is the name of the table or query in which to look; the third
is a valid SQL WHERE clause specifying which record to look up. They're
delimited by commas (or semicolons in some regional settings), not by tildes.

I'm GUESSING that you want:

DLookUp("[Country]", "[SEPA]", "[BIC] = '" &
Left([Forms![FOT]![FOTP].Form![Party Identifier],8) & "'")

This includes a ' delimiter as required for searching text fields.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Ixtreme]

John, thanks for your answer. What I am trying to do is colour the
'Party Identifier' field on the subform FOTP if the associated country
of this Party Identifier cannot be found in table BIC.

I have Party Identifier = CHASNL2XXX
The table 'SEPA' contains the fields 'Country', 'Name' and 'BIC'
Example: NETHERLANDS~JP MORGAN CHASE BANK AMSTERDAM~CHASNL2X

CHASENL2X( the first 8 characters) can be found in the SEPA table,
hence the field party identifier should not be coloured. If the value
cannot be found, colour the field red.

BTW: I changed the delimiter into a tilde in Setting, Regional
settings.I chenged it back to a comma but it still does not work.

 

[Ixtreme]

In fact, it's even less complicated:

If the 8 character value of the field Party Identifier (in the example
CHASNL2X) cannot be found in SEPA table then colour Party identifier
field in red font.

I have in conditon 1 in conditional formatting on field Party
identifier:
isnull(DLookUp("[Country]", "[SEPA]", "[BIC] = '" & Left([Forms![FOT]!
[FOTP].Form![Party Identifier],8) & "'") )

the value is not in the table SEPA and it just does not do anything.