Problem with IIF statment

T

Tony Williams

Can someone tell me what's wrong with this rather complicated IIF statement,
I keep getting syntax error message.

=IIf(IsNull[txtsubject],(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtmemnbr]")
& " " &
DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtmemnbr]")),(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtempmemnumbera]")
& " " &
DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]=Forms!frmMain!Subform1![txtempmemnumbera])))

Thanks
Tony
 
G

geppo

Ciao said:
Can someone tell me what's wrong with this rather complicated IIF
statement, I keep getting syntax error message.
Click to expand...

This way if the criterions are type text

=IIf(IsNull[txtsubject],(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]='" &Forms!frmMain!Subform1![txtmemnbr] &"'") & " " & DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]= '"&Forms!frmMain!Subform1![txtmemnbr]&"'")),(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]="&Forms!frmMain!Subform1![txtempmemnumbera] &"'") & " " & DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]="&Forms!frmMain!Subform1![txtempmemnumbera] &"'")))> Thanks> TonyThis way if the criterions are type numerical =IIf(IsNull[txtsubject],(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]= '" &Forms!frmMain!Subform1![txtmemnbr]) & " " & DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]= '"&Forms!frmMain!Subform1![txtmemnbr])),(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]="& Forms!frmMain!Subform1![txtempmemnumbera]) & " " & DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]="&Forms!frmMain!Subform1![txtempmemnumbera])))--CiaoGeppo
 
T

Tony Williams

Sorry geppo still getting syntax error?
Tony
geppo said:
Ciao said:
Can someone tell me what's wrong with this rather complicated IIF
statement, I keep getting syntax error message.
Click to expand...

This way if the criterions are type text

=IIf(IsNull[txtsubject],(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]='"
&Forms!frmMain!Subform1![txtmemnbr] &"'") & " " &
DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]=
'"&Forms!frmMain!Subform1![txtmemnbr]&"'")),(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]="&Forms!frmMain!Subform1![txtempmemnumbera]
&"'") & " " &
DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]="&Forms!frmMain!Subform1![txtempmemnumbera]
&"'")))> Thanks> TonyThis way if the criterions are type numerical
=IIf(IsNull[txtsubject],(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]=
'" &Forms!frmMain!Subform1![txtmemnbr]) & " " &
DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]=
'"&Forms!frmMain!Subform1![txtmemnbr])),(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]="&
Forms!frmMain!Subform1![txtempmemnumbera]) & " " &
DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]="&Forms!frmMain!Subform1![txtempmemnumbera])))--CiaoGeppo
Click to expand...
 
G

geppo

Ciao said:
Sorry geppo still getting syntax error?
Tony
geppo said:
Ciao said:
Can someone tell me what's wrong with this rather complicated IIF
statement, I keep getting syntax error message.
Click to expand...

This way if the criterions are type text

=IIf(IsNull[txtsubject],(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]='"
&Forms!frmMain!Subform1![txtmemnbr] &"'") & " " &
DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]=
'"&Forms!frmMain!Subform1![txtmemnbr]&"'")),(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]="&Forms!frmMain!Subform1![txtempmemnumbera]
&"'") & " " &
DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]="&Forms!frmMain!Subform1![txtempmemnumbera]
&"'")))> Thanks> TonyThis way if the criterions are type numerical
=IIf(IsNull[txtsubject],(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]=
'" &Forms!frmMain!Subform1![txtmemnbr]) & " " &
DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]=
'"&Forms!frmMain!Subform1![txtmemnbr])),(DLookUp("[txtfirstname]","[tblindividual]","[txtmemnumber]="&
Forms!frmMain!Subform1![txtempmemnumbera]) & " " &
DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]="&Forms!frmMain!Subform1![txtempmemnumbera])))--CiaoGeppo
Click to expand...
Click to expand...


you have to use the semicolons (;) instead of the commas (,)
 
J

John W. Vinson

Can someone tell me what's wrong with this rather complicated IIF statement,
I keep getting syntax error message.
Click to expand...

Well, let's break it down:

=IIf(IsNull[txtsubject],

Right there. IsNull is a function and needs parentheses to delimit its
argument:

IsNull([txtsubject])


John W. Vinson [MVP]
 
G

geppo

this way if the criterions are type numerical;


=IIf(IsNull([txtsubject]);DLookUp("[txtfirstname]";"[tblindividual]";"[txtmemnumber]="&
Forms!frmMain!Subform1![txtmemnbr]) & " " &
DLookUp("[txtsurname]";"[tblindividual]"; "[txtmemnumber]="&
Forms!frmMain!Subform1![txtmemnbr]);DLookUp("[txtfirstname]";"[tblindividual]";"[txtmemnumber]="&
Forms!frmMain!Subform1![txtempmemnumbera]) & " " &
DLookUp("[txtsurname]";"[tblindividual]";"[txtmemnumber]="&
Forms!frmMain!Subform1![txtempmemnumbera]))
 
T

Tony Williams

Thanks John
Tony
John W. Vinson said:
Can someone tell me what's wrong with this rather complicated IIF
statement,
I keep getting syntax error message.
Click to expand...

Well, let's break it down:

=IIf(IsNull[txtsubject],

Right there. IsNull is a function and needs parentheses to delimit its
argument:

IsNull([txtsubject])


John W. Vinson [MVP]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Referencing from a combo box 4
Yet another DLookup problem! 5
Help with If statement 17
DLookup problem 3
Continuing combo box problem 2
Storing a calculated value 4
Help with form/subform problem 1
Problem with DLookup on Report 3

Top