Date() and Now() (1 Viewer)

[johnb23]

On a data entry form, I changed the Property/Default value of a date field from Date() to Now() in order to capture the Time protion of an entry-date.

However, the qry to select entry-date using...
Between [enter From date of entry] And [enter To date of entry] stopped working (returned no records). ???

I have tried using different formatting options without success.

This is probably very elementary to most experienced Access db'ers...wish I was one.

 

[FoFa]

Most likely depends on your input parametes for the query.
because if you wanted one day of data, before you may have used
Between 01/01/2006 AND 01/01/2006. How ever that will not work with a time portion as 01/01/2006 really means 01/01/2006 00:00:00, and a record dated 01/01/2006 08:30:00 falls outside that range.

 

[x0reset]

So in other words, if your users want to see data for a single day, they must enter the times as well as the dates. If they want to view multiple days, they do NOT have to enter the times as well as the dates.

 

[FoFa]

Not really, typically what you can do is take the end date and add one day to it, or add 23 hourse, 59 minutes and 59 seconds to it for a correct query.
Between [enter From date of entry] And DATEADD("d",1,[enter To date of entry])
This will usually work for you, but in that very odd occurance where this something on exactly midnight of that end date, it would pick it up. Never has happened to me however.

 

[johnb23]

FoFa, x0reset, you are both right on target...maybe a little more explanation will help.
Receipts to our inventory system are batched and entered twice daily (AM / PM)...formerly only once a day. A report is then generated to allow for editing and balancing. When once a day entry occurred, the between dates were entered i.e. 1/20/2006 and 1/20/2006, just as you stated...all was well. But, whenever we went to twice a day batch entry, the PM report included the AM entries (which were already edited and balanced). Not a real big problem...just a nuisance.
My logic seemed simple enough: just need the ability to select transaction by date and either AM or PM. (Easier said than done).
Anyway, I hope the additional information helps to clarify my problem...and thanks for taking time to address this issue.

 

[Pat Hartman]

Morning:
Where DateValue(YourDate) = Date() AND TimeValue(YourDate) < #12:00 PM#;
Afternoon:
Where DateValue(YourDate) = Date() AND TimeValue(YourDate) >= #12:00 PM#;

 

[johnb23]

My desire for simple soultions always seem to influence
the problem definition phase as it has in this situation. The farther I dig into this problem the more complex it becomes. The choices now are:
1. Edit/Balance today's receipts (AM)
2. Edit/Balance today's receipts (PM)
3. Select receipts based on beginning and ending dates regardless of time

...and what if, while keying the am receipts, the time goes past 12 pm. I think I need to go back to the drawing board and really think this through...who knows, there probably is a simple solution.

Anyway, thanks for taking the time to respond to the first posting.

 

[Pat Hartman]

You do need to define your business rules but the key to working with a date field that contains a time component is to use the DateValue() function to extract ONLY the date when you want to search for a specific date or date range.