calcuation based on date

[Cordeiro82]

Hello All,

I have a future orders table that contains a date field [datedue]. The table is refreshed everyday, and the dates begin with the day of the refresh onwards.

I would like to create an additional field that is populated with the following text:

"Today" for min[datedue] (which would also be the day of the refresh)

"1" to "4" for the next 4 dates, and ">4" for all dates after that.

Any help or nudge in the right direction would be extremely helpful.

Thanks,

Cordeiro82

 

[n554386]

Sorry but I don't usnderstand what you are trying to do. Can you please give an example of what you are trying to achieve and I am sure someone could help you out. I know I can for sure but I don't understand the question.

 

[r937]

SELECT datedue
     , IIF(datedue-date()=0,"Today",
       IIF(datedue-date()=1,"1",
       IIF(datedue-date()=2,"2",
       IIF(datedue-date()=3,"3",
       IIF(datedue-date()=4,"4",">4"))))) AS whendue
  FROM ...

r937.com | rudy.ca

 

[JerryKlmns]

I wouldn't store a calculated field in a table but rather use a calculated field in a query along with a function. Function could be some nested IIFs (as rudy demostrates perfectly) or a real function in a module using VBA. The last one would be the most appropriate.

Option Explicit

Function myDateDescription(myDueDate As Date) As String
Dim myDescription As String

Select Case myDueDate 
 Case Date()
    myDescription = "Today"
 Case Date()+1 to Date()+4
    myDescription = "1 to 4"
 Case > Date()+4
    myDescription = ">4"
 Case Else
'Obviously this is for past dates
    myDescription = ""
End Select
myDateDescription = myDescription 
End Function

Now the query should be

SELECT * , myDateDescription([datedue])
FROM yourTableName;

If you still want to store that to a text field in the table, the query should be

UPDATE yourTableName
SET yourDateDescriptionField = myDateDescription([datedue])

 

[Cordeiro82]

Thanks guys.

I would like to piggyback this question with another one. Now that I have 6 slots for future orders my crosstab looks something like this

Material | Today | 1 | 2 | 3 | 4 | >4
A
B
C
When I try to link this crosstab to another table, say inventory. It works fine if the crosstab headings are constants like in this example.

But if I change the crosstab heading to a variable (the actual date) the query linking the two cannot recognize all the crosstab column headings after each refresh since they are not the same. EXAMPLE: The FUTURE_Crosstab fields in the SQL statement below are variables, however the query is running them as constants.

SELECT FUTURE_Crosstab.[30-Nov],
FUTURE_Crosstab.[1-Dec],
FUTURE_Crosstab.[3-Dec],
FUTURE_Crosstab.[>3-Dec],
Inventory_Crosstab.Material,
Inventory_Crosstab.Unrestricted
FROM FUTURE_Crosstab LEFT JOIN Inventory_Crosstab ON FUTURE_Crosstab.Material = Inventory_Crosstab.Material;


This there a way to work around this problem so the query updates the column headings based on the dates which is the variable?