How eliminate unwanted character from the field in continuous form

[eugzl]

Hi All.
The Record Source of continuous form is query where the Location field has concatenation like:

Location: IIf([qLocations].[Facility]="ABC",[qLocations].[Facility] & " " & [qLocations].[Building] & " " & [qLocations].[Wing] & [qLocations].[Floor] & "-" & [qLocations].[Room],[qLocations].[Facility] & " " & [qLocations].[Building] & [qLocations].[Floor] & "-" & [qLocations].[Room])

Of course, if the set of fields to merge is empty, only the hyphen (-) character will be displayed in the "Location" field. Is it possible to use VBA on the "Location" field to set it to blank if it has a value of the hyphen character? If yes. How to do it?
Thanks

 

[Gasman]

Test if they are Null ?

 

[bastanu]

Wrap them in brackets and use "+" instead of "&" to concatenate; using Null+ Anything=Null so the empty ones will be eliminated.

Concatenating null to string using + results in null

I have the following SQL Query in Access: SELECT ID, CurrencyName + ' (' + CurrencySymbol + ')' AS [Currency], CurrencyLocation, CurrencySymbol FROM Currencies ORDER BY SortOrder What I noticed i...

stackoverflow.com

Microsoft Access Tip: Simplify String Concatenations with Nulls and Zero Length Strings

Simplify String Concatenations with Nulls by using + vs. &

www.fmsinc.com

Cheers,

 

[SHANEMAC51]

если набор разделяемых полей пуст, в поле «Расположение» будет отображаться только дефис (-).

Location: IIf([qLocations].[Facility]="ABC",
[qLocations].[Facility]
& (" " + [qLocations].[Building])
& (" " + [qLocations].[Wing])
& [qLocations].[Floor]
& ("-" + [qLocations].[Room]),

[qLocations].[Facility]
& (" " + [qLocations].[Building])
& [qLocations].[Floor]
& ("-" + [qLocations].[Room]))

 

[eugzl]

I solved the problem using Switch function in the query. Thanks everyone.

 

[Pat Hartman]

Too bad you didn't try to understand the link. Although the & is the standard concatenation character in VBA, the + can also concatenate in some situations but has different properties. Understanding how + is different is extremely useful and gives you control over how the string is rendered when you use it correctly. Even though you found an alternate solution, I suggest that you actually try to understand the reason that + can sometimes be very helpful. DO NOT just switch to always using the +. That will lead to undesirable results. Use the & EXCEPT when you need the special property of the + when working with fields that can potentially be null.

FullName = FirstName & (" " + MiddleName) & " " & LastName

This gives you one space instead of two if MiddleName is null. Of course, if you have left the AllowZeroLentghStrings property set to the default of Yes, your "empty" fields probably have ZLS rather than null so this technique won't work. You would have convert the ZLS to null

FullName = FirstName & (" " + IIf(MiddleName = "", Null, MiddleName) & " " & LastName

I always change the AllowZeroLengthStrings property to No because I don't ever want to have to deal with both ZLS and Null. I want to know if the field looks "empty" it is null. Number and dates are not strings and so the only "empty" option for them is null.

Just FYI if any of the fields is Null, this expression will return Null.

FullName = FirstName + " " + MiddleName + " " + LastName

That is why you don't want to ALWAYS use the + as your concatenation operator.

And this expression returns an extra space when MiddleName is null which is of course not as jarring as a forlorn dash.

FullName = FirstName & " " & MiddleName & " " & LastName

 

[eugzl]

Too bad you didn't try to understand the link. Although the & is the standard concatenation character in VBA, the + can also concatenate in some situations but has different properties. Understanding how + is different is extremely useful and gives you control over how the string is rendered when you use it correctly. Even though you found an alternate solution, I suggest that you actually try to understand the reason that + can sometimes be very helpful. DO NOT just switch to always using the +. That will lead to undesirable results. Use the & EXCEPT when you need the special property of the + when working with fields that can potentially be null.

FullName = FirstName & (" " + MiddleName) & " " & LastName

This gives you one space instead of two if MiddleName is null. Of course, if you have left the AllowZeroLentghStrings property set to the default of Yes, your "empty" fields probably have ZLS rather than null so this technique won't work. You would have convert the ZLS to null

FullName = FirstName & (" " + IIf(MiddleName = "", Null, MiddleName) & " " & LastName

I always change the AllowZeroLengthStrings property to No because I don't ever want to have to deal with both ZLS and Null. I want to know if the field looks "empty" it is null. Number and dates are not strings and so the only "empty" option for them is null.

Just FYI if any of the fields is Null, this expression will return Null.

FullName = FirstName + " " + MiddleName + " " + LastName

That is why you don't want to ALWAYS use the + as your concatenation operator.

And this expression returns an extra space when MiddleName is null which is of course not as jarring as a forlorn dash.

FullName = FirstName & " " & MiddleName & " " & LastName

Since I'm just starting to develop my first projects in Access, your post is very useful to me. I have read both, but this information is very interesting.
Thanks a lot.

 

[Pat Hartman]

You're welcome. Have fun on your journey