using the value of a string to determine which string to use for a value

[omega015]

Hi All,

not 100% sure how to achieve this. I have a table setup with some names, for each name I am saying which field to use for a value (but only if the original value is "") but I am not sure how to get vba to use the value of the string instead of the recordset value.

e.g.

table
Partner ID Backup Field
5023949 "Mid(Replace(GTIN,"" "",""""),2,10)"
501354 GTIN

Set myPartner = myDb.OpenRecordset("SELECT [Partner ID], [Backup Product Field] FROM Partners where ([Partner ID] = '" & customer & "')")

If myPartner.EOF Then
GoTo errpartner01
Else
BackupProdField = myPartner![Backup Product Field]
End If

so the above goes through and checks to see if the partner exists and if so sets the backupprodfield to use

next part

If Trim(asilinMiscellaneousData1) = "" Then
Product = Left(BackupProdField & Space(22), 22)

'this is where I need to use the value of the field from BackupProdField

Else
Product = Left(asilinMiscellaneousData1 & Space(22), 22)
End If


this then checks to see if the original field is empty and if so I should use the backup field which has a value further up in the code. but this is the part I cannot get my head around as it would make Product = to say GTIN instead of the value for GTIN mentioned further up in the code.

hope this make sense.

Michael.

[jonh]

If your query only returns a single record you could add the master field to the name replacement table (removing the need to hard code field names) and generate sql something like


SQL = "select MasterField, BackupField from tblReplacement where id=" & id
Set rs = DBEngine(0)(0).OpenRecordset(SQL)

SQL = "SELECT IIf([" & rs(0) & "]='',[" & rs(1) & "],[" & rs(0) & "]) AS _" & rs(0) & " FROM tblData where id=" & id
Set rs = DBEngine(0)(0).OpenRecordset(SQL)

If the query returns multiple records and you need to do more than one replacement, join the tables so all fields are returned and get the value using


myfield = "somefield"
myval = rs(myfield).value

[omega015]

thank you for your reply. looking at your solution I think this relies on the data being stored in a table but my data is being stored as a string by reading through some text files. theres a whole bunch of vba code to read lines of text and extract information needed. I have done a very basic function to show what I am trying to do.

Function myfunction()

customer = "cust1"
original_choice = ""

backup_option1 = "ABC"
backup_option2 = "123"
backup_option3 = "XYZ"

If original_choice = "" Then
my_backup_option = DLookup("[Backup Option]", "mytable", "[customer] = '" & customer & "'")

MyResult =
'//here i need to get the value of one of the backup options above based on
which option the dlookup said to use

Else
MyResult = original_choice
End If

End Function

[jonh]

Still not sure I follow. Maybe...


If original_choice = "" Then
my_backup_option = DLookup("[Backup Option]", "mytable", "[customer] = '" & customer & "'")

select case my_backup_option
case backup_option1: MyResult = backup_option1
case backup_option2: MyResult = backup_option2
case backup_option3: MyResult = backup_option3
end select
Else
MyResult = original_choice
End If

[omega015]

I did think about select but that requires me to hardcode all the possible combinations that could be entered (and there could be a few mid() cases). I guess I might just have to hardcode the variables instead of holding them in a table. Thank you.

[jonh]

Nothing is hardcoded in the select. You need to compare values somehow.

What kind of text file is it? You can open CSV as a recordset.

[omega015]

its a flat file, below is an example of one line which holds some of the information I need. But not all lines are the same as there's a header, address record, and lots of other data I use so I read through each line of the text file then work out when information the line holds by the line prefix (e.g ASILIN), extract what I need and then I build another text file with the relevant information that is then submitted to an external program.

I am happy to submit the entire function so you can see but I thought I would keep it simple with the above example function as that is essentially what I am trying to do, but maybe I simplified it too much.

In this example below the data I want is actually missing so in this case I need to do the lookup so see what the backup field to use is for this particular partner (determined previously from the file). I will have already stored the value of that field when I was reading through the line so that is stored as a string but I am not sure how to call off that value from that string.


ASILIN 6.00eaea 81.51005010576862785 186278 Rome Carafe 1.0L 0.00 99.55 2 8.75LP160 45HMBOPG 05010576862709each LP79 CN 2

[jonh]

An example text file with instructions on how to process it would be of more use than a half written function.

I still don't know why are loading values from a database or what you're doing with them. Your first post you wrote


5023949 "Mid(Replace(GTIN,"" "",""""),2,10)"

So I guess in this case you want to apply the Mid and Replace functions to some variable.

If that's the case you could try using eval().

eval() wont know what GTIN is, so you'd need to replace GTIN with the variable's contents. e.g.


myvar = "--- foo ---"
oldval = "foo"
newval = "bar"
strcnv = "replace(%,#,$)"

strcnv = Replace(strcnv, "%", """" & myvar & """")
strcnv = Replace(strcnv, "#", """" & oldval & """")
strcnv = Replace(strcnv, "$", """" & newval & """")

result = Eval(strcnv)

Debug.Print result

'result = --- bar ---

[omega015]

So you have the full picture this is the entire function. The relevant part that I was trying to get to was lines 2280-2540 which I have now done as a select and disregarded the table. what this function does is read a text file, extracts the relevant information out of it and writes the information to another text file in a specific format.



Function OUT_Invoices(mycmd As String, EDI_OUT_Path As String, InvoicePrefix As String, EDI_OUT_Archive As String)
10 On Error GoTo err01
Dim FileName As String
20 FileName = Dir(EDI_OUT_Path & InvoicePrefix)
30 Do While FileName <> ""
40 Open (EDI_OUT_Path & FileName) For Input As #1
50 Do While Not EOF(1)
60 Line Input #1, myLine
70 Segment = Mid(myLine, 1, 6)
80 Select Case Segment

Case "ASIAUD" 'Auditing Record
90 CheckString = 10
100 Outfile = Mid(FileName, 1, Len(FileName) - 5) & "exp"
110 fFile = FreeFile
120 Open EDI_OUT_Path & Outfile For Append As #fFile

130 ASIAUD = Mid(myLine, 1, 6)
140 TradeID = Mid(myLine, 7, 35)
150 FormType = Mid(myLine, 42, 6)
160 ApplicationName = Mid(myLine, 48, 7)
170 EDIStandardsAgencyCode = Mid(myLine, 55, 1)
180 TransmissionControl = Mid(myLine, 56, 6)
190 DocType = Mid(myLine, 62, 2)
200 TestDocument = Mid(myLine, 64, 1)
210 ProductionDocument = Mid(myLine, 65, 1)
220 TradeIDInbound = Mid(myLine, 66, 35)
230 StoreID = Mid(myLine, 101, 20)

240 Case "ASIHDR" 'Header Record
250 ASIHDR = Mid(myLine, 1, 6)
260 InvoiceNumber = Mid(myLine, 7, 12)
270 InvoiceDate = Mid(myLine, 27, 2) & Mid(myLine, 19, 2) & Mid(myLine, 22, 2) 'YYMMDD
280 InvoiceTime = Mid(myLine, 29, 8)
290 InvoiceType = Mid(myLine, 37, 2)
300 asihdrPurchaseOrderNumber = Mid(myLine, 39, 20)
310 PurchaseOrderDate = Mid(myLine, 67, 2) & Mid(myLine, 59, 2) & Mid(myLine, 62, 2) 'YYMMDD
320 fCurrency = Mid(myLine, 69, 8)
330 ShipVia = Mid(myLine, 77, 12)
340 CustomerName = Mid(myLine, 89, 30)
350 CustomerNumberInternal = Mid(myLine, 119, 15)
360 ShipViaExternalValue = Mid(myLine, 134, 15)
370 BatchNumber = Mid(myLine, 149, 12)
380 StandardCarrierAlphaCode = Mid(myLine, 161, 12)
390 DepartmentNumber = Mid(myLine, 173, 30)
400 InternalVendorCodeEDI = Mid(myLine, 203, 20)
410 OrderPlacer = Mid(myLine, 223, 30)
420 OrderPlacerPhoneNumber = Mid(myLine, 253, 12)
430 UserDefinedField1 = Mid(myLine, 265, 8)
440 UserDefinedField2 = Mid(myLine, 273, 8)
450 UserDefinedField3 = Mid(myLine, 281, 8)
460 UserDefinedField4 = Mid(myLine, 289, 8)
470 UserDefinedField5 = Mid(myLine, 297, 8)
480 ContractNumber = Mid(myLine, 305, 20)
490 OrderClass = Mid(myLine, 325, 10)
500 Consign = Mid(myLine, 335, 8)
510 asihdrMiscellaneousData1 = Mid(myLine, 343, 60)
520 asihdrMiscellaneousData2 = Mid(myLine, 403, 60)
530 TransportationMode = Mid(myLine, 463, 2)
540 asihdrPickDemandNumber = Mid(myLine, 465, 12)
550 FreightCollectionTerms = Mid(myLine, 477, 20)
560 TrackingNumber = Mid(myLine, 497, 30)
570 PRONumber = Mid(myLine, 527, 15)
580 asihdrBillofLadingNumber = Mid(myLine, 542, 30)
590 asihdrBillofLadingSealNumber = Mid(myLine, 572, 25)
600 asihdrBillofLadingAppointmentNumber = Mid(myLine, 597, 30)
610 asihdrBillofLadingTruckNumber = Mid(myLine, 627, 25)
620 asihdrOrderNumber = Mid(myLine, 652, 25)
630 ReturnOrderCustomerReferenceNumber = Mid(myLine, 677, 20)
640 ReturnOrderAuthorizationNumber = Mid(myLine, 697, 12)
650 MerchAmount = Mid(myLine, 709, 18)
660 AdditionalChargeAmount = Mid(myLine, 727, 19)
670 FreightAmount = Mid(myLine, 746, 18)
680 FOB = Mid(myLine, 764, 12)
690 AlternatePO = Mid(myLine, 776, 60)
700 ShipFromName = Mid(myLine, 836, 35)
710 ShipFromAddress1 = Mid(myLine, 871, 35)
720 ShipFromAddress2 = Mid(myLine, 906, 35)
730 ShipFromAddress3 = Mid(myLine, 941, 35)
740 ShipFromCity = Mid(myLine, 976, 30)
750 ShipFromCountry = Mid(myLine, 1006, 20)
760 ShipFromState = Mid(myLine, 1026, 2)
770 ShipFromPostalCode = Mid(myLine, 1028, 12)
780 OrderedbyName = Mid(myLine, 1040, 30)
790 OrderedbyAddress1 = Mid(myLine, 1070, 35)
800 OrderedbyAddress2 = Mid(myLine, 1105, 35)
810 OrderedbyAddress3 = Mid(myLine, 1140, 35)
820 OrderedbyCity = Mid(myLine, 1175, 30)
830 OrderedbyState = Mid(myLine, 1205, 2)
840 OrderedbyPostalCode = Mid(myLine, 1207, 12)
850 OrderedbyCountry = Mid(myLine, 1219, 20)
860 asihdrShipmentNumber = Mid(myLine, 1239, 12)
870 CITApprovalCode = Mid(myLine, 1251, 15)
880 ShipperName = Mid(myLine, 1266, 25)
890 ShipDate = Mid(myLine, 1299, 2) & Mid(myLine, 1291, 2) & Mid(myLine, 1294, 2)
900 TotalAmountNoDiscounts = Mid(myLine, 1301, 15)


910 Case "ASIBTA" 'Bill To Address Record
920 ASIBTA = Mid(myLine, 1, 6)
930 BillToCode = Mid(myLine, 7, 16)
940 BillToName = Mid(myLine, 23, 35)
950 BillToAddressLine1 = Mid(myLine, 58, 35)
960 BillToAddressLine2 = Mid(myLine, 93, 35)
970 BillToAddressLine3 = Mid(myLine, 128, 35)
980 BillToCity = Mid(myLine, 163, 30)
990 BillToState = Mid(myLine, 193, 2)
1000 BillToPostalCode = Mid(myLine, 195, 12)
1010 BillToCountry = Mid(myLine, 207, 20)
1020 BillToPhone = Mid(myLine, 227, 12)
1030 BillToFax = Mid(myLine, 239, 12)
1040 longBillToName = Mid(myLine, 251, 40)


1050 Case "ASISTA" 'Ship To Address Record
1060 ASISTA = Mid(myLine, 1, 6)
1070 ShipToCode = Mid(myLine, 7, 16)
1080 ShipToName = Mid(myLine, 23, 35)
1090 ShipToAddressLine1 = Mid(myLine, 58, 35)
1100 ShipToAddressLine2 = Mid(myLine, 93, 35)
1110 ShipToAddressLine3 = Mid(myLine, 128, 35)
1120 ShipToCity = Mid(myLine, 163, 30)
1130 ShipToState = Mid(myLine, 193, 2)
1140 ShipToPostalCode = Mid(myLine, 195, 12)
1150 ShipToCountry = Mid(myLine, 207, 20)
1160 ShipToPhone = Mid(myLine, 227, 12)
1170 ShipToFax = Mid(myLine, 239, 12)
1180 StoreID = Mid(myLine, 251, 20)
1190 longShipToName = Mid(myLine, 271, 40)

1200 Case "ASIRTA" 'Remit To Address Record
1210 ASITRA = Mid(myLine, 1, 6)
1220 RemitToCode = Mid(myLine, 7, 16)
1230 RemitToName = Mid(myLine, 23, 35)
1240 RemitToAddressLine1 = Mid(myLine, 58, 35)
1250 RemitToAddressLine2 = Mid(myLine, 93, 35)
1260 RemitToAddressLine3 = Mid(myLine, 128, 35)
1270 RemitToCity = Mid(myLine, 163, 30)
1280 RemitToState = Mid(myLine, 193, 2)
1290 RemitToPostalCode = Mid(myLine, 195, 12)
1300 RemitToCountry = Mid(myLine, 207, 20)
1310 RemitToPhone = Mid(myLine, 227, 12)
1320 RemitToFax = Mid(myLine, 239, 12)

1330 Case "ASITER" 'Terms
1340 ASITER = Mid(myLine, 1, 6)
1350 TermsCodeInternal = Mid(myLine, 7, 12)
1360 TermsCodeExternal = Mid(myLine, 19, 12)
1370 InvoiceDatebasisdate = Mid(myLine, 31, 10)
1380 DiscountPercent = Round(Mid(myLine, 41, 8), 4)
1390 NetDays = Mid(myLine, 49, 4)
1400 termDiscountAmount = Round(Mid(myLine, 53, 18), 4)
1410 DiscountDate = Mid(myLine, 71, 10)
1420 DueDate = Mid(myLine, 81, 10)
1430 TermsDescription = Mid(myLine, 91, 60)
1440 DiscountDays = Mid(myLine, 151, 3)
1450 termDiscountPercentnodecimal = Mid(myLine, 154, 7)
1460 termDiscountAmountnodecimal = Mid(myLine, 161, 17)


1470 Recid = Left("HDR" & Space(3), 3)
1480 vers = Left("07" & Space(2), 2)
1490 customer = Left(TradeID & Space(20), 20)
1500 If IsNull(DLookup("[Partner ID]", "Partners", "[Partner ID] = '" & customer & "'")) Then
1510 GoTo errpartner01
1520 End If
1530 invref = Left(InvoiceNumber & Space(8), 8)
1540 Ordref = Left(asihdrOrderNumber & Space(8), 8)
1550 Cusref = Left(asihdrPurchaseOrderNumber & Space(15), 15)
1560 orddate = Left(PurchaseOrderDate & Space(6), 6)
1570 Invdate = Left(InvoiceDate & Space(6), 6)
1580 deliv = Left("" & Space(15), 15)
1590 custloc = Left(ShipToCode & Space(22), 22)
1600 billName = Left(BillToName & Space(40), 40)
1610 addr1 = Left(BillToAddressLine1 & Space(30), 30)
1620 addr2 = Left(BillToAddressLine2 & Space(30), 30)
1630 addr3 = Left(BillToAddressLine3 & Space(30), 30)
1640 addr4 = Left(BillToCity & Space(30), 30)
1650 addr5 = Left("" & Space(30), 30)
1660 postcode = Left(BillToPostalCode & Space(10), 10)
1670 deladd = Left("" & Space(4), 4)
1680 daddr1 = Left(ShipToName & Space(30), 30)
1690 daddr2 = Left(ShipToAddressLine1 & Space(30), 30)
1700 daddr3 = Left(ShipToAddressLine2 & Space(30), 30)
1710 daddr4 = Left(ShipToAddressLine3 & Space(30), 30)
1720 daddr5 = Left(ShipToCity & Space(30), 30)
1730 dpostcode = Left(ShipToPostalCode & Space(10), 10)
1740 terms = Left(TermsDescription & Space(30), 30)
1750 transport = Left("" & Space(30), 30)
1760 cucount = Left(BillToCountry & Space(2), 2)
1770 cutaxrg = Left("" & Space(15), 15)
1780 dest = Left("" & Space(20), 20)
1790 rep = Left("" & Space(4), 4)
1800 area = Left("" & Space(4), 4)
1810 Delsuf = Left("" & Space(4), 4)
1820 Print #fFile, Recid; "|"; vers; "|"; customer; "|"; invref; "|"; Ordref; "|"; Cusref; "|"; orddate; "|"; Invdate; "|"; deliv; "|"; custloc; "|"; billName; "|"; addr1; "|"; addr2; "|"; addr3; "|"; addr4; "|"; addr5; "|"; postcode; "|"; deladd; "|"; daddr1; "|"; daddr2; "|"; daddr3; "|"; daddr4; "|"; daddr5; "|"; dpostcode; "|"; terms; "|"; transport; "|"; cucount; "|"; cutaxrg; "|"; dest; "|"; rep; "|"; area; "|"; Delsuf; "|"

1830 Case "ASILIN" 'Line Item Record
1840 ASILIN = Mid(myLine, 1, 6)
1850 QuantityShipped = Mid(myLine, 7, 10)
1860 QuantityUMInternal = Mid(myLine, 17, 2)
1870 QuantityUMExternal = Mid(myLine, 19, 2)
1880 UnitPrice = Round(Mid(myLine, 21, 14), 4)
1890 UPCCodeInternal = Mid(myLine, 35, 14)
1900 UPCCodeExternal = Mid(myLine, 49, 14)
1910 ItemNumberInternal = Mid(myLine, 63, 40)
1920 ItemNumberExternal = Mid(myLine, 103, 40)
1930 ItemDescriptionInternal = Mid(myLine, 143, 30)
1940 TotalAdditionalCharges = Mid(myLine, 173, 15)
1950 LineExtendedTotal = Round(Mid(myLine, 188, 15), 4)
1960 LineNumber = Mid(myLine, 203, 9)
1970 LineWeight = Mid(myLine, 212, 13)
1980 asilinOrderNumber = Mid(myLine, 225, 12)
1990 asilinPurchaseOrderNumber = Mid(myLine, 237, 20)
2000 UserDefined1 = Mid(myLine, 257, 8)
2010 UserDefined2 = Mid(myLine, 265, 8)
2020 UserDefined3 = Mid(myLine, 273, 8)
2030 UserDefined4 = Mid(myLine, 281, 8)
2040 UserDefined5 = Mid(myLine, 289, 8)
2050 gtin = Mid(myLine, 297, 14)
2060 FullUMCode = Mid(myLine, 311, 8)
2070 asilinMiscellaneousData1 = Mid(myLine, 319, 60)
2080 asilinMiscellaneousData2 = Mid(myLine, 379, 60)
2090 asilinPickDemandNumber = Mid(myLine, 439, 12)
2100 ConversionFactor1 = Mid(myLine, 451, 20)
2110 ConversionFactor2 = Mid(myLine, 471, 20)
2120 MasterCartonUMCube = Mid(myLine, 491, 11)
2130 MasterCartonUMLength = Mid(myLine, 502, 11)
2140 MasterCartonUMWidth = Mid(myLine, 513, 11)
2150 MasterCartonUMHeight = Mid(myLine, 524, 11)
2160 MasterCartonUMNetWeight = Mid(myLine, 535, 11)
2170 MasterCartonUMGrossWeight = Mid(myLine, 546, 11)
2180 CountryofOrigin = Mid(myLine, 557, 10)
2190 EDIOrderLineNumber = Mid(myLine, 567, 20)

2200 Case "ASITAX" 'Tax Record
2210 ASITAX = Mid(myLine, 1, 6)
2220 TaxCode = Mid(myLine, 7, 20)
2230 asitaxExternalTaxCode = Mid(myLine, 27, 16)
2240 asitaxAmountWhichTaxIsBased = Round(Mid(myLine, 43, 10), 4)
2250 TaxAmount = Round(Mid(myLine, 53, 10), 4)
2260 TaxPercentage = Mid(myLine, 63, 8)


2270 Recid = Left("ILD" & Space(3), 3)
2280 If Trim(asilinMiscellaneousData1) = "" Then
2290 Select Case customer
Case 5450534005814#
2300 Product = Left(UPCCodeInternal & Space(22), 22)
2310 Case 5013546020222#
2320 Product = Left(ItemNumberExternal & Space(22), 22)
2330 Case 5000167000003#
2340 Product = Left(UPCCodeInternal & Space(22), 22)
2350 Case 5023949000004#
2360 Product = Left(Mid(Replace(gtin, " ", ""), 2, 19) & Space(22), 22)
2370 Case 5013546229809#
2380 Product = Left(gtin & Space(22), 22)
2390 Case 5029463000001#
2400 Product = Left(ItemNumberExternal & Space(22), 22)
2410 Case 5010011900045#
2420 Product = Left(Mid(Replace(gtin, " ", ""), 2, 19) & Space(22), 22)
2430 Case 5000119000006#
2440 Product = Left(Mid(Replace(gtin, " ", ""), 2, 19) & Space(22), 22)
2450 Case 5000169000001#
2460 Product = Left(Mid(Replace(gtin, " ", ""), 2, 19) & Space(22), 22)
2470 Case 5015715444440#
2480 Product = Left(Mid(Replace(gtin, " ", ""), 2, 19) & Space(22), 22)
2490 Case 5014457000000#
2500 Product = Left(UPCCodeInternal & Space(22), 22)
2510 End Select
2520 Else
2530 Product = Left(asilinMiscellaneousData1 & Space(22), 22)
2540 End If
2550 Qty = Left(Round(QuantityShipped, 0) & Space(18), 18)
2560 UOM = Left(QuantityUMExternal & Space(15), 15)
2570 Mult = Left("1" & Space(18), 18)
2580 Price = Left(Format(Round(UnitPrice, 4), "Fixed") & Space(18), 18)
2590 Priceper = Left("1" & Space(18), 18)
2600 Special = Left("" & Space(15), 15)
2610 Dscperc = Left("0.00" & Space(18), 18)
2620 Dscval = Left("0.00" & Space(18), 18)
2630 Extpri = Left(Format(Round(LineExtendedTotal, 4), "Fixed") & Space(18), 18)
2640 Extpay = Left(Format(Round(LineExtendedTotal + TaxAmount - (((LineExtendedTotal + TaxAmount) / 100) * DiscountPercent), 2), "Fixed") & Space(18), 18)
2650 Vatcode = Left(TaxCode & Space(1), 1)
2660 Vatperc = Left(Replace(TaxPercentage, " ", "") & Space(18), 18)
2670 Vatcost = Left(Format(TaxAmount, "Fixed") & Space(18), 18)
2680 Setdisc = Left(Format(Round((((LineExtendedTotal + TaxAmount) / 100) * DiscountPercent), 2), "Fixed") & Space(18), 18)
2690 Ordref = Left(asilinOrderNumber & Space(8), 8)
2700 Delref = Left(asihdrShipmentNumber & Space(12), 12)
2710 Cusref = Left(asilinPurchaseOrderNumber & Space(15), 15)
2720 Deldate = Left(ShipDate & Space(6), 6)
2730 Group = Left("" & Space(4), 4)
2740 Category = Left("" & Space(4), 4)
2750 Usage = Left("" & Space(4), 4)
2760 Desc = Left(ItemDescriptionInternal & Space(40), 40)
2770 Delsuf = Left("" & Space(4), 4)
2780 Custitem = Left("" & Space(6), 6)
2790 Rellev = Left("" & Space(4), 4)
2800 Reldesc = Left("" & Space(30), 30)
2810 Qtyord = Left("" & Space(18), 18)
2820 Qtyos = Left("" & Space(18), 18)
2830 Longprod = Left(UPCCodeInternal & Space(40), 40)
2840 Print #fFile, Recid; "|"; Product; "|"; Qty; "|"; UOM; "|"; Mult; "|"; Price; "|"; Priceper; "|"; Special; "|"; Dscperc; "|"; Dscval; "|"; Extpri; "|"; Extpay; "|"; Vatcode; "|"; Vatperc; "|"; Vatcost; "|"; Setdisc; "|"; Ordref; "|"; Delref; "|"; Cusref; "|"; Deldate; "|"; Group; "|"; Category; "|"; Usage; "|"; Desc; "|"; Delsuf; "|"; Custitem; "|"; Rellev; "|"; Reldesc; "|"; Qtyord; "|"; Qtyos; "|"; Longprod; "|"



2850 Case "ASICHG" 'Additional Charges Record
2860 ASICHG = Mid(myLine, 1, 6)
2870 ChargeDescription = Mid(myLine, 7, 40)
2880 ChargeCodeInternal = Mid(myLine, 47, 10)
2890 ChargeCodeExternal = Mid(myLine, 57, 15)
2900 ChargeAmount = Mid(myLine, 72, 15)
2910 ChargeLineNumber = Mid(myLine, 87, 8)
2920 ChargeTypeCorA = Mid(myLine, 95, 1)
2930 MethodOfHandling = Mid(myLine, 96, 2)
2940 ChargeUnitOfMeasure = Mid(myLine, 98, 2)
2950 ChargeTaxAmount = Mid(myLine, 100, 10)
2960 ChargeVATAmount = Mid(myLine, 110, 10)
2970 ChargePercent = Mid(myLine, 120, 10)
2980 ChargeAmountNoDecimal = Mid(myLine, 130, 15)
2990 ChargeTaxAmountNoDecimal = Mid(myLine, 145, 10)
3000 ChargeVATAmountNoDecimal = Mid(myLine, 155, 10)


3010 Case "ASINTE" 'Order and Line Comments
3020 ASINTE = Mid(myLine, 1, 6)
3030 INV = Mid(myLine, 7, 3)
3040 Note = Mid(myLine, 10, 100)


3050 Case "ASISTX" 'Tax Summary Record
3060 ASISTX = Mid(myLine, 1, 6)
3070 InternalTaxCode = Mid(myLine, 7, 20)
3080 asistxExternalTaxCode = Mid(myLine, 27, 16)
3090 asistxAmountWhichTaxIsBased = Round(Mid(myLine, 43, 10), 4)
3100 TaxAmount = Round(Mid(myLine, 53, 10), 4)
3110 TaxPercentage = Mid(myLine, 63, 8)
3120 TaxChargeAmount = Mid(myLine, 71, 10)

3130 Recid = Left("VAT" & Space(3), 3)
3140 Vatcode = Left(asistxExternalTaxCode & Space(1), 1)
3150 Vatperc = Left(Replace(TaxPercentage, " ", "") & Space(18), 18)
3160 Invdate = Left(InvoiceDate & Space(6), 6)
3170 Goods = Left(Format(asistxAmountWhichTaxIsBased, "Fixed") & Space(18), 18)
3180 Gval = Left(Format(asistxAmountWhichTaxIsBased, "Fixed") & Space(18), 18)
3190 Vatval = Left(Format(TaxAmount, "Fixed") & Space(18), 18)
3200 Setdisc = Left(Format(termDiscountAmount, "Fixed") & Space(18), 18)
3210 Payval = Left(asistxAmountWhichTaxIsBased + TaxAmount & Space(18), 18)
3220 Print #fFile, Recid; "|"; Vatcode; "|"; Vatperc; "|"; Invdate; "|"; Goods; "|"; Gval; "|"; Vatval; "|"; Setdisc; "|"; Payval; "|"


3230 Case "ASIEND" 'Ending Record
3240 ASIEND = Mid(myLine, 1, 6)
3250 TotalLineItems = Mid(myLine, 7, 6)
3260 TotalWeight = Mid(myLine, 13, 12)
3270 TotalVolume = Mid(myLine, 25, 12)
3280 TotalMonetaryAmount = Mid(myLine, 37, 15)
3290 TotalUnitsShipped = Mid(myLine, 52, 10)
3300 TotalPackages = Mid(myLine, 62, 10)
3310 TrackingNumber = Mid(myLine, 72, 8)
3320 TotalMonetaryAmountwotax = Mid(myLine, 80, 15)
3330 TotalTaxAmount = Mid(myLine, 95, 15)
3340 FullTrackingNumber = Mid(myLine, 110, 30)
3350 blank = Mid(myLine, 140, 14)
3360 TotalMonetaryAmount2 = Mid(myLine, 154, 15)
3370 TotalMonetaryAmountwotax2 = Mid(myLine, 169, 15)
3380 TotalTaxAmount2 = Mid(myLine, 184, 15)
3390 TotalMonetaryAmountwdisc = Mid(myLine, 199, 15)
3400 blank2 = Mid(myLine, 214, 20)
3410 BillofLadingPRONumber = Mid(myLine, 234, 15)
3420 asiendBillofLadingNumber = Mid(myLine, 249, 30)
3430 asiendBillofLadingSealNumber = Mid(myLine, 279, 25)
3440 asiendBillofLadingAppointmentNumber = Mid(myLine, 304, 30)
3450 asiendBillofLadingTruckNumber = Mid(myLine, 334, 25)
3460 LoadNumber = Mid(myLine, 359, 25)
3470 MasterBillofLadingNumber = Mid(myLine, 384, 30)
3480 asiendShipmentNumber = Mid(myLine, 414, 12)
3490 TotalMerchAmount = Format(Round(Mid(myLine, 426, 15), 4), "Fixed")
3500 TotalMerchAmountNoDecimal = Mid(myLine, 441, 15)
3510 endDiscountAmount = Format(Round(Mid(myLine, 456, 15), 4), "Fixed")
3520 endDiscountAmountNoDecimal = Round(Mid(myLine, 471, 15), 4)
3530 Recid = Left("ITD" & Space(3), 3)
3540 Vatcode = Left("" & Space(1), 1)
3550 Vatperc = Left("" & Space(18), 18)
3560 Invdate = Left(InvoiceDate & Space(6), 6)
3570 Goods = Left(Format(TotalMerchAmount, "Fixed") & Space(18), 18)
3580 Gval = Left(Format(TotalMerchAmount, "Fixed") & Space(18), 18)
3590 Vatval = Left(Format(TaxAmount, "Fixed") & Space(18), 18)
3600 Setdisc = Left(Format(endDiscountAmount, "Fixed") & Space(18), 18)
3610 Payval = Left(TotalMerchAmount + TaxAmount & Space(18), 18)
3620 Print #fFile, Recid; "|"; Vatcode; "|"; Vatperc; "|"; Invdate; "|"; Goods; "|"; Gval; "|"; Vatval; "|"; Setdisc; "|"; Payval; "|"

3630 Close #fFile

3640 Case Else
3650 End Select

3660 Loop
3670 Close #1

3680 Name EDI_OUT_Path & FileName As EDI_OUT_Archive & "split archive\" & FileName
cont:
3690 FileName = Dir(EDI_OUT_Path & InvoicePrefix)
3700 Loop
3710 Exit Function
errpartner01:
3720 Call report_error(mycmd, 0, "Uknown Trading Partner", "EDI Partner: " & customer & " in file '" & FileName & "' unknown")
3730 Close #fFile
3740 Kill EDI_OUT_Path & Outfile
3750 Close #1
3760 Name EDI_OUT_Path & FileName As EDI_OUT_Path & "error\" & FileName
3770 GoTo cont
err01:
3780 Call report_error(mycmd, Erl, Err.Number, Err.Description)
3790 Close #fFile
3800 Kill EDI_OUT_Path & Outfile
End Function

[jonh]

Well I had a look and I'm not sure that you could simplify the code that much. You could certainly make it more readable just by putting the code into functions.


Select Case mfv.mfs.Segment
Case "ASIAUD": ASIAUD mfv 'Auditing Record
Case "ASIHDR": ASIHDR mfv 'Header Record
Case "ASIBTA": ASIBTA mfv 'Bill To Address Record
Case "ASISTA": ASISTA mfv 'Ship To Address Record
Case "ASITRA": ASITRA mfv 'Remit To Address Record
Case "ASITER": If Not ASITER(mfv) Then GoTo errpartner01 'Terms
Case "ASILIN": ASILIN mfv 'Line Item Record
Case "ASITAX": ASITAX mfv 'Tax Record
Case "ASICHG": ASICHG mfv 'Additional Charges Record
Case "ASINTE": ASINTE mfv 'Order and Line Comments
Case "ASISTX": ASISTX mfv 'Tax Summary Record
Case "ASIEND": ASIEND mfv 'Ending Record
End Select

Note, that if ASITER doesn't return true it jumps to the error label, but the return value is ignored in the other cases.


Public Function ASITER(mfv As MyFileVars) As Boolean

If IsNull(DLookup("[Partner ID]", "Partners", "[Partner ID] = '" & mfv.customer & "'")) Then
Exit Function
Else
ASITER = True
End If

You can ignore this next part. But in case you're wondering what "mfv As MyFileVars" is...

I don't know how/if you are declaring variables. I created various user defined types and linked them all together in another udt called MyFileVars.
I'm not sure I'd even recommend you do this in this case. Normally it helps to keep things structured but there are so many variables here you might be better off sticking to what you've got if it works.


Public Type MyFileVars
mfs As tMyFileStuff 'vars relating to files
customer As String 'current customer
ASIAUD As tASIAUD 'vars for ASIAUD
ASIHDR As tASIHDR 'etc
ASIBTA As tASIBTA 'etc
'...
End Type


Public Type tMyFileStuff
filename As String
outfile As String
ErrFile As String

'file handles
fIn As Integer
fOut As Integer
fErr As Integer

'inputted text
myLine As String
Segment As String
End Type

Public Type tASIAUD
CheckString As Variant
ASIAUD As Variant
TradeID As Variant
FormType As Variant
ApplicationName As Variant
EDIStandardsAgencyCode As Variant
TransmissionControl As Variant
DocType As Variant
TestDocument As Variant
ProductionDocument As Variant
TradeIDInbound As Variant
StoreID As Variant
End Type

MyFileVars is passed into each function as mfv ...


Public Function ASIAUD(mfv As MyFileVars) As Boolean
Dim myLine
myLine = mfv.mfs.myLine

With mfv.ASIAUD
.CheckString = 10
.ASIAUD = Mid(myLine, 1, 6)
.TradeID = Mid(myLine, 7, 35)
.FormType = Mid(myLine, 42, 6)
.ApplicationName = Mid(myLine, 48, 7)
.EDIStandardsAgencyCode = Mid(myLine, 55, 1)
.TransmissionControl = Mid(myLine, 56, 6)
.DocType = Mid(myLine, 62, 2)
.TestDocument = Mid(myLine, 64, 1)
.ProductionDocument = Mid(myLine, 65, 1)
.TradeIDInbound = Mid(myLine, 66, 35)
.StoreID = Mid(myLine, 101, 20)
End With
End Function

[jonh]

Actually you could simplify that section a little bit by grouping the cases and formatting the string at the end.


If Trim(asilinMiscellaneousData1) = "" Then
Select Case customer
Case 5450534005814#, 5000167000003#, 5014457000000#
Product = UPCCodeInternal
Case 5013546020222#, 5029463000001
Product = ItemNumberExternal
Case 5013546229809#
Product = gtin
Case 5010011900045#, 5000119000006#, 5000169000001#, 5015715444440#, 5023949000004#
Product = Mid(Replace(gtin, " ", ""), 2, 19)
End Select
Else
Product = asilinMiscellaneousData1
End If

Product = Product & String(22 - Len(Product), " ")