VBA Code

[kitty77]

What is the best way to state this?

If [Msampleid1] = "" Then [Mlink2] = "#" & "\customers\" & LCase([Msampleid]) & ".pdf" Else [Mlink2] = "#" & "\customers\" & LCase([Msampleid]) & "-" & LCase([Msampleid1]) & ".pdf"

Thanks.

 

[June7]

Does the code work properly? Why would Msampleid have an empty string? If you want to build an Access hyperlink string then you need two # characters, one at each end of the link.

 

[kitty77]

No, it seems to go by the first part and give me the Else. Even though Mssampleid1 is null or empty.

 

[kitty77]

Seems like the null or empty or "" is not working or proper.

 

[June7]

I don't allow empty strings in fields or controls. However, you can test for Null and empty string with:

If [Msampleid1] & "" = "" Then
    [Mlink2] = "#\customers\" & LCase([Msampleid]) & ".pdf#"
Else
    [Mlink2] = "#\customers\" & LCase([Msampleid]) & "-" & LCase([Msampleid1]) & ".pdf#"
End If

However, there is no need to save this calculated value into table field. Just calculate when needed. Could do it in query or textbox and no VBA would be needed. Consider:

"#\customers\" & LCase([Msampleid]) & "-" + LCase([Msampleid1]) & ".pdf#"

Or could use that simpler expression in VBA.

Note use of + for concatenation. This is because arithmetic with Null returns Null so it will do the same with text concatenation whereas & concatenation with Null returns the text.

 

[plog]

Logically, everything that's going to go into your variable no matter what, shouldn't be duplicated in parts of the if/Else:

[Mlink]="#\customers\" & LCase[Msampleid])
If NZ([Msampleid1], "") <> "" Then [Mlink2] = [Mlink] &  "-" & LCase([Msampleid1])
[MLink] = [Mlink] & ".pdf"

 

[CJ_London]

Even though Mssampleid1 is null or empty.

empty is not a relevant term for a variable in a database, it only refers to cells in excel

this

if [Msampleid1] = ""

is only testing for a zls (zero length string), it is not testing for null

to test for null or zls you would use

if nz( [Msampleid1,"")=""

I don't see the benefit of using lowercase, since access and windows are not case sensitive but you could restate as


[Mlink2] = "#" & "\customers\" & LCase([Msampleid]) & iif(nz( [Msampleid1,"")<>"", "-" & LCase([Msampleid1]),"") & ".pdf"