Good Wedding Reception Ordurves

Access: Issue with dates (if> = Now ())

I you propose to address a problem in this post quite boring in connection with the dates. This issue was raised during a discussion on Access Development forum MSDN as follows:

Suppose you have a table " spots" with the fields " Num_Tache ", " TaskName ", " Duree_Tache "and" DateFinPrevue_Tache "and you want to view using a query list of tasks that should be completed as of today and beyond.

Create a data set including tasks with completion dates earlier than the current date, equal to the current date and after the current date and then build your query.

When creating your application, you may be tempted to tighten proceed as follows:

Select Taches.Nom_Tache
Stains From
Where DateFinPrevue_Tache> = Now ();

This corresponds, in query design mode, add in the field column " DateFinPrevue_Tache " on the "Status " expression "> = Now () .

If so, run your query and see what happens. If you look closely you will notice that the tasks with a date is equal to the date of the current day have been excluded and that the task list contains only those that have a date subsequent to the date.

Why this result?

To answer this, remember (or remember) that first:

Access To as Excel, a date is nothing but a serial number composed of two (02 ) parts, the whole which denotes the number of days elapsed since a reference date and the decimal part represents the time of day (hours, minutes, seconds).

Knowing that the day 1.00 (which refers to the 01/01/1900 00:00 and can also be defined at 01/01/1904), the serial number that corresponds to 07 / 02/2011 00:00 is 40581.00 the same day at noon will 40581.50 6:00 p.m. and we 40581.75

Know then that:

When you enter a date like " 07/02/2011" for example and not add the time, the application will only record the integer part of the serial number. So in our case 40581

Finally remember that:

function Now () returns the serial number of the system date at the moment it is executed. IF I run the example 07/02/2010 at 09:00 value is considered to be 40,581.25.

Conclusion:

The serial number returned by tasks with a scheduled end date the current day is still below the serial number returned by the function Now () these tasks will always be excluded from the query result. The solution

• Some might be tempted to enter complete dates like "02/07/2010 12:00:00" to indicate noon. this will solve the problem if you run your application before noon but at noon and a second your solution will be obsolete. Moreover, it is not practical to capture systematically the date this form.

But then how do ?

The first solution that came to mind was to use the " SérieDate (year, month, day) " (or DateSerial (Year; Mounth; Day) ) by combining the functions Now () (or Now () ) Year () , Month () and Day () as follows: SérieDate (Year (Now ()); Month (Now ( )), Day (Now ())) .

Then in the thread, a more adequate solution has been proposed by a participant (Blaise032). This solution is simply to replace the function Now () function by Date () that reference that the integer part of the serial number of the date. It is true that before proposing the first solution I looked in Access function TODAY () (which exists in Excel) to find it. Only by reading the proposal Blaise032 I understand that the equivalent of the Excel TODAY () function is Access DATE () .

syntax SQL query will be:

Select Taches.Nom_Tache
From Spots
Where ((Taches.DateFinPrevue_Tache)> = Date ());

This corresponds, in query design mode, to be replaced in the field column " DateFinPrevue_Tache " on line "Status " expression "> = Now () " with "> = Date () .

Feel free to leave comments.

Technorati Tags: Access , problem, request , date, now, now