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
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