Access VBA: OGM-code op factuur

Status
Niet open voor verdere reacties.
F

fde

Gebruiker
Lid geworden
31 aug 2017
Berichten
110
Ik probeer op onze facturen de OGM-code (gestructureerde mededeling +++ 123/1234/12345 +++) te plaatsen.
Manueel berekenen is geen probleem doch via vba gaat dit veel sneller denk ik.
Voor aan de eerste 10 digits te komen gebruik ik de factuurnummer (bv 2017199) + klantnummer (bv 127).
Kan iemand me hiermee op weg me helpen aub?
 
Je voorbeeldje is ongeschikt om iets mee te doen. Ik snap ook de bedoeling niet: is het de bedoeling dat je drie plusjes maakt, dan een getal, slash, getal, slash, getal en dan weer 3 plusjes? En waar komen die getallen dan vandaan?
 
Een gestructureerde mededeling bestaat uit 12 cijfers waarvan de laatste 2 cijfers een berekend controle getal is.
Voor de opbouw van een gestructureerde mededeling heb je 10 cijfers nodig. Deze 10 cijfers deel je door 97. De uitkomst wordt naar beneden afgerond.
Dan: de eerste 10 cijfers minus (het afgeronde getal x 97) = het controle getal.

bv:
2.017.108.001 / 97 = 20.794.927,84.53 --> als de rest van deze deling 0 zou zijn wordt altijd het controle getal 97 genomen.
afronden naar beneden: = 20.794.927
controle getal: 2.017.108.001 - (20.794.927 x 97) = 82

Dus de cijfers voor de gestructureerde mededeling : 201710800182
De vormgeving van een gestructureerde mededeling ziet er zo uit: +++xxx/xxxx/xxxxx+++

Opbouw cijfers (10 benodigde digits) : 2017108 = Factuurnummer [FACTUUR_NUMMER] en 001 = Klantnummer [FACTUUR_DEBITEUR_NUMMER] komende uit de tabel tbl_factuur waarvan [ID_FACTUUR] de primaire sleutel is.
 
Het was een beetje puzzelen, maar volgens mij kun je het gewoon in een query oplossen en heb je geen functies nodig.
Code: 
OGM: "+++" & IIf(Val([Factuur_Nummer] & Right("000" & [Factuur_Deb_Nr];3))=0;Left([Factuur_Nummer] & Right("000" & [Factuur_Deb_Nr];3);3) & "/" 
     & Mid([Factuur_Nummer] & Right("000" & [Factuur_Deb_Nr];3);4;4) & "/" & Right([Factuur_Nummer] & Right("000" & [Factuur_Deb_Nr];3);2) & 97;
     Left([Factuur_Nummer] & Right("000" & [Factuur_Deb_Nr];3);3) & "/" & Mid([Factuur_Nummer] & Right("000" & [Factuur_Deb_Nr];3);4;4) & "/" & 
     Right([Factuur_Nummer] & Right("000" & [Factuur_Deb_Nr];3);2) & Val([Factuur_Nummer] & Right("000" & [Factuur_Deb_Nr];3))
     -(Int(Val([Factuur_Nummer] & Right("000" & [Factuur_Deb_Nr];3))/97)*97)) & "+++"
 
Andere aanpak:

Code: 
"+++" & Format(Format(tblOGM.Factuur_Nummer,"0000000") & Format(tblOGM.Factuur_Deb_Nr,"000") & Abs(Format(tblOGM.Factuur_Nummer,"0000000") & Format(tblOGM.Factuur_Deb_Nr,"000"))-(97*Int(Abs(Format(tblOGM.Factuur_Nummer,"0000000") & Format(tblOGM.Factuur_Deb_Nr,"000"))/97)),"000/0000/00000") & "+++" AS OGM

Als een factuurnummer minder dan 7 posities bevat, wordt het aangevuld met voorloopnullen.
Als het klantnummer minder dan 3 posities bevat, wordt het aangevuld met voorloopnullen.

Als een factuurnummer meer dan 7 posities kan hebben en/of een klantnummer meer dan 3 posities kan hebben, dan kun je bovenstaand SQL uitbreiden met een combinatie van de Mid en de Len functie (eea afhankelijk van hoe je 7 resp. 3 posities wilt strippen uit factuurnummer resp. debiteurnummer).

Persoonlijk zou ik hier een functie voor schrijven in VBA.
Een voordeel daarvan is dat je commentaar/toelichting/versiebeheer in VBA kan zetten.

PS kan zijn dat je de komma's moet vervangen door puntkomma's.

Tardis
 
Ik heb beide oplossingen uitgeprobeerd. OGM = OctaFish - OGM2 = Tardis.
Zie bijgevoegde print-shot: omg.PNG

- In de meeste gevallen is het controle cijfer in beide oplossingen correct.
- Bij de oplossing van OctaFish ontbreekt de eerste digit van het klantnummer
- In mijn geval zou de OGM steeds moeten beginnen met +++201/ ....... :
Bij de oplossing van Tardis zijn er enkelen die beginnen met +++020/........ en net deze zijn ook deOMG-codes die bij OctaFish niet juist worden weergegeven.

Enkele narekeningen:
OMG: 201/7060/010 - OMG2: 020/1706/00010 --> juiste code: 201/7060/00197
OMG: 201/7070/019 - OMG2: 020/1707/00019 --> juiste code: 201/7070/00109
OMG: 201/7073/012 - OMG2: 020/1707/30012 --> juiste code: 201/7073/00102
 
Laatst bewerkt:
Heb de code aangepast op 2 punten:

- voorloopnul toegevoegd aan controlegetal
- als de rest van de deling 0 (nul) is, dan wordt het controlegetal 97 (was ik vergeten)

Aangepaste code (kan zijn dat je de komma's door puntkomma's moet vervangen):

Code: 
"+++" 
& Format(Format(tblOGM.Factuur_Nummer,"0000000") & Format(tblOGM.Factuur_Deb_Nr,"000") 
& IIF(Format(Abs(Format(tblOGM.Factuur_Nummer,"0000000") & Format(tblOGM.Factuur_Deb_Nr,"000"))-(97*Int(Abs(Format(tblOGM.Factuur_Nummer,"0000000") & Format(tblOGM.Factuur_Deb_Nr,"000"))/97)),"00") = 0,97,Format(Abs(Format(tblOGM.Factuur_Nummer,"0000000") & Format(tblOGM.Factuur_Deb_Nr,"000"))-(97*Int(Abs(Format(tblOGM.Factuur_Nummer,"0000000") & Format(tblOGM.Factuur_Deb_Nr,"000"))/97)),"00")) ,"000\/0000\/00000") 
& "+++" AS OGM

Tardis
 
Een oplossing via VBA

Open een module in VBA.
Plak daarin deze code:

Code: 
Public Function OGM(a, b) 'a en b zijn parameters, vervang deze in je query door kolomnamen

Dim x As Double
Dim y As Double
Dim z As String

'samenvoegen van factuurnummer en debiteurnummer, beiden worden zonodig aangevuld met voorloopnullen
x = Format(a, "0000000") & Format(b, "000")

'bepalen controlegetal
y = Abs(x) - 97 * Int(Abs(x / 97))

'vervang een gevonden controlegetal 0 door 97
If y = 0 Then
y = 97
End If

'voeg eerder samengevoegd factnr en debnr samen met controlegetal
'10 posities resp. 2 posities incl. evt. voorloopnullen
z = Format(x, "0000000000") & Format(y, "00")

'bepaal OGM incl. bijbehorende opmaak
OGM = "+++" & Format(z, "000/0000/00000") & "+++"

Functie voeg je vervolgens toe aan een query.
Vervang daarbij a en b door kolomnamen.
In jouw geval wordt dat dan OMG(Factuur_Nummer, Factuur_Deb_Nr)

Tardis
 
fde zei:
- In de meeste gevallen is het controle cijfer in beide oplossingen correct.
- Bij de oplossing van OctaFish ontbreekt de eerste digit van het klantnummer
Klik om te vergroten...
Zonder correct voorbeeldje van jou kant is het lastig om de juiste output te krijgen; dan moeten we dus eerste zelf records gaan inkloppen. Ik ben bereid een hoop te doen voor vragenstellers, maar daar trek ik de streep, ik ben gen tikgeit :). Ik zou zelf ook een functie gebruiken voor dit soort codes. Ik zal de mijne om verwarring te voorkomen in de la laten liggen, want de code van tardis is niet veel anders en werkt neem ik aan verder prima.
 
@Tardis: ik vermoed dat deze aanpassing de goede is. omg2.PNG
Ik heb de vorige OMG-codes die begonnen met +++020/.... allen nagerekend en daarvan is het berekend controle getal en de format juist.
Ook de vba code werkt perfect.

@OctaFish: ik zal in het vervolg bij zulke vragen de benodigde tabel & query in een separate mdb zetten. Het is inderdaad niet de bedoeling dat je oneindig nummers inklopt.

Althans dank aan beiden voor de geboden oplossingen. Ik zou alleszins niet weten waar te beginnen.
 
Laatst bewerkt:
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan