INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query inserts a character but only in certain instances

Query inserts a character but only in certain instances

(OP)
A Query in a church database has the following column.

CODE

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

RE: Query inserts a character but only in certain instances

(OP)
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.

RE: Query inserts a character but only in certain instances

Is HouseNbr field type TEXT or NUMBER?

RE: Query inserts a character but only in certain instances

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

RE: Query inserts a character but only in certain instances

So you may have a SPACE character.

Use TRIM()

RE: Query inserts a character but only in certain instances

Actually i'd do

IIF(Trim(AptNbr)='',

RE: Query inserts a character but only in certain instances

(OP)
I wonder whether or not I'm understanding you correctly.

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

CODE

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

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

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

RE: Query inserts a character but only in certain instances

Use QUOTES instead of TICS in the expression.

RE: Query inserts a character but only in certain instances

(OP)
Well, by golly, she no work.

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

CODE -->

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.

RE: Query inserts a character but only in certain instances

(OP)
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.

RE: Query inserts a character but only in certain instances

Try IsNull(Trim([AptNum]))

RE: Query inserts a character but only in certain instances

(OP)
Thus, the expression would be:

CODE -->

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

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

RE: Query inserts a character but only in certain instances

(OP)
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!

RE: Query inserts a character but only in certain instances

(OP)
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.)

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") 

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.

RE: Query inserts a character but only in certain instances

(OP)
This gets weird.

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

RE: Query inserts a character but only in certain instances

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

CODE -->

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

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

RE: Query inserts a character but only in certain instances

great!

RE: Query inserts a character but only in certain instances

(OP)
Thanks, PHV.

I appreciate your assistance.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close