Nested IIF statement or Switch? (1 Viewer)

[superfly5203]

I have the following statement in a calculated field which calculates the number of days late a proposal is:

IIf([Contract]="A" And [Change Type]=2 Or [Change Type]=4 Or [Change Type]=9 Or [Change Type]=11,([actualproposal]-[CCO Date])-90,([actualproposal]-[CDapprovaldate])-90)

This code works fine, if one of the 4 change types are dected, it uses the "CCO Date" instead of the "CDapprovaldate" field to calculate the days for proposal delivery. The -90 at the end of the true/ false statements is how long the contractor has to deliver the proposal, so if they took 100 days this calculated field would return a value of 10, as in 10 days late. My problem is, I have three contracts A, B, and C and they all have different proposal delivery dates 90, 60, and 30.

I tried to put another IIf statement at the end of this one, and it said I had incorrect syntax somewhere. I have seen a lot of nested IIf statements and switch statements while researching this problem, but I haven't seen one evaluate two different criteria (maybe I didn't look long enough ).

So how can I combine these three IIf statements into one working statement in one calculated field?

IIf([Contract]="A" And [Change Type]=2 Or [Change Type]=4 Or [Change Type]=9 Or [Change Type]=11,([actualproposal]-[CCO Date])-90,([actualproposal]-[CDapprovaldate])-90)
IIf([Contract]="B" And [Change Type]=2 Or [Change Type]=4 Or [Change Type]=9 Or [Change Type]=11,([actualproposal]-[CCO Date])-30,([actualproposal]-[CDapprovaldate])-30)
IIf([Contract]="C" And [Change Type]=2 Or [Change Type]=4 Or [Change Type]=9 Or [Change Type]=11,([actualproposal]-[CCO Date])-60,([actualproposal]-[CDapprovaldate])-60)

 

[CJ_London]

assuming these are your real values and this is in a query (vba does not use IN)

[actualproposal]-iif([Change Type] IN (2,4,9,11),[CCO Date],[CDapprovaldate])- switch(Contract="A",90,Contract="B",30,Contract="C",60)

 

[plog]

At some point its time to move this to a function--you are very close. What you would do is create a function with a signature like this:

get_DaysLate(in_Contract, in_ChangeType, in_ActualProposal, in_CCODate, in_ApprovalDate)

Then, in your query you would use it like so:

DaysLate: get_DaysLate([Contract], [ChangeType], [actualproposal], [CCO Date], CDapprovaldate])

Then the function would perform your logic--in how ever many lines you need to accomplish it-- and return your result.

For your issue, do you have a Contract table? Switch statements in queries are usually a hack around a poor structure. Instead of the switch, you should have a Contract table that has a field with ContractType and the amount of days to use for it. Then, you simply JOIN that table and then use the days in that table.

 

[superfly5203]

This isn't in a query, it's for a calculated field in a table.

I don't have a contract table, I thought about making one but there would be literally 3 entries on there and nothing else. I have a table with all of the specifics for each proposal and i have another table that has the different "change types" values. The expression CJ provided says this expression can't be used in calculated field.

This field only exists for reporting purposes, so should I just take it out of my form/table and only calculate it when a query is run using one of the expressions provided?

 

[CJ_London]

calculated fields are very limiting as you have discovered. You certainly can't use a udf per plog's suggestion. They can also cause performance issues

This field only exists for reporting purposes, so should I just take it out of my form/table and only calculate it when a query is run using one of the expressions provided?

yes

 

[superfly5203]

Thanks. I'll take it out and when I create a query I'll use some of the expressions provided here.

 

[superfly5203]

assuming these are your real values and this is in a query (vba does not use IN)

[actualproposal]-iif([Change Type] IN (2,4,9,11),[CCO Date],[CDapprovaldate])- switch(Contract="A",90,Contract="B",30,Contract="C",60)

I used this code and it worked great. However, I'm having a rather infuriating problem with it now. I was adding this to an existing query and the query won't save this formula. Well, it does save, but when I open the query again it is no longer anywhere in the query. I've made new queries and it disappears in those as well. I'm rather new to access, but I've never seen anything like this before!

Here is the code adapted for use, that does work in another query:

proposal Delta: [actual_proposal]-IIf([tblMod.TypeLetter] In ('B','D'),[CCO_mod_date],[CD_approval])-Switch([Forms]![Search_Award_Fee]![ContractCombo]="Boeing",90,[Forms]![Search_Award_Fee]![ContractCombo]="ESOC",30,[Forms]![Search_Award_Fee]![ContractCombo]="C2V2 ",60)

And here is the code that keeps disappearing:

Proposal Delta: [actual_proposal]-IIf([tblMod.TypeLetter] In ('B','D'),[CCO_mod_date],[CD_approval])-Switch([Forms]![Search_Date]![Contractlist]="Boeing",90,[Forms]![Search_Date]![Contractlist]="ESOC",30,[Forms]![Search_Date]![Contractlist]="C2V2 ",60)

They are practically identical! I've searched everywhere for some help on this issue but all the results are talking about how to delete fields with a query.

 

[CJ_London]

it may be to do with the type of query? what are the queries? select, update, insert, make table? If not a select and you don't assign proposal delta to a field or criteria, it won't be retained

 

[superfly5203]

All of my queries are select. Here is the SQL from the query that does work

SELECT tblSSCN.SSCN_Number, tblSSCN.SSCNTitle, tblDates.actual_proposal, tblContracts.ContractName, Abs([tblDollars]![prop_price]) AS [Proposed Price], [actual_proposal]-IIf([tblMod.TypeLetter] In ('B','D'),[CCO_mod_date],[CD_approval])-Switch([Forms]![Search_Award_Fee]![ContractCombo]="Boeing",90,[Forms]![Search_Award_Fee]![ContractCombo]="ESOC",30,[Forms]![Search_Award_Fee]![ContractCombo]="C2V2 ",60) AS [Proposal Delta]
FROM tblSSCN INNER JOIN (tblDollars INNER JOIN (tblDates INNER JOIN (tblContracts INNER JOIN tblMod ON tblContracts.ContractID = tblMod.Contract) ON tblDates.DatesID = tblMod.Dates) ON tblDollars.DollarsID = tblMod.Dollars) ON tblSSCN.SSCNID = tblMod.SSCN
WHERE (((tblDates.actual_proposal)>([Forms]![Search_Award_Fee]![StartDate]) And (tblDates.actual_proposal)<([Forms]![Search_Award_Fee]![EndDate])) AND ((tblContracts.ContractName)=[Forms]![Search_Award_Fee]![ContractCombo]) AND ((tblMod.TypeLetter)="A" Or (tblMod.TypeLetter)="B" Or (tblMod.TypeLetter)="C" Or (tblMod.TypeLetter)="D"))
ORDER BY tblDates.actual_proposal;

and here is one where I put in the code and it is not retained:

SELECT tblContracts.ContractName, tblDates.actual_def, tblDates.CD_approval, tblDates.actual_proposal, tblDates.actual_te, tblDates.actual_ige, tblDates.actual_ppm, tblDates.actual_neg, tblDates.actual_cdup, tblDollars.prop_price, tblDollars.neg_price, tblMod.TypeLetter, tblModType.TypeCode, tblSSCN.SSCN_Number, tblSSCN.SSCNTitle, IIF (IsNull(CCO_mod_date), DateDiff('d',CD_approval, actual_def), DateDiff('d',CCO_mod_date, actual_def)) AS finalchangeage, DateDiff('d',actual_ppm, actual_neg)-24 AS deltaNEG, DateDiff('d',actual_te, actual_ppm)-21 AS deltaPPM, DateDiff('d',actual_proposal, actual_te)-21 AS deltaTE, IIF (IsNull (actual_cdup), (DateDiff('d',actual_neg, actual_ppm)-12), (DateDiff('d',actual_neg, actual_cdup)-12)) AS deltaCDUP, IIF (IsNull (actual_cdup), (DateDiff('d',actual_neg, actual_def)-12), (DateDiff('d',actual_cdup, actual_def)-12)) AS deltaDEF, [actual_proposal]-IIf([tblMod.TypeLetter] In ('B','D'),[CCO_mod_date],[CD_approval])-Switch([Forms]![Search_Date]![Contractlist]="Boeing",90,[Forms]![Search_Date]![Contractlist]="ESOC",30,[Forms]![Search_Date]![Contractlist]="C2V2 ",60) AS [Proposal Delta]
FROM tblSSCN INNER JOIN (tblModType INNER JOIN (tblDollars INNER JOIN (tblDates INNER JOIN (tblContracts INNER JOIN tblMod ON tblContracts.ContractID = tblMod.Contract) ON tblDates.DatesID = tblMod.Dates) ON tblDollars.DollarsID = tblMod.Dollars) ON tblModType.TypeCode = tblMod.TypeLetter) ON tblSSCN.SSCNID = tblMod.SSCN
WHERE tblContracts.ContractName IN('Boeing') AND tblMod.TypeLetter IN('A','B','C') AND tblDates.actual_proposal BETWEEN [Forms]![Search_Date]![startdate] and [Forms]![Search_Date]![enddate];

Before I run the query, I can see this field in the datasheet view. However, once I close it and run it through a command button on a form its gone. I can look in the SQL and its not in the Select statement. I have a few other ones in that second set of SQL, and there is not a problem with those, just this one. I don't get it. This field doesn't have it's own field in any table, but neither do the other calculated ones, but they work just fine.

 

[CJ_London]

Only other thing I can suggest is to compact and repair the db. If this doesn't work, can you post your db? remove or disguise any sensitive data as well as tables and forms not required, compact it and zip it

 

[superfly5203]

I did the compact and repair, and nothing changed. Is there an easy way to remove all the sensitive info from a database without messing something up? There are lots of fields I would need to delete or make zero before I could upload.

 

[CJ_London]

create a new db and import the relevant tables/forms etc - one of the options is to import table schema only (i.e. no data)

Then either type in some dummy data or copy/paste some relevant records from the original db.

 

[superfly5203]

I think I found my problem. I feel pretty dumb really. I total forgot that my form builds an SQL statement every time i hit the command button so it rewrites the query every time. I went back to check everything one last time and I saw that, and like I said, felt pretty dumb. thanks for helping!

 

[superfly5203]

I tried to put the code in the command button which compiles the SQL statement, but now i get a run time error 3141 The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.

Earlier it was said you can't use the IN with VBA, but since this turns into a SQL statement, does that allow it to work?

here is the SQL code in the VBA builder:

strSQL = "SELECT tblContracts.ContractName, tblDates.actual_def, tblDates.CD_approval, tblDates.actual_proposal, tblDates.actual_te, tblDates.actual_ige, tblDates.actual_ppm, tblDates.actual_neg, tblDates.actual_cdup, tblDollars.prop_price, tblDollars.neg_price, tblMod.TypeLetter, tblModType.TypeCode, tblSSCN.SSCN_Number, tblSSCN.SSCNTitle, " & _
    "IIF (IsNull(CCO_mod_date), DateDiff('d',CD_approval, actual_def), DateDiff('d',CCO_mod_date, actual_def)) AS finalchangeage, " & _
    "DateDiff('d',actual_ppm, actual_neg)-24 AS deltaNEG, " & _
    "DateDiff('d',actual_te, actual_ppm)-21 AS deltaPPM, " & _
    "DateDiff('d',actual_proposal, actual_te)-21 AS deltaTE, " & _
    "actual_proposal-IIf(TypeLetter In ('B','D'),CCO_mod_date,CD_approval)-Switch(contractlist='Boeing',90,contractlist='ESOC',30,Contractlist='C2V2',60) AS Proposal Delta, " & _
    "IIF (IsNull (actual_cdup), (DateDiff('d',actual_neg, actual_ppm)-12), (DateDiff('d',actual_neg, actual_cdup)-12)) AS deltaCDUP, " & _
    "IIF (IsNull (actual_cdup), (DateDiff('d',actual_neg, actual_def)-12), (DateDiff('d',actual_cdup, actual_def)-12)) AS deltaDEF " & _
    "FROM tblSSCN INNER JOIN (tblModType INNER JOIN (tblDollars INNER JOIN (tblDates INNER JOIN (tblContracts INNER JOIN tblMod ON tblContracts.ContractID = tblMod.Contract) ON tblDates.DatesID = tblMod.Dates) ON tblDollars.DollarsID = tblMod.Dollars) ON tblModType.TypeCode = tblMod.TypeLetter) ON tblSSCN.SSCNID = tblMod.SSCN " & _
    "WHERE tblContracts.ContractName IN(" & strCriteria & ") AND tblMod.TypeLetter IN(" & strCriteria2 & ") AND tblDates.actual_proposal BETWEEN [Forms]![Search_Date]![startdate] and [Forms]![Search_Date]![enddate] ; "

 

[CJ_London]

you can use it in VBA in this case because you are building a sql string to run

output the sql string (debug.print strsql) to the immediate window then copy and paste into a new query window and see what it looks like - you'll find it easier to find the error there.

Personally I avoid using the string continuation because it is easier to miss something - usually spaces

try

 strSQL = "SELECT tblContracts.ContractName, tblDates.actual_def, tblDates.CD_approval, tblDates.actual_proposal, tblDates.actual_te, tblDates.actual_ige, tblDates.actual_ppm, tblDates.actual_neg, tblDates.actual_cdup, tblDollars.prop_price, tblDollars.neg_price, tblMod.TypeLetter, tblModType.TypeCode, tblSSCN.SSCN_Number, tblSSCN.SSCNTitle, " 
 strSQL = strSQL & "IIF (IsNull(CCO_mod_date), DateDiff('d',CD_approval, actual_def), DateDiff('d',CCO_mod_date, actual_def)) AS finalchangeage, " 
 etc

and if I do use them it is to keep the string visible on the screen without having to scroll left/right

e.g.

 strSQL = "SELECT " & _
 "tblContracts.ContractName,  " & _
 "tblDates.actual_def,  " & _
 "tblDates.CD_approval,  " & _
 "tblDates.actual_proposal,  " & _
 etc

although there is a limit to how many you can use (think it is around 20-30)

 

[superfly5203]

That helped a lot, I found it didn't like the space in my new field after the AS. So i changed AS Proposal Delta to AS Proposal_Delta and it got past that error.

Now it will run the query correctly, but there are two pop ups asking for the 'contractlist'. If I type in a contract name twice, it runs and give the correct out put. That is what my list box is called, i don't know why it's asking for my input after i made my selection.

Oh, I just now thought that since it's multiselect list box it won't work. I'll make it a combo box and see if that works.

Edit: I think the problem is in the WHERE statement, the part where it points to the combo box. It isn't recognizing the selection.

 

[CJ_London]

if your sql is being run with execute or runsql then you can't reference controls in this way, you need to add it in as you have for criteria

"WHERE tblContracts.ContractName IN ([COLOR=red]" & strCriteria & "[/COLOR])...BETWEEN [Forms]![Search_Date]![startdate

]

 

[superfly5203]

I had that in before but took it out, so I put it back in and now I think my string is returning a null value. I'm not getting any results, and if I remove that part of the WHERE statement, everything works (it does show all records instead of matching records of course).