operand without operator - invalid syntax

[aquinox]

Hi,

Wonder if i can get some help here with the syntax. The following is the code i'm trying to put into a control source of a textbox.

=iif([SubJob_Name] = "", dlookup("[Job_Name]","Job Register","[Job_ID]='" &[Job Register]![Job_ID] & "' AND [Industry_No]='"[Job Register]![Industry_No] & "' AND [Client_No]='"[Job Register]![Client_No] & "'"), [SubJob_Name])

I got an invalid syntax - you may have entered an operand without an operator. How do i add "AND" to the criteria in the dlookup?

Thank you in advance

 

[JoeAtWork]

I think you are missing a couple of ampersands:

=iif([SubJob_Name] = "", dlookup("[Job_Name]","Job Register","[Job_ID]='" & [Job Register]![Job_ID] & "' AND [Industry_No]='" [COLOR=red]&[/color] [Job Register]![Industry_No] & "' AND [Client_No]='" [COLOR=red]&[/color] [Job Register]![Client_No] & "'"), [SubJob_Name])

 

[aquinox]

Thank you JoeAtWork for your reply.

That fixes the error message but it prompts for Job Register as soon as i get out of the designview mode. I then removed [Job Register] from the code as follows

=iif([SubJob_Name] = "", dlookup("[Job_Name]","Job Register","[Industry_No]='" & [Industry_No] & "' AND [Client_No]='" & [Client_No] & "' AND [Job_No]='" & [Job_No] & "'"), [SubJob_Name])

this time, no prompts but the textbox is empty (no value). Not sure if the codes is right. The logic is if the [SubJob_Name] is empty (no value), go and look up in the table [Job Register] where [SubJob Register]![Industry_No] = [Job Register]![Industry_No] and [SubJob Register]![Client_No] = [Job Register]![Client_No] and [SubJob Register]![Job_No] = [Job Register]![Job_No] otherwise, shows [SubJob_Name]. it think that's right but the codes doesn't do what it's supposed to do.

Is there any way i can find out why it didn't work?

Thank you in advance

 

[JoeAtWork]

You are leaving out some crucial information, so I am making the following assumptions:

The control is on a subform embedded in a main form that is called [Job Register]. If that is the case, I think you want something like:

=iif([SubJob_Name] = "", dlookup("[Job_Name]","Job Register","[Industry_No]='" & [Forms]![Job Register]![Industry_No] & "' AND [Client_No]='" & [Forms]![Job Register]![Client_No] & "' AND [Job_No]='" & [Forms]![Job Register]![Job_No] & "'"), [SubJob_Name])

 

[aquinox]

Thank you JoeAtWork for your reply.

Sorry for not leaving out some crucial information here. In the database, i have tables, "Job Register" and "SubJob Register". For example
In the Job Register table, we may have records like
000100 - 00(Industry_No), 01(Client_No), 00(Job_No)

In the SubJob Register table, we have
00010000 - 00(Industry_No), 01(Client_No), 00(Job_No), 00(SubJob_No)
00010001 - the same job but there is a subjob for this job

In the job register table, all the jobs have their own job name. Whereas, in the subjob register table, it only has job name if there is a subjob

for example in the job register table
Job Name
000100 Major Cleaning

in the SubJob register table,
SubJob Name
00010000
00010001 Rubbish Removal

I have a report produced based on the subjob register table. my problem here is that when it comes to the job name, there are lots of blank fields in the SubJob Name. So avoiding these blank fields in the job name field in the report, i put in a new textbox in the report that has the above codes in its control source.

If the SubJob Name is empty, get the name from the "Job Register" table given it has the same industry no, client no, and job no. So in the report, i can see as follows

00010000 Major Cleaning
00010001 Rubbish Removal
...

Hope i explained it well

Thank you in advance