2 ++ Question

[all7holly]

Hello,

How can I use 2 ++ on one line in a query?

Thanks,
Holly

 

[spikepl]

What prevents you from using 2 ++ on one line in a query? I have no clue as to what 2 ++ is, but that shouldn't hold you back.

 

[all7holly]

The error I get is below.
the expression you entered contains invalid syntax you may have entered an operator such as the + operator in an expression without a corresponding command

I am trying to add 30 to NET 30 terms and add 45 to NET 45 terms; to give me the date the invoice is due.

 

[spikepl]

So access is telling you, just like me, that it also has no clue as to what 2 ++ is. That is kind of a hint that whatever you are trying to do is not the Access way.

How are you doing whatever it is you are doing: show the code (or SQL) or else it will just be guessing.

 

[all7holly]

InvoiceDueDate: IIf([payment terms]="NET 30",[invoice date sent]+30,IIf([payment terms]="Net 45",[invoice date sent]+45,IIf([payment terms]="Due Upon Receipt","Due Upon Receipt Past Due","UNKNOWN")))

 

[spikepl]

Your parens are messed up. Look at the last IIF on its own and fix it. Take it out and check if the parens are OK elsewhere and then reinsert it.

Actually this is always the procedure when a complex expression gives problems - rebuild it bit by bit, after checking each bit by itself.

Update: Hmm I better eat some grovel. I checked the expression and it seems OK.

I presume [invoice date sent] is of data type Date/Time, and the field in which InvoiceDueDate is is of type Text? It is always tricky to save data of two different types into the same field, as you expression attempts to do.

 

[MSAccessRookie]

InvoiceDueDate: IIf([payment terms]="NET 30",[invoice date sent]+30,IIf([payment terms]="Net 45",[invoice date sent]+45,IIf([payment terms]="Due Upon Receipt","Due Upon Receipt Past Due","UNKNOWN")))

Spikepl is right here. In two of the IIf() results, the value of InvoiceDueDate is in Date/Time Format, and in the other two it is in String Format. One way to eliminate the conflict could be to convert the Date/Time responses to Strings using cStr().

 

[all7holly]

I guess I don't understand. Could you please show and example?

 

[MSAccessRookie]

I have color coded your IIf() Statement to display values that are Date/Time Type in GREEN, and values that are String Type in RED. I have also added a new Function to convert the Date/Time Type to String (cStr()) Type that is displayed in PURPLE.

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]IIf([payment terms]="NET 30", [/FONT][/COLOR][/COLOR][/FONT]
[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana][COLOR=seagreen][B][COLOR=darkorchid]   cStr([/COLOR][invoice date sent]+30[COLOR=darkorchid])[/COLOR][/B][/COLOR], [/FONT][/COLOR][/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   [COLOR=black][FONT=Verdana]IIf([payment terms]="Net 45", [/FONT][/COLOR][/COLOR][/FONT]
[FONT=Verdana][COLOR=black]       [COLOR=black][FONT=Verdana][COLOR=seagreen][B][COLOR=black][FONT=Verdana][COLOR=seagreen][B][COLOR=darkorchid]cStr([/COLOR][[/B][/COLOR][/FONT][/COLOR]invoice date sent]+45[COLOR=darkorchid])[/COLOR][/B][/COLOR], [/FONT][/COLOR][/COLOR][/FONT]
[FONT=Verdana][COLOR=black]       [COLOR=black][FONT=Verdana]IIf([payment terms]="Due Upon Receipt", [/FONT][/COLOR][/COLOR][/FONT]
[FONT=Verdana][COLOR=black]           [COLOR=black][FONT=Verdana][COLOR=red][B]"Due Upon Receipt Past Due"[/B][/COLOR], [/FONT][/COLOR][/COLOR][/FONT]
[FONT=Verdana][COLOR=black]           [COLOR=black][FONT=Verdana][COLOR=red][B]"UNKNOWN"[/B][/COLOR][/FONT][/COLOR][/COLOR][/FONT]
[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]        )[/FONT][/COLOR][/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   [COLOR=black][FONT=Verdana])[/FONT][/COLOR][/COLOR][/FONT]
[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana])[/FONT][/COLOR]
[/COLOR][/FONT]

 

[all7holly]

It Worked perfectly! Thank you!

 

[MSAccessRookie]

It Worked perfectly! Thank you!

I was glad to be able to help, and hope that my explanation made sense