Darn plus signs in calculated controls/fields...

[EternalMyrtle]

Why can't I ever get these to work properly? I want to use them to ignore null fields so that I won't get the punctuation but it isn't working.

Here is what I have but the commas still show up:

=IIf([Status]=1,(+[MailBoxOrPOBox]) & (+", "+([CompanyLocID].[Column](2))) & (+" ,"+([CompanyLocID].[Column](3))),"")

is there a better way than trying to use these + signs??

Thank you!!

 

[pbaldy]

You concatenate the punctuation and the potentially Null field inside parentheses, like

="fixed text" & (", " + PotentiallyNullField)

 

[EternalMyrtle]

Could have sworn I tried that but will try again when I return to the office tomorrow. Thanks!

 

[EternalMyrtle]

It's still doing it

Put in this: =IIf([Status]=1,(+[MailBoxOrPOBox]) & (", "+[CompanyLocID].[Column](2)) & (", "+[CompanyLocID].[Column](3)),"")

and it is showing up like this:

, street address,

(with the first field and the last field being empty)

Why

 

[pr2-eugin]

I have the same problem. I just thought to myself, screw it and wrote a function. Not the best way, but does what I want ! LOL

Public Function linearAddress(line1 As Variant, line2 As Variant, line3 As Variant, _
                              Optional line4 As Variant) As String
[COLOR=Green]'**************************************************************
'Code Courtesy of
'      Paul Eugin        March 2013
'   The function JOINS the single 'Atomic' address fields
'               into one Single LINE address.
'       This formatting is used for Mail Merge fields.
'**************************************************************[/COLOR]
    Dim retStr As String
    If Not IsNull(line1) Then retStr = retStr & line1 & ", "
    If Not IsNull(line2) Then retStr = retStr & line2 & ", "
    If Not IsNull(line3) Then retStr = retStr & line3 & ", "
    If Not IsNull(line4) Then retStr = retStr & line4 & ", "

    If retStr = vbNullString Then Exit Function
    linearAddress = Left(retStr, Len(retStr) - 2)
End Function

With the above code.

? linearAddress(Null, "Westby Road", Null, Null)
Westby Road
? linearAddress(Null, "Westby Road", Null, "BH5 1HA")
Westby Road, BH5 1HA
? linearAddress(12, Null, Null)
12

 

[RainLover]

Is Column 2 really 2 or should it be 1.

Counting starts at 0.

 

[pr2-eugin]

Counting starts at 0.

I think Column counting starts at 0 in VBA and on forms/query it starts with 1, me thinks !

 

[RainLover]

I think Column counting starts at 0 in VBA and on forms/query it starts with 1, me thinks !

You may very well be correct. When referring to the Bound Column of a Combo we usually select 1 not 0.

Thank you.

 

[pbaldy]

The column is a property, and doesn't change based on where you're referring to it from. Still zero based on a form.

 

[RainLover]

Paul

For my clarification.

A Combo on a form has the property of "Column Count" In this situation there is no Zero. It starts at One.

Why do we differ.

 

[EternalMyrtle]

Anywhere you are referring to the columns in the form properties it starts at 1 (e.g. bound column or column count).

Anywhere else it starts with 0 (e.g. in code and in calculated controls on a form).

 

[pbaldy]

Paul

For my clarification.

A Combo on a form has the property of "Column Count" In this situation there is no Zero. It starts at One.

Why do we differ.

I'm referring to the Column property itself and saying that whether in VBA code or on a form, the first column will always be zero here:

ComboName.Column(0)

You're referring to a different property, that does indeed start at 1.

 

[EternalMyrtle]

You use single quotes with the plus signs not double: http://www.databasedev.co.uk/query_concatenation.html

So it should have been:

=IIf([Status]=1,(+[MailBoxOrPOBox]) & (', '+([CompanyLocID].[Column](2))) & (' ,'+([CompanyLocID].[Column](3))),"")

So happy to mark this as SOLVED

 

[pbaldy]

I would be surprised if the single/double quotes was an issue. In a brief test, it didn't matter which I used. That site used singles, but I don't see it saying that they must be used. In any case, glad you have it working.

 

[EternalMyrtle]

In a brief test, it didn't matter which I used.

It doesn't seem to matter in queries but in calculated fields it does or at least in did in this case.

 

[EternalMyrtle]

Meant calculated controls, not fields...

 

[pbaldy]

I wasn't saying you were wrong, just that I was surprised. Perhaps it depends on version as I had tested that scenario and it worked fine in a 2007 form:

=[Field1] & (", "+[Field2])

 

[EternalMyrtle]

I wasn't saying you were wrong

Don't worry, I didn't take it that way. Hmm, that is odd since I am also using 2007. Sometimes it seems to matter and sometimes it doesn't. I don't get it. Will chalk it up to a quirk.

Just happy I got it working in this instance