Replacing Table Fields w/ Another value

[Rsandoval]

Hello,

Every month I get an Excel Spreadsheet file with Airline Schedules for that month. I import the spreadsheet into Access 2002 so I can run a query from an .asp page. This information will enable passengers to view/sort the data on the web. I need to convert some of the data to user friendly names so the passengers will understand what is what.

tblData

Fields Data Description
Time 1034 Time in military time
Airline WN airline code (convert)
FlightNumber 2151 flight number
Aircraft M80 aircraft
Frequency X6 too many variations to convert
City Dallas City arriving from

I need to convert the Airline code names to user friendly names.

AA = American Airlines
DL = Delta Airlines
CO = Continental Airlines
WN = Southwest Airlines
And so on….

I was thinking a module where I could run a macro to convert the data after I imported it. I have been experimenting with Select statements but I do not know how to program modules and how to run them. Is this a Private Sub or function or what? Here is what I have:

Dim strAirline As String
Dim rsAirlines As Recordset

'Open the database and tables
Set conn = CurrentDb()

strSQL = "SELECT * tblData.Airline from tblData"

'Open recordset
Set rsAirlines = tblData.OpenRecordset(strSQL)

Do Until rsAirlines.EOF
Select Case Airline
Case "AA"
Airline = "American Airlines"
Case "CO"
Airline = "Continental Airlines"
End Select
rsAirlines.MoveNext
Loop

rsAirline.Close
Set conn = Nothing

But I am getting so confused on the whole thing and frustrated. I can code this in .asp but Access seems so foreign. Please help. I appreciate it.

Roger

 

[cmmrfrds]

A module is a place where you can create functions or subroutines that can be used throughout the application. In a module you can create variables in the declaration section in the front part of the module that can be at the module level scope or can be made Public to be at the application level scope. Of course, variables defined in a function are in scope for the duration of the function.

A public function can be initiated/called from anyplace in the application, including macros, Forms, and other functions. An easy way to test a function is to do a RunCode in a Macro and pick the function you want to test or run.

An example function.
Function Testit()
dim avar as string
set avar "something"
debug.print "this avar value = "; avar
End Function
Run the function from a Macro and then do a "Control G" which will bring up the immediate window so you can view the debug printout.

Hope this gets you started.

 

[scking]

Is it an Access 97 or Access 2000 database. That kinda determines how you handle the objects.

Access 2000 uses ADO as the default vs DAO for a recordset object. So if you are trying to use an ADO recordset everything is fine. However, Access 2000 uses the CurrentProject.Connection for the assignment.
Set conn = CurrentProject.Connection

Access 97 uses DAO as the default for a recordset object. So you would need to include the following.
Dim rsAirlines As DAO.Recordset

And you also need to update the recordset differently.
Do Until rsAirlines.EOF
rsAirlines.Edit
Select Case rsAirlines!Airline
Case "AA"
Airline = "American Airlines"
Case "CO"
Airline = "Continental Airlines"
End Select
rstAirlines.Update
rsAirlines.MoveNext
Loop

Steve King

Growth follows a healthy professional curiosity

 

[GoDawgs]

Don't know if this will help, but how about just creating a table that has the short airline name and the user friendly airline name. Whenever you're pulling the data out you can just link the two tables (very easy link since it's only two fields) and you won't have to convert those codes every month. Just an idea, hope it helps.

Kevin

 

[scking]

Kevin,

Shucks, You're just trying to do this the correct way. Then a query would need to be created too.

Steve King Growth follows a healthy professional curiosity

 

[GoDawgs]

Ha ha, my bad. Yeah, I didn't know if he was running a query already or what...if it's a query that he's running then he can just throw that table into the query since it won't throw anything off...then instead of pulling the [shortairlinename] field or whatever he can pull the [UserFriendlyName] field.

 

[silverfish12]

I'm using Access 2000. I'm using a subform linking to a mainform. In this subform, the fields are from a table called called tbl_prodpromo. In this table, there's a field - PromoAutoID (it's the primary key of tbl_promo). I would like to get the max(PromoAutoID) from tbl_promo & then insert this maxPromoAutoID into tbl_prodpromo.

I thought of using a query but in this subform, I can only use the table. I've defined a variable called lPromoAutoID to get the max(PromoAutoID) but I don't know how to assign it back to the PromoAutoID field.

Dim lPromoAutoID As Long

Private Sub Form_Load()
Const strConn = ODBCAdminStr

Dim objConn As ADODB.Connection
Dim rsGetPromoAutoID As ADODB.Recordset
Dim strSQL As String

Set objConn = New ADODB.Connection
Set rsGetPromoAutoID = New ADODB.Recordset

objConn.Open strConn

strSQL = "SELECT max(PromoAutoID) as PromoAutoID FROM tbl_promo"
rsGetPromoAutoID.Open strSQL, objConn
rsGetPromoAutoID.MoveFirst
lPromoAutoID = rsGetPromoAutoID.Fields("PromoAutoID&quot

End Sub


Can anybody help?

 

[Rsandoval]

Well I have received many responses.

cmmrfrds: Thanks for the definitions regarding the module. This helped.

Steve: In my situation, a query would work but I found out that it was not needed and I am using Access 2002.

Kevin: Regarding linking the tables, I do not believe I could since one would be a relational table and one would be a flat file. I believe it would be more work to make that happen.

To All: With your ideas and other posts I managed to figure it out. I created another field that I would populate with the airline name. The strange thing is that I had to use DAO. I don't know why. I tried to connect with the default but when I debugged, the database was not being accessed. If you look at my code and can figure it out, let me know. It works just fine now but like I said, I had to use DAO for some reason. Thanks to you all!!!



Public Sub RemoveAirlineCode()

Dim dbsData As DAO.Database
Dim rstData As DAO.Recordset
Dim strAirline As String
Dim sqlSelect As String
Dim strNameAirline As String
Dim strFltNum As Double


'Open the database and tables
Set dbsData = CurrentDb

sqlSelect = "SELECT tblData.*;"

'Open recordset
Set rstData = dbsData.OpenRecordset("tblData&quot

Do Until rstData.EOF
strAirline = rstData.Fields("Airline&quot.Value
strFltNum = rstData.Fields("FlightNumber&quot.Value

Select Case strAirline
Case " AA"
If strFltNum > 4999 And strFltNum < 6000 Then
strNameAirline = &quot;American Eagle&quot;
Else
strNameAirline = &quot;American Airlines&quot;
End If
Case &quot; AC&quot;
strNameAirline = &quot;Air Canada&quot;

.....blah, blah, blah..

Case &quot; YX&quot;
strNameAirline = &quot;Midwest Express&quot;
End Select
With rstData
.Edit
'rstData.Fields(&quot;AirlineName&quot.Value = strAirline
!AirlineName = strNameAirline
.Update
End With
rstData.MoveNext
Loop

rstData.Close
Set dbsData = Nothing


End Sub

 

[scking]

Just realize that with a query you would NOT EVER have to run the function you have built. Also, creating duplicate data, (e.g. a new field that holds the name of the carrier repeatedly) is contrary to the primary rules of database normalization. In this case, the design doesn't really suffer damage though.

Steve King Growth follows a healthy professional curiosity

 

[Rsandoval]

I just realized I did not thank all for your advice. The combination of your advice helped me solve my problem with much trial and error. 2 heads are better than one. Thanks