Day Date Problem

[Royal Presence]

Hi,

I put this in a query:

Expr3: Date()+[Ship_Date]

in order to forecast. The purpose is to provide production managers with seeing certain time frames, what is already on order. If they could look at any given day and see what's out there for six, ten, 17, whatever days ahead it would be helpful. I did this in Excel for them, but we are moving to Access and testing it out. This is one issue I can't figure.

With the above I get the following:

8/12/2108, rather than in days. I have tried using 'd', but nothing is returned. Are there any suggestions?
We are using 2002.

Thank you.

Vaya con Dios

 

[SydneyGeek]

You need to use the DateAdd function. Look for it in the Expression Builder.

Denis

 

[Royal Presence]

DateAdd still didn't work, kept coming up with incomprehensible numbers. I ended up using the date()-ShipDate, which gives negative and positive numbers. As long as we know the minus are the days ahead and the positives are the days behind, it works. I'll keep trying though.

 

[tlclear]

There are a couple of things you can do.
First of all, calculate the difference between the ship_date and the current date. Something like this: DIFF: order_ship_date!ship_date-Date().
Then, although there should be some way to do this with IIf, create three queries, one for ship dates in the past, ship date = today, and ship date in the future.
In the past (already shipped): STATUS: "SHIPPED " & Abs([DIFF]) & " DAYS AGO" , criteria DIFF < 0
Shipping today:STATUS: "SHIPS TODAY" , CRITERIA DIFF = 0
Ships in the future: STATUS: "SHIPS IN " & [DIFF] & " DAYS" , criteria DIFF > 0
Then do a union query to put all the data in one place
Let me know if this helps

 

[mdmilner]

I'm suspicious the problem is one of two potential problems.
First is field formats - is the [Ship_Date] field a date formatted field or is is a text field that looks like a date?

I'm not sure why this works sometimes but you can try:

Expr3: Date()+TRIM([Ship_Date])

Mike

 

[Royal Presence]

The Ship Date is a date field.

 

[SydneyGeek]

DateAdd still didn't work, kept coming up with incomprehensible numbers. I ended up using the date()-ShipDate, which gives negative and positive numbers. As long as we know the minus are the days ahead and the positives are the days behind, it works. I'll keep trying though.

If ShipDate IS a date field, this is the only approach you can easily take. To clean out the negatives, filter the query -- put >=0 in the Criteria row of the calculated field. If you want, you could also sort ascending so managers get a list of ship dates in order of occurrence.

Denis

 

[Royal Presence]

Thanks- that's a splendid option. Yet, if they pull up by days out, then they will get past ship dates. Maybe I better try the Past Shipped, route too.

I've sorted by pw, so the ascending order of ship dates is intact.

 

[giacomo]

Have you tried using DateDiff('d',Date(),[ship_date]) ?
When I ran it with some test dates I got the same results as when I did date() - [ship_date] except that days in the future a positive instead of negative.

 

[Royal Presence]

Eureka- issue resolved.

It worked! Thanks so much