Hyperlink To Server Location?

[Micron]

You will have to substitute my made up names for your real ones and Dim your variables. I guess I should only concentrate now on what you bolded and assume you got the rest of your questions answered.

# of placeholders after the first digit: Len(Prop#)-1 --> 3141 will give you 3 (141) which we'll divide by a 1 that is followed by that many zeros: 1000.
45678 will give 4 (5678) so it won't matter how many digits are in the folder name. That divisor would be 10000. Sticking with 3141 example,
Divisor = "1" (won't ever change). Would have to be Dim'd as a variant, I think, in order to switch the data type when needed. Text to start with.
For cntr = 1 to ZeroCount (1 to 3 in this case). ZeroCount is a number equal to the number of digits after the first one in Prop#


Divisor = Divisor & "0" (on first pass, this will be 10; 2nd pass 100, 3rd pass 1000). The zeros are concatenated as text.
Next (loop number of times from 1 to the ZeroCount value)
Divisor = CInt(Divisor) - make sure 1000 is a number so you can get the MOD of the Prop#. Later, you'd convert it to a string to become part of your path.
3141 MOD Divisor (3141 MOD 1000) = 141. I guess you know the rest.

[ssanfu]

On my form, I have a different proposal number for each record. The name of the field is Prop#.

The name of the CONTROL on the form should not have the hash mark. Maybe name the control "Prop"

Using Micron's pseudo code, I came up with this:

Private Sub Prop_DblClick(Cancel As Integer)
    Dim vProp As Long
    Dim LB As Long
    Dim UB As Long
    
    Dim ZeroReq As Integer
    Dim DivNum As Long
    Dim NumZero, i
    Dim PropMod As Long
    Dim HyperPath As String

    vProp = Me.Prop   '<< Me.Prop is the control name, not the field name ("Prop#")

    ZeroReq = Len(vProp) - 1
    NumZero = 1

    For i = 1 To ZeroReq
        NumZero = NumZero & "0"
    Next
    DivNum = CInt(NumZero)

    PropMod = vProp Mod DivNum
    LB = vProp - PropMod
    UB = LB + DivNum - 1

    HyperPath = "\\server\folder1\folder2\" & LB & "-" & UB & "\" & vProp

    MsgBox HyperPath  'you know what to do from here

End Sub

[Micron]

@ssanfu: looks excellent. I see you did not overlook the fact that the counter should be a variant, whereas I did. Looks very much like what I had ready in Notepad, but didn't post due to the strong message I got. The only real difference is not testing that Prop# contains no non numeric characters - IsNumeric(Prop#). And yeah, I already posted about the hash tag character in post #4.
Thanks.

[ssanfu]

@ Micron
Re: not testing that Prop#. I saw

2) They do not exist...everything is 4 digits.

and took it to mean there would be nothing entered below 1000 and the field type would be numeric. But I guess "0123" would be 4 digits... and necessitate the field type being TEXT. So the code would need to be changed to account for the field "Prop#" being TEXT.

[Micron]

But is was said that they might be 5 in the future.
Might not be as bad as you think. IsNumeric("0123") is True and 123 Mod 1000 is still 123. So the LB and UB values would be 0000 and 0999 I think. However, I did have your vProp as a variant, not Long as I felt it necessary to coerce the data types as code progressed (i.e. sometimes a number, sometimes text - especially for the link string). I didn't mention that because you did a great job, and frankly, I had not thought it through that much.

[shoelesscraig]

Guys, first of all...thanks for the help...

But, second of all...I think all of that code is irrelevant. Now that I think about it, I don't need to calculate digits, etc. Each group of folders will always have 100 folders in it.

For example, right now we are using 4800-4899. Next will be 4900-4999. Then 5000-5099 and so on. Even when we get to 5 digits, it will be 10000-10099. So I will always "MOD 100" no matter what.

The code I'm using is:


Dim strPropUpper As String, strPropLower As String, strPropMod As String


strPropMod = Me.ProposalNum Mod 100
strPropLower = Me.ProposalNum - strPropMod
strPropUpper = strPropLower + 99


FollowHyperlink ("\\server\folder1\folder2\Proposals" & strPropLower & " " & "-" & " " & strPropUpper & "" & Me.ProposalNum)



This is working flawlessly ever time. I also changed everything to work with our Projects folder system, which is 6 digits long...same code works perfectly. (And yes, if you are wondering...since all of you experts are telling me NOT to use the # symbol, I went back and changed it...).

[ssanfu]

Well, OK then.Glad you got it to work. Sometimes posting a question helps define the question and a solution.

One note:
You have this

FollowHyperlink ("\\server\folder1\folder2\Proposals" & strPropLower  & " " & "-" & " " & strPropUpper & "" &  Me.ProposalNum)

Since the text in red IS text, you can simplify by using

FollowHyperlink ("\\server\folder1\folder2\Proposals" & strPropLower  & " - " & strPropUpper & "" &  Me.ProposalNum)

[shoelesscraig]

Well, OK then.Glad you got it to work. Sometimes posting a question helps define the question and a solution.

One note:
You have this

FollowHyperlink ("\\server\folder1\folder2\Proposals" & strPropLower  & " " & "-" & " " & strPropUpper & "" &  Me.ProposalNum)

Since the text in red IS text, you can simplify by using

FollowHyperlink ("\\server\folder1\folder2\Proposals" & strPropLower  & " - " & strPropUpper & "" &  Me.ProposalNum)

I haven't tried what you said, but I'm thinking that will NOT work. The folder names for my range have a space between the numbers and the dash in the middle. For example, it is "3100 - 3199"....NOT "3100-3199". If the space isn't there, it won't work since a folder named "3100-3199" does not exist, but "3100 - 3199" does.

I know I left that off of my original posts, but I did that on purpose to keep things simpler on this forum. I knew how to add the spaces back in.

[ssanfu]

You are concatenating a space, a dash and a space.

This

& " " & "-" & " " &

is the same as this

& " - " &

Notice there is a space before and after the dash.



If you are concatenating a last name field and a first name field (like "Duck, Daffy"), to get lastname, comma, space, firstname, you would use

FullName: LastName & ", " & FirstName

not

FullName: LastName & "," & " " & FirstName

This

& " " & "-" & " " &

will work, it is just more typing and takes a little longer to read....



Anyway, good luck with your project.....

[shoelesscraig]

Ahhhh...I totally understand! Sorry. Didn't see the spaces!

Thanks for the help!