Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

calcuation based on date

Status
Not open for further replies.
Aug 24, 2005
56
US
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
 
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.
 
Code:
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
 

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.

Code:
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
Code:
SELECT * , myDateDescription([datedue])
FROM yourTableName;

If you still want to store that to a text field in the table, the query should be
Code:
UPDATE yourTableName
SET yourDateDescriptionField = myDateDescription([datedue])
 
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top