Create Module

[Gerrit.B]

In my table if have fields like Carrier, BL and others.
In the table with carriers i have already created fields like URL1 and URL2.
All carriers have their own way of building a url for tracking purposes.
So i wanted to create a module so i can directly see the actual status of every shipment, in a AxtiveX Control (webbrowser)
For every carrier want to define a rule to build the url.

Who could help me to create a first start of the module, or tell me what i am doing wrong.


Option Compare Database
Option Explicit

Dim URL As String



Function Tracking_URL(Carrier As Byte, BL As String, URL1 As String, URL2 As String)
If Carrier = 58 Then ' Place here the carrier ID
URL = "URL1 & BL & URL2"
Else
End If
If Carrier = 62 Then ' Place here the carrier ID
URL = "URL1 & BL"
Else
URL = "Empty"
End Function


G.

 

[Norie]

You shouldn't have quotes around the concatenations.

Perhaps more importantly you should use the name of the function, which appears to be Tracking_URL instead of just URL.

If there are a lot of carriers then it's probably better to use Select Case.

That could also be helpful if more than one carrier uses the same rules.

Function Tracking_URL(Carrier As Byte, BL As String, URL1 As String, URL2 As String) As String
 
    Select Case Carrier
 
            Case 58
               Tracking_URL = URL1 & BL & URL2
            
            Case 62
               Tracking_URL = URL1 & BL
   
            Case Else
               Tracking_URL = "Empty
 
     End Select
 
End Function

When there are carriers with the same rules you can just add them to the existing Cases.

For example if carriers 45 and 76 have the same rules as carrier 58 you could use this.

Case 45, 58, 78

 

[Gerrit.B]

Norie,

Tested this in sample database and it works great.

Thanks

G.

 

[revans]

I'm inclined to use an enum in these situations as I don't like remembering magic id numbers. Something like this will work in a module

Public Enum Carrier
  USPS = 1
  UPS = 58
  FedEx = 59
  SomeGuyOnABike = 60
End Enum
 
Function Tracking_URL(TheCarrier As Carrier)
...
  Select Case TheCarrier
    Case Carrier.UPS, Carrier.FedEx
    Case Carrier.USPS
    Case Carrier.SomeGuyOnABike
    Case Else
  End Select
...
End Function

hth,

Rich

 

[Gerrit.B]

I started with code below (from Norie)

Option Compare Database
Option Explicit

Function Tracking_URL(Carrier As Byte, BL As String, URLP1 As String, URLP2 As String) As String
'If more use the same rules you can use Case 45, 58, 78
'Selected fields need to be filled, even if they are not needed. Or else it will give an ERROR
'
'
'
'
Select Case Carrier

Case 58 'Add here companyID
Tracking_URL = URLP1 & Mid(BL, 5, 9)

Case 55 'Add here companyID
Tracking_URL = URLP1 & Mid(BL, 5, 5) & URLP2

Case Else
Tracking_URL = "Empty"

End Select

End Function


Now i get an error when selecting empty fields, for carriers where these fields are NOT used at all.
Sample Carrier 1 uses only URL1 and part of BL so field URL2 is selected in module but not used. How can i avoid all these errors.

G.

 

[Norie]

How exactly are you using the formula?

What errors are you getting?