Strange appearance of Hyphen character 2

[THWatson]

Following is code in a form that is used to produce a new record in the event that one person of two in a household dies, or they separate.

What the code does is take all of the information regarding name, address etc. and create a new record.

Now, say you query the table and, using an IIf function, want to put together an AptNbr (if it exists) and a HouseNbr and Street,
Thus, you should end up with
922 - 14 Olive ST (if there is an AptNbr)
or
14 Olive ST (if there is no AptNbr)

For whatever reason the result is that a hyphen is thrown in, so you get - 14 Olive ST

This only happens when a new record was created using the process below. The baffling thing is that no extra characters show in the table, and when you query the HouseNbr it appears as it should. It's only when you attempt to use the IIF that you run into this problem.

Here's the code.

NewRecordProcess2:
Call MsgBox(Me.FirstName2 & " will be removed from here and a new record created." _
                  & vbCrLf & "" _
                  & vbCrLf & "Envelope #s assigned exclusively to " & FirstName2 & " will be moved to the new record" _
                  & vbCrLf & "                  and an End Date of Yesterday's date applied." _
                  & vbCrLf & "" _
                  & vbCrLf & "You will then be placed in the Remarks section of the new record" _
                  & vbCrLf & "   in case you wish to change or add to the remarks entered." _
                  , vbExclamation, "Remove process")
DoCmd.SetWarnings False
    strTemp = Me.FirstName2
    strTemp2 = IIf(IsNull(Me.LastName2), Me.LastName, Me.LastName2)
    strTemp3 = Nz(Me.HouseNbr, "?")
    strTemp19 = Nz(Me.AptNbr, "")
    strTemp4 = Nz(Me.Street, "?")
    strTemp5 = Nz(Me.City, "?")
    strTemp6 = Nz(Me.Province, "?")
    strTemp7 = Nz(Me.Code, "?")
    strTemp8 = Nz(Me.HomePhone, "?")
    strTemp9 = Nz(Me.BusPhone, "?")
    strTemp10 = Nz(Me.Person2Status, "A")
    strTemp11 = IIf(IsNull(Me.Person2DateOfBirth), "", Me.Person2DateOfBirth)
    strTemp12 = IIf(IsNull(Me.Person2Baptism), "", Me.Person2Baptism)
    strTemp13 = IIf(IsNull(Me.Person2Confirmation), "", Me.Person2Confirmation)
    strTemp14 = Me.RemovedHow
    strTemp15 = Me.RemovedDate
    strTemp16 = Me.FirstName
    strTemp17 = Me.LastName
    strTemp18 = Nz(Me.Address2, "?")
    strTemp20 = Nz(Me.RetHomeID)

' Keep track of location in database and Old Record ID
varBookmark = Me.Bookmark
lngOldUniqueID = Me.UniqueID

' Create new record
DoCmd.GoToRecord , , acNewRec
Me.LastName = strTemp2
Me.FirstName = strTemp
Me.AptNbr = strTemp19
Me.HouseNbr = strTemp3
Me.Street = strTemp4
Me.Address2 = strTemp18
Me.City = strTemp5
Me.Province = strTemp6
Me.Code = strTemp7
Me.HomePhone = strTemp8
Me.BusPhone = strTemp9
Me.Person1Status = strTemp10
Me.Person1DateOfBirth = strTemp11
Me.Person1Baptism = strTemp12
Me.Person1Confirmation = strTemp13
Me.chkPerson1Removed = True
Me.RemovedHow = strTemp14
Me.RemovedDate = strTemp15
Me.RetHomeID = strTemp20
Me.Remarks = "Removed from record for " & strTemp16 & " " & strTemp17 & " on " & Format(Date, "mmmm dd, yyyy")

Do you spot anything in there that should create the issue I am experiencing?

The AptNbr field in the table is set to Text, because the AptNbr could be something such as 5A rather than a number.
Also the "Allow zero length" property is set to Yes. I have tried changing it to No, but it made no difference.

 

[THWatson]

This gets weird.

No matter what character I put in the IIf, that character, or even a space occurs when the query runs.

 

[THWatson]

Turns out I can fix it in a query by using a column

IIf(IsNull([AptNbr]) Or [AptNbr]="",[HouseNbr] & " " & [Street],[AptNbr] & " - " & [HouseNbr] & " " & [Street])

Still don't understand why this is necessary, but as long as it works I'm good to go.

 

[strongm]

Given that your original post doesn't seem to contain the line that is supposed to concat AptNbr (if it exists) and a HouseNbr and Street, it is difficult to comment.

 

[THWatson]

Hi strongm

Initially I was dealing with that over in Queries.

Using the following code

IIf(IsNull([AptNbr]),[HouseNbr] & " " & [Street],[AptNbr] & " - " & [HouseNbr] & " " & [Street])

Note that the hyphen character ( - ), or dash if you prefer, is only supposed to kick in when there is actually an AptNbr, not in other cases.
It worked when there was an AptNbr. For example
902 - 1799 Case CR

But I was getting strange results when there was no AptNbr...and when it was a new record that had been created using the process above...and only then, not with a normal brand new record...I was expecting to get the following:
92 Walnut ST (when no AprNbr)
but instead would get
- 92 Walnut ST

By experimenting I found that if I changed the " - " piece in the code to something else ( -- ) or ( ** ) those characters would show so you would get
-- Walnut ST
** 92 Walnut ST

But then I found out that I could use the code posted above and it works.

I racked my brain trying to figure out if there was something in the code that created the new record, by hiving details off from an original one, that caused the problem. That's why I posted.

Thanks. Sorry I left things obscure.

 

[strongm]

A Null record and an empty string are not the same thing

 

[THWatson]

I understand that.

What I don't understand is why the process places a null value, or an empty string, whichever it is, in the AptNbr field, and only in that field...and creating a new record from scratch doesn't do that.

 

[MajP]

or an empty string, whichever it is, in the AptNbr field, and only in that field...and creating a new record from scratch doesn't do that

Not sure why you do not understand that. Clearly your code puts in a empty string. You wrote it to do exactly that.
'
strTemp19 = Nz(Me.AptNbr, "")
...
Me.AptNbr = strTemp19

 

[THWatson]

Okay, I hear what you are saying.

The question is: Is there a fix for the code so that it doesn't do that?

 

[THWatson]

Is this the offending line?

[b]strTemp19 = Nz(Me.AptNbr, "")[/b]

It's been so long ago since I created this code that I'm not clear why I put it that way in the first place.
Perhaps I did it because only about 1/4 of the existing records in this database for my church live in other than single dwellings, so it grabs the AptNbr if there is one and writes it to the new record...and, at least it was intended, to write nothing if no AptNbr existed.

On the other hand, a little testing in a query indicates that changing the string to the following makes no difference.

[b]strTemp19 = Nz(Me.AptNbr)[/b]

 

[strongm]

You might want to carefully read the Nz function documentation, which states that it

return(s) zero, a zero-length string (""), or another specified value

Assuming strTemp19 is DIMmed as a string (as it certainly appears to be), you therefore get a zero-length string returned no matter whether you use

[tt]strTemp19 = Nz(Me.AptNbr, "")[/tt]

or

[tt]strTemp19 = Nz(Me.AptNbr)[/tt]


There are several possible fixes, for example:

[blue]Dim strTemp19 as Variant
...
strTemp19 = Nz(Me.AptNbr, Null)[/blue]

At which point your original IIF should work as you expect (you may, of course, wish to rename the variable as well)

 

[THWatson]

Thanks, I'll give it a whirl. I appreciate your kind assistance.

 

[MajP]

Here is a little concatenation trick that may work for you. In this case it will not because you forced the empty string. But in general if you do not enter a value into a field it is a null. You can use the + character for concatenation in a SQL query. I will demonstrate it in a vb procedure.

Public Sub ConcatWithPlus()
  'null + String = null
  'null & string = string
  Dim First As String
  Dim last As String
  Dim middle As Variant
  
  First = "John"
  last = "Smith"
  middle = "L"
  
  Debug.Print First & " " & middle & ". " & last
  Debug.Print First + " " + middle + ". " + last
  
  middle = Null
  
  Debug.Print First & " " & middle & ". " & last
  Debug.Print First & " " & middle + "." & last
End Sub

output

' When the middle name is present
John L. Smith
John L. Smith

' When there is no middle name
John . Smith
John Smith

So in a query you could do something like

Select First & " " & middle + "." & last as FullName....
It will correctly produce
John L. Smith
or
John Smith without a hanging period

 

[THWatson]

Thanks! A handy thing for my took kit.