Date in continuous form

[pwalt08]

Hello,

I have a table, and form that are going to be used as a call log. The form will be continuous for a month and then we will run a query to delete old calls.

The table has a hidden field for date. I would like this field auto populated which I am able to do. The problem I am running into is 3rd shift 11p -7a. I would the like the field to auto populate with the previous date until 7am then start to use the new date.

This is my first database and I am learning as I go. Any help is greatly appreciated!!

 

[guerillaunit]

Try this champ

Work Date: IIf(Time() Between #12:00:00 AM# And #7:00:00 AM#,Date()-1,Date())

 

[pwalt08]

Thank you! I will give it a shot tonight. Where should I put that?

 

[guerillaunit]

This would be your hidden field. Replace "Work Date" to whatever the name of the field is

 

[pwalt08]

I put that in the default value, works perfectly! thank you so much!

 

[pwalt08]

So that did exactly what I wanted it to. But now I am running into the issue of sorting. I need to be able to run a report of the calls and sort the calls properly.

A call that is logged at 4am on 2/7/12 needs to be after a call that is logged at 11pm on 2/6/12. I thought of a few possible solutions but I am unsure how to go about them.

Solution 1- I could somehow set up a shifts, so that given the system time access knows that 6am is 3rd shift from the previous day and calls are sorted appropriately.

Solution 2- Would it be possible to tell access what time to use as "midnight" ?

 

[guerillaunit]

Add a timestamp field to your table. Start a new column in the query you are using to append data to your table. Then use the "time()" function. It will list the date and time of when a record is added to the database.

 

[pwalt08]

Add a timestamp field to your table. Start a new column in the query you are using to append data to your table. Then use the "time()" function. It will list the date and time of when a record is added to the database.

That was my original plan, but if I want to go back and query all calls from 2/4/12 I want it to pull all the calls from 7am on 2/4/12 through 7am on 2/5/12

 

[guerillaunit]

You can do a double sort. Sort first by the hidden column and then sort by the timestamp

 

[pwalt08]

I really appreciate your help, but I am a little lost now. Dealing with dates/times was so much easier in excel. haha

There is already a "timerequested" field that is set to default value of time(), I added a hidden column for "todays date". In my query now, I have it set to ask the user [What date?]

I only planned on using the query to sort and filter the table based on the date provided but the user.