Read a file and import its data into a table (3 Viewers)

[anishkgt]

ok i've above is solved and all working well.

Earlier i had set the Basefare as string and now changed to Long. When running the app i get a "type mismatch" at

BaseFare = Trim(Mid(TextLine, 9, 12))

and the value in the variable is 0. Is it because long can't take values of 0 or is it something else. The data type in the table is also number

 

[Frothingslosh]

Type Mismatch means you're passing incompatible values to a variable, such as trying to assing a text value to an integer variable. If you've changed Basefar to Long, your listed function won't work. Trim returns a string, and you cannot pass a string to a long without converting it first.

BaseFare = CLong(Trim(Mid(TextLine, 9, 12)))

See if that works.

Also, please note that if your Trim ever returns a non-numeric value, you will still get an error from the CLong function.

 

[anishkgt]

Aite will let you know once it is done. One doubt lets say one of variable has no value or is zero, will this return error by any chance.

 

[Frothingslosh]

Zeros are fine, empty strings aren't.

 

[anishkgt]

tried that, returned error "Sub or Function not defined"

 

[namliam]

The problem lies in what does this return?
Trim(Mid(TextLine, 9, 12))
When infact it returns anything but a number it will throw an error... thus you need to prevent this from happening... Most likely it is returning an empty string ...
Preventing it is easy...

If Trim(Mid(TextLine, 9, 12)) = "" then
     BaseFare = 0
else
     BaseFare = CLong(Trim(Mid(TextLine, 9, 12)))
endif

 

[anishkgt]

Still gives an error, "Type mismatch" at

EQamt = CLng(Mid(TextLine, 39, 12))

My variable is long. The actual field is empty so the value for BaseFare picked up is 0. not sure where the problem is.

 

[anishkgt]

going nuts with this error

Dim BaseFare as Long
                                                       BaseFare = Mid(TextLine, 9, 10)

is how it is. But when the BaseFare gets a value of 0, an error returns with "Type Mismatch". tried the if statement

If Trim(Mid(TextLine, 9, 12)) = "" then
BaseFare = 0
else
BaseFare = CLong(Trim(Mid(TextLine, 9, 12)))
endif

That did not solve it either.

 

[namliam]

the funciton is clng not clong
Also from your two samples you are changing from 10 to 12 chars, one of which is probably wrong.

Very intresting this, odd even... buggy even?... I tried

Dim BaseFare As Long
Dim TextLine As String
TextLine = "abcdefgh         0alasfkhas"
BaseFare = CLng(Trim(Mid(TextLine, 9, 10)))

It does cause an error, changing the clng to cdbl does work....

 

[Frothingslosh]

My apologies about the CLong thing. That's what I get for being in a hurry.

 

[anishkgt]

no matter what i put be it cdbl or clng, it still gives me that error "Type Mismatch". :banghead:

The if statement checks for a 0 value and then the else statement is executed, shouldn't the else statement be skipped if the if statement is true, or was it the other way round.

 

[anishkgt]

How come this is not happening for other variables like the TotalFare and TicketNumbers they are long and double respectively.

 

[anishkgt]

ok figured it out. whew !

No how can i format the date which is a string to a date format more or less like convert string to date

dim D as integer , M as string, Y as integer
MirDate = 19OCT14
D = Left(MirDate, 2)
M = trim(Mid(Mirdate, 3,3))
Y = left(MirDate, 2)
MDFormated = Format(dateSerial(Y,M,D),"D-M-Y")
msgbox = ("MDFormated")

now the above code is not correct but i know that is something i need. how can i correct it ?

 

[namliam]

dateserial converts it to a proper date\?? You are unformatting it by the added format

 

[namliam]

So did you get things working ??

 

[anishkgt]

Well got laid with work. Well got things sorted out. Couldn't actually build the the complete database. But completed a simpler version. Its fine now.

 

[Frothingslosh]

There are worse things that can happen than getting laid at work....

 

[anishkgt]

Thats rite froth, could be worse lol

 

[anishkgt]

After a while

now need to change the way data is added to the table, i need to add each pax and tkt number as a new record. so my amended code is as below

If Left(TextLine, 3) = Trim("A02") Then  'First A02(PAX) line
                Pax = Trim(Mid(TextLine, 4, 30))
                RS.AddNew
                RS![Passengers] = Pax
                tkt = ALC & Trim(Mid(TextLine, 49, 10))
                RS![tkt#] = tkt
                tktDisp = ALC & Trim(Mid(TextLine, 49, 10))
            If Trim(Mid(TextLine, 49, 10)) = "" Then
                Close #1
                'Name SrcFolder & srcfile As NoTicket & srcfile
                txtMIRActivity.Value = srcfile & " " & PNR & " " & "No Ticket number " & vbNewLine & txtMIRActivity.Value
                GoTo NextFile 'Abort and move to next file
                Else
                tktNum = Trim(Mid(TextLine, 49, 10))
             End If
                passenger = tkt & " " & Pax
                Line Input #1, TextLine
                Line Input #1, TextLine
                Do While Left(TextLine, 3) = ("A02") 'Loops through following A02 (Pax) lines (if any)
                    Pax = Trim(Mid(TextLine, 4, 30))
                    tkt = ALC & Trim(Mid(TextLine, 49, 10))
                    passenger = passenger & vbNewLine & tkt & " " & Pax
                    Line Input #1, TextLine
                    Line Input #1, TextLine
                Loop
                    RS![Passengers] = Pax
                    RS![tkt#] = tkt
                    RS![FileName] = srcfile

while executing this i see only pax added to the table but there are two pax in the file. how can i add the remaining

 

[anishkgt]

well got that figured after a while.