CIO Tech Eksperten IT-JOB IT-Kurser Events Podcast Søg

Log ind eller opret profil

Du kan også logge ind via nedenstående tjenester

idani Praktikant

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

Synes godt om

terry Ekspert

15. september 2009 - 12:18 #1

If th ecpr number contains - then it isnt numeric. Does it, or is it a number? (1302741234)

Synes godt om

idani Praktikant

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

Synes godt om

idani Praktikant

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

Synes godt om

terry Ekspert

15. september 2009 - 12:30 #4

What data type does the field have?

Synes godt om

idani Praktikant

15. september 2009 - 12:33 #5

it is stated as Text - because other wise it will not show 0

/Ida

Synes godt om

idani Praktikant

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)

Synes godt om

terry Ekspert

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

Synes godt om

terry Ekspert

15. september 2009 - 12:46 #8

So in your SQL select you should use something like this

SELECT convertCPR([FieldWithCPRNumberGoesHere]) AS CPR FROM ....

Synes godt om

idani Praktikant

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

Synes godt om

terry Ekspert

15. september 2009 - 12:48 #10

You need to make a module

Synes godt om

idani Praktikant

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

Synes godt om

terry Ekspert

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.

Synes godt om

terry Ekspert

15. september 2009 - 14:03 #13

Hows it going Ida, do you need more help?

Synes godt om

idani Praktikant

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

Synes godt om

terry Ekspert

15. september 2009 - 14:41 #15

No quite sure I understand your SQL.

What is the names of the table and field?

Synes godt om

idani Praktikant

15. september 2009 - 14:47 #16

Tabel name: Efter 2006
Field name: Cpr

Synes godt om

terry Ekspert

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]

Synes godt om

idani Praktikant

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

Synes godt om

terry Ekspert

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.

Synes godt om

terry Ekspert

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

Synes godt om

idani Praktikant

15. september 2009 - 15:05 #21

All right now it finds all the cpr numbers lower than 31

How do i then convert them?

Synes godt om

terry Ekspert

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])

Synes godt om

idani Praktikant

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

Synes godt om

idani Praktikant

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

Synes godt om

terry Ekspert

15. september 2009 - 16:15 #25

Great to hear you got it working.

Synes godt om

terry Ekspert

15. september 2009 - 21:26 #26

thanks

Synes godt om

Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Følg dette spørgsmål

Opret Preview

Access kurser

Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Se alle Access kurser

Flere spørgsmål fra Access kategorien

Titel	Indlæg	Oprettet	Seneste aktivitet
Rapport koder placeret i moduler Af per2edb i Access	4	I går 15:15	I går 18:04
Print specifik subform Af per2edb i Access	8	09/09/202414:12	10/09/202411:54
query calculated field ? Af fimo i Access	5	03/09/202409:23	03/09/202411:08
Fjernadgang til Access app Af per2edb i Access	5	29/08/202408:21	29/08/202415:07
Filter der lister tomme felter Af per2edb i Access	3	26/08/202421:52	27/08/202415:20

Se alle spørgsmål i kategorien Opret spørgsmål

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester

Alle kategorier på Eksperten

Seneste artiklerRSS