Adding a 0 to single digit imports

[TheWolfster]

Hello,

New to the forums, and new to access! (I'm actually a hardware guy who knows very little about coding). I'm using a few VBA books (shocking I know) and guides on the internet to get by, but I don't know enough to do the following.

I've been working on modifying an existing database that allows my lab to upload files (.dat, really just .txt) into access and sorts their information appropriately.



.dat files contain header information that sorts the upload. In this case

OFCC#x# -- -- AAA

OFCC7x1 95 00 TMS

What I'd like to, is on import, add a 0 before the 7, and ignore the 95 00 TMS

The orginal database imports files with EMP###x# -- -- ### AAA

This is the section of code, could I add it here?

Private Function Get_EMP_ID_From_Header(Header As String) As Integer
Dim EMP_location As Integer
Dim EMP_ID As Integer

EMP_location = InStr(1, UCase(Header), "LFCC" Or "OFCC" Or "LFCP" Or "OFCP")

EMP_ID = Mid(Header, EMP_location + 4, 1)

Get_EMP_ID_From_Header = EMP_ID

End Function
______

Private Function Get_File_From_Header(Header As String) As Integer
'This function returns the "file" number from file

Dim x_location As Integer
Dim first_space_after_x_location As Integer
Dim FileNum As Variant


x_location = InStr(1, UCase(Header), "X")
'first_space_after_x_location = InStr(x_location, Header, " ")

FileNum = Mid(Header, x_location + 1, 1)

If (FileNum > 0) And (FileNum < 6) Then
    Get_File_From_Header = FileNum
Else
    Get_File_From_Header = -1
End If

End Function

Sorry if I'm not being very articulate on the matter seeing as I have no idea what I'm doing.

[llkhoutx]

Is EmpID always in character 5? if so

Emp_ID=Cint(Mid(Header,4,1)) 

EMP_location = InStr(1, UCase(Header), "LFCC" Or "OFCC" Or "LFCP" Or "OFCP") 

is erroneous. You can't have "OR'ed" arguments. VBA isn't very smart.

Try

Private Function Get_EMP_ID_From_Header(Header As String) As Integer
Dim EMP_location As Integer
Dim EMP_ID As Integer

'this avoids complex nested if's. It's very, very fast
If InStr(1, Header, "LFCC") > 0 Then EMP_location = InStr(1, Header, "LFCC")
If InStr(1, Header, "OFCC") > 0 Then EMP_location = InStr(1, Header, "OFCC")
If InStr(1, Header, "LFCP") > 0 Then EMP_location = InStr(1, Header, "LFCP")
If InStr(1, Header, "OFCP") > 0 Then EMP_location = InStr(1, Header, "OFCP")

EMP_ID = Mid(Header, EMP_location + 4, 1)

Get_EMP_ID_From_Header = EMP_ID

End Function 

[llkhoutx]

Pardon my not answering

What I'd like to, is on import, add a 0 before the 7, and ignore the 95  00 TMS 

I'm not clear what the result would be for "OFCC7x1 95 00 TMS".

I suppose what you getting at is that that additional digit is needed for additional employees.

Strings are appended by merely addng the additional string, e.g.
If you want to precede a "7" wwith a "0", merely "0" & "7" results in "07". This is called concantenation.

Of course integers can't be concantenated, but they can be displayed with leadin zeroes by using the "Format" function.
format(7,"00") returns 07 (for display), the vae remains an integer 7.

[TheWolfster]

Ah, thank you very much.

I have more questions, if you'd be so kind as to help me.

So would Format(EmpID, "000") work? If so, where exactly would I put it, simply before the start of the If commands you mentioned above?

There is a bit of code that states

ID = Get_EMP_ID_From_Header(HeaderText)

If Not ((ID > 0) And (ID < 1000)) Then
    'There was a problem getting the file number, ie
    'it was not between 1 and 10.  Report this to the user:
    MsgBox "Oops!  The ID number could not be obtained for this file : " & vbCrLf & vbCrLf & FileName & vbCrLf & vbCrLf & "This file will not be uploaded!" & vbCrLf & "Fix the file header information and try again!", vbExclamation, "Physio Upload"
    Kill (strProcessedFile)
    UploadPhysioFile = 0
    Exit Function
End If

Secondly, I'd like to add a field that identifies the type of file either as LFCC, OFCC, LFCP, or OFCP. All files that are imported have one of these headers.

I've also noticed that some headers are OFCC#x# 95 00 TMS and some are OFCC#x# 95 00 SJL. Any file with the SJL text in the header fail to import. 95 00 can be ignored, and the TMS or SJL are just the initials of the experimenter who was running that session. I've searched the code and haven't found anything that specifies either. Anyway to negate that/prevent Access from even looking at that part of the header?

That's all I need help with.

Sidenote: I actually work in a psychophysiology lab, and all this time I thought EMP meant Empathy (one of the studies we run), which kept making me think all of this code was unique! But it just stands for Employee (which you clarified). With that in mind I was able to figure out/look up a lot of code and solve a couple problems. Ha!

[llkhoutx]

Format(EmpID, "000" ) would produce 3 digits.

Format(EmpID, "00" ) would produce 2 digits.

Either would be used to display data or in building a new string containing the added digits.

Example

dim iEE as integer
iEE="OFCC7x1 95 00 TMS"
iEE=left(iEE,6) & format(mid(iEE,7,1),"000") & right(iEE,Len(iEE,Len(iEE)-7)) 

would result in

OFCC7x001 95 00 TMS 

I'm not sure where it goes.

Left("OFCC7x001 95 00 TMS",4) gives you "OFCC" or what ever is stored in the first 4 characers. 

Hopes this helps you. Our thread is convoluted. Maybe it's time to start a new thread.

[TheWolfster]

Seems good. I think I will start a new thread.

[carlmdobbs]

Hello. You don't just want the OFCC7x1 95 00 TMS to display with a "0" in front of it. You want to change the data, right, so it stores the 0.

You can change the text like this:
Function fnChangeNumber(strNumber as string)
Dim strL as string, iLen as integer, strR as string, i as integer
lLen = Len(strNumber)
i = instr(StrNumber, i)
strL = left(strNumber,4)
strR = Right(strNumber, iLen-i)

fnChangeNumber = strL & "0" & strR
End Function

This contatenates the left four letters, ands a 0 then adds the remainder of the letters.

Is this too complex for you? Write me at carlmdobbs@gmail.com and I'll help you further.

If you go to my website dobbscomputerinstitute.web.officelive.com you can sign up for regular MS Access tips emailed to you.