I need to translate numbers to text 1

[mdctsg]

I need to translate numbers to text from my company db. I am using the statement below to pull out the info into a csv file. In the Shipvia column we us numbers 1 = Post office, 2 = Fedex, 3 = UPS, 4 = Courier. When I pull it out I get the numbers. Is there a way to translate them to the text listed?

"SELECT Orders.OrderNo, Shipment.Cost, Shipment.Weight, Shipvia FROM Orders, Shipment, WHERE Orders.m_primaryKey = Shipment.m_foreignKey00"

Thanks
Bob

 

[allanon]

Place this code into a module:

Public Function MyShipvia(ByVal MyInt As Integer) As String
MyShipvia = ""
Select Case MyInt
Case 1
MyShipvia = "Post Office"
Case 2
MyShipvia = "Fedex"
Case 3
MyShipvia = "UPS"
Case 4
MyShipvia = "Courier"
Case Else
MyShipvia = "Other"
End Select
End Function

Change your query to:

"SELECT Orders.OrderNo, Shipment.Cost, Shipment.Weight, MyShipvia(Shipvia) as Shipping FROM Orders, Shipment, WHERE Orders.m_primaryKey = Shipment.m_foreignKey00"

 

[mdctsg]

Sorry I left out a field name in my first post would this make a difference

"SELECT Orders.OrderNo, Shipment.Cost, Shipment.Weight, Shipvia.Type FROM Orders, Shipment, WHERE Orders.m_primaryKey = Shipment.m_foreignKey00"

 

[allanon]

No Difference.

"SELECT Orders.OrderNo, Shipment.Cost, Shipment.Weight, MyShipvia(Shipvia) as Shipping, Type FROM Orders, Shipment, WHERE Orders.m_primaryKey = Shipment.m_foreignKey00"

 

[MichaelRed]

Easier (and probably faster) to just use the "CHOOSE" function. It is well documented in HELP, which is available with your installation of Ms. A., and does not require internet access, having a module / procedure, or recourse to Tek-Tips professional programming support.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PWise]

if what you need is to display text like on a report create a combo box with these propties
row source type valuelist
row source '1 ; Post office; 2; Fedex;3; UPS;4;Courier'
columns 2
columns witdhs '0";3"'

 

[BillPower]

Sorry to interject at this stage of the thread, though all the suggestions above are very good, one point's been missed. What happens if Fedex goes out of business or you want to add a Shipment option etc. You would need to edit your code and if this is a distributed database, re-distribute it to all users if a change needed to be made.

What I think you need to do is to create a new table with 2 fields.

Field Name: Shipvia ShipMethod
*************************************
1 Post office
2 Fedex
3 UPS
4 Courier

Index the field Shipvia No Duplicates.

In Relationships make a one to many join from the Shipvia in the New Table to the Shipvia in the Existing Table, Enforce Referential Integrity.

Add the new table to your query and add ShipMethod as a field. Whatever Shipvia number is input, ShipMethod will automatically be displayed.

Any future changes can now be done without the need for coding or upgrading users. All that will need to be done is to edit the new table.

 

[allanon]

billpower is absolutely right here. The proper way is through the use of a lookup table. Then it is easy to add and remove values at any time.

 

[mdctsg]

Thanks for all the input. Since I am dealing with an access db I posted the question here instead of VB. What I am doing is using the statement in vb to pull the info into a csv file. I cannot alter the company db in anyway. As for the services we ship with I was hoping to be able to have something like

If 1 then Post Office
If 2 then Fedex
If 3 then UPS
If 4 then Courier
If ? I can add more if needed

 

[allanon]

If you are doing this with vb then either use the case statement:

MyShipvia(MyVariable)


Or

Use MichaelRed's method:

Choose(MyVariable, "Post Office", "Fedex", "UPS", "Courier&quot

Either should work fine.

 

[mdctsg]

I keep getting undefined MyShipvia in expression error

Any tips?

Bob

 

[allanon]

Have you created the function in the modules? If so then compile your app. There is an error in the function.

 

[BillPower]

You say that you can't alter your client's db.

Why not create a lookup table in the Your db. No need for any code, just SQL!