Excel Formula to Access Form

[mlubbs]

I have an excel formula that I have been trying to recreate on my form in access without luck. This works pretty slick in excel and if I can get it to work in access I will be on the road to saving my department about 6 to 7 hours of data entry a weeek.

Here is the formula

=IF($B$4<>"",HYPERLINK(IF(ISERR(VALUE(RIGHT($B$4,1))),"s:/"&IF(ISNA(ERROR.TYPE(VALUE(LEFT($B$4,1)))=3),LEFT($B$4,1),"ALPHA")&"/"&$B$4&".pdf","s:/"&IF(ISNA(ERROR.TYPE(VALUE(LEFT($B$4,1)))=3),LEFT($B$4,1),"ALPHA")&"/"&$B$4&".pdf"),$B$4),"")

Thanks,

Mike

 

[The_Doc_Man]

I don't recall seeing you in the introduction section, so let me say Welcome to the forums (belatedly).

Can you tell us in plain language (not code) what it is that you want to do? That is a complex formula that LOOKS like you are trying to parse something related to a file name. There may be easier ways to extract whatever it is that you seek but reverse engineering Excel formulae isn't easy.

 

[Minty]

To add to @The_Doc_Man comment, some starting data and your expected results would be really helpful.
Enough examples to cover all eventualities is always a bonus.

 

[mlubbs]

Thanks guys. Sorry for the lack of an explanation. Hopefully I can explain it well enough.

In our excel worksheet you need to type in a Part Number. Then the above formula looks in the hierarchy of our AutoCAD folder. If the PN starts with a 1, it looks at the 1 folder for the entire part number. If it starts with a 2, it looks at the 2 folder for the entire part number and so on.

The final outcome is that if I have part number 123456, the formula will go to the 1 Folder within the AutoCAD file and hyperlink the drawing for PN 123456.

Please let me know if you need more info and I will provide what I can.

Thanks

Mike

 

[Minty]

You can use the left([YourField],1) to return the folder location. Something like

Sub FindFolder(sPartNumber As String)

    Dim sFolder     As String
    Dim sPath       As String
    
    sFolder = Left(sPartNumber, 1)
    
    sPath = "C:\YourAutoCadPath\GoesHere\"
    
    Shell "c:\windows\explorer.exe """ & sPath & sFolder & "\" & sPartNumber & ".pdf" & """, vbMaximizedFocus"

End Sub

 

[mlubbs]

Minty,

Thanks for the code. This is really above my level of understanding with access. I edited your code to the below. Is there anything else that should be edited?

Would I make it an On Click Event or some other type of event?

Sub FindFolder(sITMID As String)

    Dim sFolder     As String
    Dim sPath       As String
    
    sFolder = Left(sITMID, 1)
    
    sPath = "S:\"
    
    Shell "c:\windows\explorer.exe """ & sPath & sFolder & "\" & sITMID & ".pdf" & """, vbMaximizedFocus"

End Sub

 

[Minty]

Yes that would be the general idea, assuming sITMID was a value in a control on your form.
If you command button was called cmdButton1 It would be something like

Private Sub cmdButton1_Click()
        
    Dim sMyValue As String
    
    sMyValue = Me.txtTest       ' This assumes your ITMID is in a control called txtTest
    
    Call FindFolder(sMyValue)
    
End Sub
Sub FindFolder(sITMID As String)

    Dim sFolder     As String
    Dim sPath       As String
    
    sFolder = Left(sITMID, 1)
    
    sPath = "S:\"
    
    Shell "c:\windows\explorer.exe """ & sPath & sFolder & "\" & sITMID & ".pdf" & """, vbMaximizedFocus"

End Sub

Obviously, you could just lump all the code into the button click event, but you might want to use it in other places, so you could save the FindFolder Sub into a module where it could be used from anywhere not just the form where this button was.

 

[mlubbs]

I am not sure that they way this all was set up will work, but it is most likely that I just do not understand. I did not set this database up. The person that did is no longer here and doing what I am asking to be done was above them as well.

When I open the database it brings me to a screen with a tab named switch. I assume that means it is a switchboard. There are two buttons on this page. They both work the same, however one is filtered for our Mexico Plant and the other is Filtered of our local plant.

When you click on the button it asks you to put in an ITMID (aka Part Number). Then you click ok and it brings up the required info about that ITMID.

I attached some screenshots that may or may not help you to understand the setup of the database.

Thanks again!

-M