Comparing dates

[Guest]

Dear access supporters,

I am new at using acces. I have created a table with a number of fields. Two
of these fields are dates (datein and dateout). Now I want acces
automatically calculate the number of days between these two fields and add
that number in a field (date difference).

I tried touse the expression =datediff("d"";dateout;datein), but that did
not work. I also used ="dateout"-"datein", but that does not seem to work
either !

Can someone help me please?

Thank you very much !

 

[Rick Brandt]

Dear access supporters,

I am new at using acces. I have created a table with a number of fields. Two
of these fields are dates (datein and dateout). Now I want acces
automatically calculate the number of days between these two fields and add
that number in a field (date difference).

I tried touse the expression =datediff("d"";dateout;datein), but that did
not work. I also used ="dateout"-"datein", but that does not seem to work
either !

Can someone help me please?

Thank you very much !

The DateDiff idea is a good one, just not in a table. Calculations like that do
not belong in tables. Use that expression on forms, reports, and in queries and
you don't need the value to be in the table.

 

[Guest]

The correct syntax is

DateDiff("d", DateIn, DateOut)

There are some other factors.

First, you should not be storing the difference in your table. One of the
basic rules of database design is not to store calculated values. It is a
waste of time and disk space, and has a probablilty of becoming inaccurate.
It is always better to perform the calculation only to show it to humans in
forms an reports.

Now, the correct syntax really depends on where you are using it. If you
are using it on a form, you need to address the names of the controls, not
the table field names. So, if you have text boxes on your form named
txtDateIn and txtDateOut and you are going to display it in a text box named
txtDays then the syntax would be:

Me.txtDays = DateDiff("d", Me.txtDateIn, Me.txtDateOut)

If you can tell me exactly how you intend to use this, I can help get it
right.

 

[Guest]

Hello Klatuu,

Thank you very much for your support.

I have a table called 'data entry'.

In that table I have information about problems that occur during my
project. I want to know per problem, how long it takes to close the problem
(DateOut-Datein).

On top of that I want to know (in graphs) what the percentage is of problems
that are open for a certain period (DateOut-DateIn), so that I can report on
that.

Can you help me with this?

Thanks very much !

 

[Guest]

Your graph will be based on a query. To get the difference in days, use a
calculated field in the query to return the number of days:

TotalDays: DateDiff("d", DateIn, DateOut)

That will give you a field named TotalDays that will be the number of days
between the two dates.

 

[Guest]

Hello Klatuu,

Thanks again for your help !

I built a query based on two fields out of the table 'data entry' (DateIn
and DateOut). The third field is an expression 'Expr1: [TotalDays]' based on
your expression below.

When I save and press execute it asks me for total days, but I want a list
of all entries in DateIn and DateOut with their corresponding difference.

Please help !

Thanks

 

[Guest]

Dear Rick,

Thanks, I see your point, but no matter where I try (query or forms) it
won't work !

PLease help me ?

Thanks kindly !

Jay

 

[Guest]

You put the code below in the Field row of the query builder, not 'Expr1:
[TotalDays]'

TotalDays: DateDiff("d", DateIn, DateOut)

Hello Klatuu,

Thanks again for your help !

I built a query based on two fields out of the table 'data entry' (DateIn
and DateOut). The third field is an expression 'Expr1: [TotalDays]' based on
your expression below.

When I save and press execute it asks me for total days, but I want a list
of all entries in DateIn and DateOut with their corresponding difference.

Please help !

Thanks

Your graph will be based on a query. To get the difference in days, use a
calculated field in the query to return the number of days:

TotalDays: DateDiff("d", DateIn, DateOut)

That will give you a field named TotalDays that will be the number of days
between the two dates.

 

[Rick Brandt]

Dear Rick,

Thanks, I see your point, but no matter where I try (query or forms)
it won't work !

PLease help me ?

If your fields are named dateout and datein then

datediff("d", datein, dateout)

....should return the number of days between them.

In a query you would create a new field like...

DaysDiff: DateDiff("d", datein, dateout)

 

[Guest]

Thanks Rick,

I found out that I was missing the [ and ] brackets before and after the
table names: DaysDiff: DateDiff("d", [datein], [dateout]).

So your solution was absolutely perfect, thanks !!

The same goes for Klatuu, thanks as well !

Kind regards,

Jay

 

[Rick Brandt]

Thanks Rick,

I found out that I was missing the [ and ] brackets before and after the
table names: DaysDiff: DateDiff("d", [datein], [dateout]).

So your solution was absolutely perfect, thanks !!

You should only need those brackets if your field names contain spaces or some
other "illegal" character like #. If they are actually "datein" and "dateout"
then the brackets should not be required.

 

[Guest]

Hello RIck,

Ok great, I will keep that in mind, thanks !

Ciao !

Thanks Rick,

I found out that I was missing the [ and ] brackets before and after the
table names: DaysDiff: DateDiff("d", [datein], [dateout]).

So your solution was absolutely perfect, thanks !!

You should only need those brackets if your field names contain spaces or some
other "illegal" character like #. If they are actually "datein" and "dateout"
then the brackets should not be required.