15. september 2009 - 12:05
Der er
25 kommentarer og 1 løsning
Opdaterings formel til rettelse af cpr nummere
Hejsa Jeg sidder på et lønkontor og benytter i den forbindelse en hjemmebrygget database - som samler alle posteringer fra 2 forskellige systemer. I det ene system står nogle af Cpr numrene som +4 numre. Dvs. 130274-xxxx bliver til 530274-xxxx Jeg har nu behov for at kunne fjeren de 4 tal igen - altså sige noget i retning af Alle cpr numre som er større end 4000000000 skal der trækkes 4000000000 fra. Hvordan skriver jeg lige den. Der ud over så ville det være rigtigt rart hvis jeg kan få en mini guide på hvordan jeg opretter forspørgslen - sådan at jeg kan være sikker på at de tal som jeg trækker 4000000000 fra også er de rigtige ;o) Hilsen Ida
Annonceindlæg fra Cognizant
15. september 2009 - 12:18
#1
If th ecpr number contains - then it isnt numeric. Does it, or is it a number? (1302741234)
15. september 2009 - 12:24
#2
Hi Terry The number only contains numbers ;o) I have managed to make a select query that selects all cpr numbers above 4000000000 But now I can't get it to deduct the mentiont amount ;o) Best regards ida
15. september 2009 - 12:26
#3
forgot somthing the number is written as 1302741234 as you states and i then need the number 5302741234 become 1302741234 It is allways the same amount that I need to deduct /Ida
15. september 2009 - 12:30
#4
What data type does the field have?
15. september 2009 - 12:33
#5
it is stated as Text - because other wise it will not show 0 /Ida
15. september 2009 - 12:36
#6
Bye the way If it is - I can just run the same query a number of times And the locate all 4 - 5 - 6 and 7 in the frot of the number and change it to 0 - 1 - 2 ore 3 after what is needed /Ida But I still need help to do that ;o)
15. september 2009 - 12:40
#7
Actually what I think you need to do is check if the first two digits are > 31 Try this Public Function convertCPR(CPR As String) As String If CInt(Left(CPR, 2)) > 31 Then convertCPR = (CInt(Left(CPR, 2)) - 40) & Right(CPR, Len(CPR) - 2) End If End Function
15. september 2009 - 12:46
#8
So in your SQL select you should use something like this SELECT convertCPR([FieldWithCPRNumberGoesHere]) AS CPR FROM ....
15. september 2009 - 12:47
#9
Hi Terry where do I fill in the function It looks very right - but I'm a bit confused about where to put it. Please state it is eg.Tools / Macro Best regards ida
15. september 2009 - 12:48
#10
You need to make a module
15. september 2009 - 12:54
#11
All right now I have made a module then what to do ? Sorry to be a pain in the - but I'm self taught - and still needs a lot of expirence /Ida
15. september 2009 - 12:57
#12
I'm also self taught, and still need a lot of experience :o) But we'll get there I'm sure. If you have opend a new module then copy the code I gave into that. Then save the module. Then alter the SQL so that the field containing the CPR number includes the call to the funtion you just made.
15. september 2009 - 14:03
#13
Hows it going Ida, do you need more help?
15. september 2009 - 14:11
#14
Hi Terry Well I had to go for lunch ;o) But when I'm in my query - I select the sql view. Then I copy the SELECT convertCPR([cpr]) AS CPR From efter 2006; in there and then get the message Circular reference caused by alias 'CPR' in query definition's Select list. What to do then?? /Ida
15. september 2009 - 14:41
#15
No quite sure I understand your SQL. What is the names of the table and field?
15. september 2009 - 14:47
#16
Tabel name: Efter 2006 Field name: Cpr
15. september 2009 - 14:49
#17
Dont understand why you get a Circular reference but try this. SELECT convertCPR([cpr]) AS A From [efter 2006]
15. september 2009 - 14:52
#18
Yihaaa that one worked ;o) - partly Now I got it in my query - but it only shows a collum of blank fields So how do I get the modul par in now? /Ida
15. september 2009 - 14:58
#19
? Did you copy the code I gave Skrevet tir. d. 15. september 2009 kl. 12:40:33| #7 into a module? The SQl uses (calls) this code, If you look at the SQl you see convertCPR. What happends here is the SQL calls the function which you should have in a module.
15. september 2009 - 14:59
#20
Looing at the cod ether is a little error though :o( Public Function convertCPR(CPR As String) As String If CInt(Left(CPR, 2)) > 31 Then convertCPR = (CInt(Left(CPR, 2)) - 40) & Right(CPR, Len(CPR) - 2) ELSE convertCPR = CPR End If End Function
15. september 2009 - 15:05
#21
All right now it finds all the cpr numbers lower than 31 How do i then convert them?
15. september 2009 - 15:09
#22
"it finds all the cpr numbers lower than 31" Doesnt it show ALL cpr numbers correctly? If it doesn then th efuncion works as it should. To alter the CPR in the table (make a backup first) UPDATE [efter 2006] SET [CPR] = convertCPR([cpr])
15. september 2009 - 15:15
#23
the cpr numbers are shown correctly So I will try the update formel Will revert whith sucess ore not ;o) /Ida
15. september 2009 - 15:43
#24
Hi Terry it worked Tks a lot for your help - now I just have to do it for the rest of my tables ;o) I only convert on at a time - since it is taking a lot of memory ;o) Please drop an ansver /Ida
15. september 2009 - 16:15
#25
Great to hear you got it working.
15. september 2009 - 21:26
#26
thanks
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser