Query inserts a character but only in certain instances

[THWatson]

A Query in a church database has the following column.

AddressCheck: IIf(IsNull([AptNbr]),"",[AptNbr] & " - ") & [tblTrinity].[HouseNbr] & " "+[tblTrinity].[Street] & IIf(IsNull([Address2]+[tblTrinity].[Street]),"",Chr(13)+Chr(10)) & [Address2]

This should, for example, produce the following results is there is no apartment in the address:
14 Nelson ST

or, this result if there is an apartment:
1002 - 14 Nelson ST

However, on the odd occasion what is produced when there is no apartment number is this:
- 14 Nelson ST

Any idea as to what is causing the problem?

Thanks.

Tom

 

[THWatson]

I should have added that the hyphen character does not appear in the table tblTrinity, nor does it appear in the HouseNbr column in the query.

 

[SkipVought]

Is HouseNbr field type TEXT or NUMBER?

 

[THWatson]

It's Text, because it's possible to have an address such as 35A

 

[SkipVought]

So you may have a SPACE character.

Use TRIM()

 

[SkipVought]

Actually i'd do

IIF(Trim(AptNbr)='',

 

[THWatson]

I wonder whether or not I'm understanding you correctly.

Do you mean the first part of the expression would then be:

IIf(Trim([AptNbr])='',"",[AptNbr] & " - ")

That makes it worse. It puts a hyphen in everything.

So maybe I'm having a "thick" moment.

 

[SkipVought]

Use QUOTES instead of TICS in the expression.

 

[THWatson]

Well, by golly, she no work.

You mean, I gather, the first part of the expression to be the following:

IIf(Trim([AptNbr])="","",[AptNbr] & " - ")

It works fine if there is a value in AptNbr. Then it shows as
115 - 1400 Woodbine RD E

Otherwise, it puts in a hyphen. So the street address ends up being like so...
- 197 Jane ST

The hyphen is one stubborn little fella.

 

[THWatson]

The baffling thing is there is no empty character, space or anything else, in the table from which the query runs.

It only shows up when the query is run.

 

[SkipVought]

Try IsNull(Trim([AptNum]))

 

[THWatson]

Thus, the expression would be:

IIf(IsNull(Trim([AptNbr])),"",[AptNbr] & " - ")

Works fine if there is an AptNbr value. If there isn't then you get a hyphen.

 

[THWatson]

The interesting, and also baffling thing, is that this only occurs under certain circumstances. This is when it occurs.

Say there are two people, Sally and Fred Jones. They live at 12 Walnut Drive.
Sally dies. Then I have a process that flags Sally as deceased, and moves Fred and creates a new record.

New record for Fred is created just fine, and nothing untoward shows in the table.
But then if I run a query to make a Directory, that's when this hyphen shows up.

I have to paw back through a few more things. So far I can't see anything in the code that creates the record that would cause this, and there's no value in the AptNbr in the table.

It's just enough, though, to make me lose some more of the hair I still have!

 

[THWatson]

Here's the code in the form that creates a new record when, for example, one of the people is removed (by death, divorce, etc.)

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
[b]Me.AptNbr = strTemp19[/b]
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")

I have put the AptNbr field in bold print as it seems to be the only one affected. But I can't see anything there that would cause a problem. It doesn't write a hyphen into the table. That hyphen just shows up when you query the table for a record such as this.

 

[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]

I think I found a work-around. The following construction in a query column.

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

Still don't understand why the appearance of non-existing characters, but this works.

 

[SkipVought]

great!

 

[PHV]

You may replace this:
IsNull([AptNbr]) Or [AptNbr]=""
with this:
Trim([AptNbr] & "")=""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

Thanks, PHV.

I appreciate your assistance.