asked on

MS Access date formatting

Hi Gang... I have a query that retrieves a date fiield stored as text :-( and then formats it as such

>=Format([Forms]![frm_Select Rx Date Range]![ctlRxStartDate],"yyyymmdd") And <=Format([Forms]![frm_Select Rx Date Range]![ctlRxEndDate],"yyyymmdd")

How can I make this date range be +1 day?

sshah254

Use the DateAdd function

http://www.techonthenet.com/access/functions/date/dateadd.php

Ss

Gustav Brock

Try this:

>=Format([Forms]![frm_Select Rx Date Range]![ctlRxStartDate],"yyyymmdd") 
And 
<=Format(DateAdd("d",1,[Forms]![frm_Select Rx Date Range]![ctlRxEndDate]),"yyyymmdd")

Open in new window

/gustav

hwassinger

ASKER

cactus, this is close but I need BOTH dates to be +1. This is Necassary as the user is entering the date in a form and I need to carry it from 1 query to another as the query must cross a date line due to work hours

hwassinger

ASKER

>=Format(DateAdd("d",1,[Forms]![frm_Select Rx Date Range]![ctlRxStartDate],"yyyymmdd") And <=Format(DateAdd("d",1,[Forms]![frm_Select Rx Date Range]![ctlRxEndDate]))

Seems to fail and resets itself back to

>=Format([Forms]![frm_Select Rx Date Range]![ctlRxStartDate],"yyyymmdd") And <=Format(DateAdd("d",1,[Forms]![frm_Select Rx Date Range]![ctlRxEndDate]))

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hwassinger

ASKER

Thanks!

Gustav Brock

You are welcome!

/gustav