need help with query

[aquinox]

Hi,

I'm afraid i've got the wrong syntax here and wonder if i can get some help here.

In one of the combo-boxes's control source, i have the following code

=iif([Subject Code] = "O",dlookup("[J_ID]","Rate Department","[J_ID] = 'Team*'",dlookup("[J_ID]","Rate Department","[J_ID] <> 'Team*'"))

[Subject Code] is the name of another combo-boxes.

I tried 'like' eg. [J_ID] LIKE 'Team*'" but didn't like it.

Wonder what would be the correct syntax for this to work.

Thank you in advance

 

[jrbarnett]

You need to put Rate Department inside square brackets as there is a space in the name of the table, query or control.

You also need to use [J_ID] Like 'TEAM*'
or [J_ID] Not Like 'TEAM*'

for the false argument criteria in order for the pattern matching to work properly.
Be aware though, that if there are multiple records that match the criteria, DLookup will pull the first one it finds.
If there are no matches, then DLookup will return Null.

John

 

[aquinox]

Hi Jrbarnett,

Thank you for your reply.

It works but like you said before, I'm just getting a single recordset appear in the combo-box. I think i shouldn't use dlookup as it returns only a single value. Is there a way of getting a list of value as set in the criteria.

What i'd like to see is for it to shows a list of records that start with "Team*".

How do i go about it?

Thank you in advance

 

[Spenney]

You could use a SELECT statement with a WHERE clause instead of DLOOKUP.