dresdena1
asked on
MS Access 2007 - Add time element to query/table
EE,
I have an Access 2007 database.
I ship using UPS WorldShip.
When UPS WorldShip processes a shipment label it exports the tracking number and estimated delivery date (formatted as: 20141104 - Nov. 4, 2014 - for example) into a table (UPS_Tracking_Info) in Access.
I would like to send customers a (mail merge) email after two weeks asking them to fill out a brief 2 or 3 question survey of what they purchased.
The query that I would need to join the UPS_Tracking_Info, Orders, and Customers table I can take care of. What I would like to know is if there is a way to add 14 days to the delivery_estimate field (delivery_estimate + 14)?
Is there a way of doing this?
Any help would be greatly appreciated.
dresdena1
I have an Access 2007 database.
I ship using UPS WorldShip.
When UPS WorldShip processes a shipment label it exports the tracking number and estimated delivery date (formatted as: 20141104 - Nov. 4, 2014 - for example) into a table (UPS_Tracking_Info) in Access.
I would like to send customers a (mail merge) email after two weeks asking them to fill out a brief 2 or 3 question survey of what they purchased.
The query that I would need to join the UPS_Tracking_Info, Orders, and Customers table I can take care of. What I would like to know is if there is a way to add 14 days to the delivery_estimate field (delivery_estimate + 14)?
Is there a way of doing this?
Any help would be greatly appreciated.
dresdena1
ASKER
eghtebas,
Thank you for the prompt response.
In the UPS_Tracking_Info table, the delivery_estimate field is text.
I made a copy of the table and tried to change the field from text to date/time and I got a data type mismatch error and it deleted all of the data in the delivery_estimate field.
Is there a way to convert it?
Thanks,
dresdena1
Thank you for the prompt response.
In the UPS_Tracking_Info table, the delivery_estimate field is text.
I made a copy of the table and tried to change the field from text to date/time and I got a data type mismatch error and it deleted all of the data in the delivery_estimate field.
Is there a way to convert it?
Thanks,
dresdena1
use CDate() function.
DateAdd("d", 14, CDate(delivery_estimate))
DateAdd("d", 14, CDate(delivery_estimate))
The UPS date format is not one that Access recognizes so you need to convert the string to something Access will recognize as a date first.
DateAdd("d", 14, DateSerial(Left(delivery_e stimate, 4), mid(delivery_estimate, 5, 2), right(delivery_estimate, 2) )
DateAdd("d", 14, DateSerial(Left(delivery_e
ASKER
eghtebas,
I have a query. It has joins from UPS_Tracking_Info, Orders, and Customers.
In the query I have the customers name, email, order_number, tracking_number, and delivery_estimate
When I look at it in Design view I see the queried fields. When I put DateAdd("d", 14, CDate(delivery_estimate)) in the Criteria Field of delivery_estimate I get a "Data type mismatch in criteria expression" error.
In the UPS_Tracking_Info table where delivery_estimate is found I would not know how to apply a function to it.
Any help would be greatly appreciated.
Thanks.
dresdena1
I have a query. It has joins from UPS_Tracking_Info, Orders, and Customers.
In the query I have the customers name, email, order_number, tracking_number, and delivery_estimate
When I look at it in Design view I see the queried fields. When I put DateAdd("d", 14, CDate(delivery_estimate)) in the Criteria Field of delivery_estimate I get a "Data type mismatch in criteria expression" error.
In the UPS_Tracking_Info table where delivery_estimate is found I would not know how to apply a function to it.
Any help would be greatly appreciated.
Thanks.
dresdena1
I see, because the format is like 20141104,
For conversion purpose, use as stated by Pat:
DateAdd("d", 14, DateSerial(Left(delivery_e stimate, 4), mid(delivery_estimate, 5, 2), right(delivery_estimate, 2) )
For conversion purpose, use as stated by Pat:
DateAdd("d", 14, DateSerial(Left(delivery_e
ASKER
eghtebas and PatHartman,
Thank you, but where should I use this?
dresdena1
Thank you, but where should I use this?
dresdena1
in the query that selects the customers you want to contact.
Select ...
From ...
Where SomeDate = DateAdd("d", 14, DateSerial(Left(delivery_e stimate, 4), mid(delivery_estimate, 5, 2), right(delivery_estimate, 2) )
Select ...
From ...
Where SomeDate = DateAdd("d", 14, DateSerial(Left(delivery_e
ASKER
PatHartman,
When I add the Where statement, I get a syntax error message for a missing ) or ]
When I add a ) to the end and try to run it I am prompted with a box to "Enter Parameter Value" for SomeDate.
If I press Enter I get an error that the "expression is typed incorrectly or is too complex to be evaluated..."
I don't know if it will help, but I will paste the SELECT statement below:
SELECT Customers.[First Name], Customers.[Last Name], Customers.[Email Address], UPS_Tracking_Info.OrderNo, UPS_Tracking_Info.Tracking No, UPS_Tracking_Info.complete , UPS_Tracking_Info.delivery _estimate
FROM (Customers INNER JOIN [Order Table] ON Customers.[Customer ID] = [Order Table].[Customer ID]) INNER JOIN UPS_Tracking_Info ON [Order Table].OrderNo = UPS_Tracking_Info.OrderNo
WHERE SomeDate = DateAdd("d", 14, DateSerial(left(delivery_e stimate, 4), mid(delivery_estimate, 5, 2), right(delivery_estimate, 2) ))
Thank you for all of the help.
dresdena1
When I add the Where statement, I get a syntax error message for a missing ) or ]
When I add a ) to the end and try to run it I am prompted with a box to "Enter Parameter Value" for SomeDate.
If I press Enter I get an error that the "expression is typed incorrectly or is too complex to be evaluated..."
I don't know if it will help, but I will paste the SELECT statement below:
SELECT Customers.[First Name], Customers.[Last Name], Customers.[Email Address], UPS_Tracking_Info.OrderNo,
FROM (Customers INNER JOIN [Order Table] ON Customers.[Customer ID] = [Order Table].[Customer ID]) INNER JOIN UPS_Tracking_Info ON [Order Table].OrderNo = UPS_Tracking_Info.OrderNo
WHERE SomeDate = DateAdd("d", 14, DateSerial(left(delivery_e
Thank you for all of the help.
dresdena1
I tested the following and it works:
MyDate: DateAdd("d",14,DateSerial( Left("2014 0909",4),M id("201409 09",5,2),R ight("2014 0909",2)))
Replace "20140909" with your data field.
MyDate: DateAdd("d",14,DateSerial(
Replace "20140909" with your data field.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you to everyone for all of the help.
Gustav Brock I put your query in and it worked perfectly the first time!
Thank you all again for all of the help!
Gustav Brock I put your query in and it worked perfectly the first time!
Thank you all again for all of the help!
You are welcome!
/gustav
/gustav
DateAdd("d", 14, delivery_estimate) to get that date you want.
DateAdd ( interval, number, date )
For more examples and options, see: http://www.techonthenet.com/access/functions/date/dateadd.php