[AccessD] substract dates

Sorry, Pedro, I don't quite understand.  What do you mean the query doesn't 
work?  Can you give a specific example of where it gives the incorrect 
result?  You specified it should show 1 if the difference between the dates 
is less that 365, and a 0 if it is more, and I think this is what the 
expression I gave you would do.

Let's clear this up first, before we tackle the next question of negative 
values - which I presume you mean would be if Uitvoerdatum is later than 
Afsluitdatum?

Regards
Steve

-----Original Message----- 
From: pedro at plex.nl
Sent: Thursday, September 30, 2010 1:12 PM
To: steve at datamanagementsolutions.biz
Subject: Re: [AccessD] substract dates

Hello Steve and others,

i was just a trail, so the in the final database, the two tables will be 
joined.

But the query you gave, doesn't work.

When adjusting the query from <365   to   >365, the result has to be the 
other part of the records. Now only the value field changes from 1 into 0.

Who can help me further with this.

I would like to make one addition.
In the days column, negative values may not participate in the granted 
values 1 and 0.

Thanks

Pedro


In antwoord op:

> From: "Steve Schapel" <steve at datamanagementsolutions.biz>
> To: "Access Developers discussion and problem solving"
> Date: Thu, 30 Sep 2010 22:28:31 +1300
> Subject: Re: [AccessD] substract dates
>
>
>
> Pedro,
>
> Leaving aside for the moment why these data are in two separate tables, 
> and
> of course I don't know the full story here, but at some stage you might
> consider joining them together.
>
> I think the query you need here will look like this:
>
> SELECT tbl1.patientnr, [Afsluitdatum]-[Uitvoerdatum] As Days,
> Abs(([Afsluitdatum]-[Uitvoerdatum])<365) As TheValue
> FROM tbl1 INNER JOIN tbl2 ON tbl1.patientnr = tbl2.patientnr
>
> Regards
> Steve
>
> -----Original Message----- 
> From: pedro at plex.nl
> Sent: Thursday, September 30, 2010 10:39 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] substract dates
>
> Dear List,
>
> i have two tables
>
> tbl1
> patientnr        Afsluitdatum
> 01234567    01-01-2010
> 09876543    15-01-2010
>
> tbl2
> patientnr      Uitvoerdatum
> 01234567    01-02-2009
> 09876543    01-01-2009
>
> I would like as result a new table that subtracts UitvoerDatum from
> Afsluitdatum, and when the result is within 365 days, i need a "1" and 
> when
> the result is more then 365 days, i need a "0".
>
> So the result is:
>
> patientnr         days      value
> 01234567       334         1
> 09876543       379         0
>
> Normally i do this in Excell, but i would like to do everything in access
> and not part in access and part in excell.
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>