Calculating dates based on multiple fields

[Norah B]

Hi -

I am trying to develop a form that contains a field that shows a date calculated based on a text field and other date. I figured out how to build a query to get this for one sort of value, but what I really, really need is to do this for multiple situations:

If [Priority]="High", then [Received Date]+7 OR
If [Priority]="Medium", then [Received Date]+14 OR
If [Priority]="Low", then [Received Date]+17

I couldn't get a query to accept more than one of the above; I also tried creating a macro that would read a query per option - no go.

This may be overly ambitious - as an alternative, is it possible to create one report, sorted by priority, that uses each of these?

Thanks so much!
Norah

 

[Mile-O]

If there's a possibility that the [Priority] may not have a value, use this in the Field.

IIf([Priority] = "High", [Received Date] + 7, IIf([Priority] = "Medium", [Received Date] + 14, IIf([Priority] = "Low", [Received Date] + 17, Null)))

Otherwise, you can use this:

IIf([Priority] = "High", [Received Date] + 7, IIf([Priority] = "Medium", [Received Date] + 14, [Received Date] + 17))

 

[Norah B]

Thanks for the information; however, I can't seem to get the statement to work - by setting as criteria for the field in the form, in a query, as a macro. I'm obviously missing something - could you please clarify where I'm supposed to apply it?

Thanks much.

 

[jjturner]

You should be able to just use (paste) the formula Mile-O-Phile provided in your ControlSource (in the Properties box on the 'Data' tab) for the text box where you want the calculated date to be displayed, preceded by the '=' sign:

=IIf([Priority]="High", etc., etc.

 

[neileg]

Alternatively, create a table that holds your priority types and a numeric field for the days e.g.

Priority advDays
__________0
High_______7
Medium____14
Low_______17

Bring this table into your query and calculate your date from [Received Date]+[advDays]

I prefer not to hard code this kind of calculation because someone always invents a new priority type or changes the day value at a later date.

 

[Norah B]

Thanks for the advice - I went the query route - and used it to create a report. I appreciate the help!

 

[cunninghamsa]

Gidday,

Sorry to resuscitate this one but it seemed to be just the ticket for what I was hoping to accomplish. I used the following formula in a text box (Estimate Completion Date) based on the user's selection in a combo box (Service Standards):

IIf ([ServiceStandards] = "<7 days URGENT", [ReceivedDate] + 7, IIf ([ServiceStandards] = "<14 days", [ReceivedDate] +14, IIf [ServiceStandards] = "<28 days", [ReceivedDate] + 28, IIf [ServiceStandards] = "<42 days", [ReceivedDate] + 42, IIf [ServiceStandards] = ">42 days", [ReceivedDate] + 49))

If I stick to entering the first clause of the formula into the ControlSource of EstimateCompletionDate text box, everything works fine. When I add the rest I get various results:

Gives me the first date okay; or
"Name?" appears in text box as if system doesn't recognize formula syntax;

In addition, when I go back into the properties box, the formula will either appear as typed or will truncate after first clause.

Can someone tell me what I'm doing wrong? It seemed so simply efficient...

Thank you! Love this forum!

 

[Mile-O]

[ServiceStandards], I'd guess, is a combobox.

Is the column that has the information you are selecting the Bound Column?

 

[cunninghamsa]

Hello Mile-O-Phile,

Yes, you're right, ServiceStandards is a combobox with five possible options. I may not have understood your question corrrectly but my comprehension of bound column is as follows: If I want a value retrieved from text entered into a field, I enter this field name into ControlSource property. If, however, I want to calculate the value, I have to enter a formula into ControlSource. In my mind, it's an either/or choice not a combination of the two.

By the way, my formula begins with = . I've also tried preceeding the = sign with the name of the table and field. Doesn't change anything.

 

[cunninghamsa]

Hello Mile-O-Phile,

Just thought I'd let you know that I figured out the errors in syntax for my formula. Takes me a while but I get it eventually. Just a few missing parentheses were causing me all this grief. Good grief!

For anyone out there who should be at the same developmental stage as I am (and who should want to accomplish a similar task), here's the correct expression (with all the bells and whistles):

=IIf([Service Standards]="<7 days URGENT",[ReceivedDate]+7,IIf([Service Standards]="<14 days",[ReceivedDate]+14,IIf([Service Standards]="<28 days",[ReceivedDate]+28,IIf([Service Standards]="<42 days",[ReceivedDate]+42,IIf([Service Standards]=">42 days",[ReceivedDate]+49)))))

Ciao!

 

[Mile-O]

You might find it even easier to put a VBA function of this into your database. It would cut out the need for such a big formula and may run faster as the code can eliminate the need for multiple immediate if statements.

Public Function GetDate(ByVal strService As String, ByVal dteReceived As Date) As Date

    Dim intDays As Integer

    Select Case strService
        Case Is = "<7 days URGENT"
            intDays = 7
        Case Is = "<14 days"
            intDays = 14
        Case Is = "<28 days"
            intDays = 28
        Case Is = "<42 days"
            intDays = 42
        Case Is = ">42 days"
            intDays = 49
    End Select
    
    GetDate = DateAdd("d", intDays, dteReceived)

End Function

Then, where you have the ControlSource with the huge expression put:

=GetDate([ServiceStandard], [ReceivedDate)