invalid syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to set the status of certain deliverables. I get an invalid
syntax error with this statment. I am sure I am missing parentheses somewhere
but I am not sure where.


=IIf([Date Rec'd]>[Dute Date],â€Past Dueâ€,IIf([Dute Date]<Date(),â€Past
Dueâ€,IIf([Date Rec'd]<[Dute Date],â€On Timeâ€,â€Pendingâ€)))


Thanks
 
I am trying to set the status of certain deliverables. I get an invalid
syntax error with this statment. I am sure I am missing parentheses somewhere
but I am not sure where.


=IIf([Date Rec'd]>[Dute Date],”Past Due”,IIf([Dute Date]<Date(),”Past
Due”,IIf([Date Rec'd]<[Dute Date],”On Time”,”Pending”)))


Thanks
Click to expand...

Is it really "Dute Date" as opposed to "Due Date"?

let's take this nested IIF apart:

=IIf([Date Rec'd]>[Dute Date],
”Past Due”, << true branch 1
IIf([Dute Date]<Date(), << false branch 1
”Past Due”, << true branch 2
IIf([Date Rec'd]<[Dute Date], << false branch 2
”On Time”, << true branch 3
”Pending”))) << false branch 3

You might have better luck with the Switch() function - see the online
help in the VBA editor.

John W. Vinson[MVP]
 
No, dute date is a typos but it is a consistant typo - I am afraid I would
find all the places that I have it so I have left it...

Can I use the switch function in forms and reports/queries?




John Vinson said:
I am trying to set the status of certain deliverables. I get an invalid
syntax error with this statment. I am sure I am missing parentheses somewhere
but I am not sure where.


=IIf([Date Rec'd]>[Dute Date],â€Past Dueâ€,IIf([Dute Date]<Date(),â€Past
Dueâ€,IIf([Date Rec'd]<[Dute Date],â€On Timeâ€,â€Pendingâ€)))


Thanks
Click to expand...

Is it really "Dute Date" as opposed to "Due Date"?

let's take this nested IIF apart:

=IIf([Date Rec'd]>[Dute Date],
â€Past Dueâ€, << true branch 1
IIf([Dute Date]<Date(), << false branch 1
â€Past Dueâ€, << true branch 2
IIf([Date Rec'd]<[Dute Date], << false branch 2
â€On Timeâ€, << true branch 3
â€Pendingâ€))) << false branch 3

You might have better luck with the Switch() function - see the online
help in the VBA editor.

John W. Vinson[MVP]
Click to expand...
 
If you want to repeat an expression like this in several places (forms and
reports/queries), you might be better off to create a small function that
can easily be modified if/when you want to change the text.

Public Function GetDueMsg(pdatRecd as Date, pdatDue as Date) as String
Select Case True
Case pdatRecd > pdatDue
GetDueMsg = "Past Due"
Case pdatDue < Date
GetDueMsg = "Past Due"
Case pdatRecd<pdatDue
GetDueMsg = "On Time"
Case Else
GetDueMsg = "Pending"
End Select
End Function
Save this function in a standard module and name the module
"modBusinessRules".
You can then use this function in queries, control sources, etc.
=GetDueMsg([Date Rec'd], [Dute Date])


--
Duane Hookom
MS Access MVP


MMH said:
No, dute date is a typos but it is a consistant typo - I am afraid I would
find all the places that I have it so I have left it...

Can I use the switch function in forms and reports/queries?




John Vinson said:
I am trying to set the status of certain deliverables. I get an invalid
syntax error with this statment. I am sure I am missing parentheses
somewhere
but I am not sure where.


=IIf([Date Rec'd]>[Dute Date],"Past Due",IIf([Dute Date]<Date(),"Past
Due",IIf([Date Rec'd]<[Dute Date],"On Time","Pending")))


Thanks
Click to expand...

Is it really "Dute Date" as opposed to "Due Date"?

let's take this nested IIF apart:

=IIf([Date Rec'd]>[Dute Date],
"Past Due", << true branch 1
IIf([Dute Date]<Date(), << false branch 1
"Past Due", << true branch 2
IIf([Date Rec'd]<[Dute Date], << false branch 2
"On Time", << true branch 3
"Pending"))) << false branch 3

You might have better luck with the Switch() function - see the online
help in the VBA editor.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
No, dute date is a typos but it is a consistant typo - I am afraid I would
find all the places that I have it so I have left it...

Can I use the switch function in forms and reports/queries?
Click to expand...

You can use Switch() anywhere you can use IIF().

Duane's function is probably your best bet though - easier to document
and understand!

John W. Vinson[MVP]
 
Back
Top