At udlede alder i år fra et cpr nr i formatet tekst

select cpr,cint(right(left(cpr,len(cpr) - 5),2)) as birthyear  from cpr where cint(right(left(cpr,len(cpr) - 5),2)) < 50

tabelnavn er cpr og feltnavn indeholdende cprnummer er ligeledes cpr

//akj

SELECT tblCprNumber.*
FROM tblCprNumber
WHERE (((DateAdd("yyyy",50,CDate(Mid([CPR],1,2) & "/" & Mid([CPR],3,2) & "/" & Mid([CPR],5,2))))<Date()+1));

I can see that the solution I gave doesnt always works!

Jeg har kørt med denne i mange år

Select person where
Int((Now()-DateValue(Mid([LØNFIL].[Cprnr],1,2) & "-" & Mid([LØNFIL].[Cprnr],3,2) & "-19" & Mid([LØNFIL].[Cprnr],5,2)))/365.25)))>=50

Henrik

SELECT tblCprNumber.*, tblCprNumber.CPR, CDate(IIf(CDate(Mid([CPR],1,2) & "/" & Mid([CPR],3,2) & "/" & Mid([CPR],5,2))<=Date(),CDate(Mid([CPR],1,2) & "/" & Mid([CPR],3,2) & "/" & Mid([CPR],5,2)),DateAdd("yyyy",-100,CDate(Mid([CPR],1,2) & "/" & Mid([CPR],3,2) & "/" & Mid([CPR],5,2))))) AS Dt
FROM tblCprNumber
WHERE (((CDate(IIf(CDate(Mid([CPR],1,2) & "/" & Mid([CPR],3,2) & "/" & Mid([CPR],5,2))<=Date(),CDate(Mid([CPR],1,2) & "/" & Mid([CPR],3,2) & "/" & Mid([CPR],5,2)),DateAdd("yyyy",-100,CDate(Mid([CPR],1,2) & "/" & Mid([CPR],3,2) & "/" & Mid([CPR],5,2))))))<=DateAdd("yyyy",-50,Date())));

bit of a mouth full but it seems to work

Hej terry
Din kode fungerer perfekt - jeg er dybt imponeret - jeg ville ALDRIG kunne have skrevet en queri af denh type så du fortjener absolut pointene.

Jeg siger også tak til dk aki og hnteknik MEN jeg har ikke kunnet få jeres forslag til at virke!

Hej igen terry
Jeg opretter lige et nyt spørgsmål idet jeg har brug for en forbedring. Jeg håber du kan hjælpe mig med den også.
(I andre må naturligvis også gerne prøve)

Hm - nu har det ellers virket de sidste 7 år og virker stadig

hnteknik>Your solution does work to some extent, but not as it is.

This works

Select * FROM person where
Int((Now()-DateValue(Mid([Cprnr],1,2) & "-" & Mid([Cprnr],3,2) & "-19" & Mid([Cprnr],5,2)))/365.25)))>=50

OR
Select person FROM ([LØNFIL] where
Int((Now()-DateValue(Mid([LØNFIL].[Cprnr],1,2) & "-" & Mid([LØNFIL].[Cprnr],3,2) & "-19" & Mid([LØNFIL].[Cprnr],5,2)))/365.25)))>=50

but not as is!

I must also say that your solution (when modified) is a little more elegant than mine. I'm a little unsure that dividing by 365.25 will always give the correct result, but then I am sure you have tested it.

>>terry
This is not a nursery. One is supposed to modify the code to some extend to ones personal needs. I don't have the time you obviously spend on servicing other users. My believe is, that you in several cases actually do them a 'bjørnetjenste', because your way they probably never learn.

The above has been tested on up to 4000 CPRNRs and you can check that the year actually is swicthing on their 'fødselsdag' on a daily basis.

Good Night - I need my beauty sleep - because tomorrow is it my birthday - so says my CPRNR check.

You dont need to get on my back hnteknik :o)

I can only answer for myslef, I have no idea if wmtoole is able to modify your code so that it works, vel?

I dont care on how many CPR numbers you have tested your code on, as it is here it deosnt work! and if you hadnt moaned so much I would have given you the points as it is now you can go to your bed and sulk without them :o)

Good night, and a happy birthday tomorrow :o)

hey Terry - I'm not on your back ;-)
I just admire you for the time you spend over and over again on peoples posed problems, which often can be found searching this site in split seconds.

As you probably already know, I don't go for your or others points, so don't hit me with your evil spell - sourcer - I cought a bad cold yesterday evening so back to bed :-~) Thanks anyway

sorry to hear that, hope you get well soon.