Select data to print 2

[THWatson]

Using Access 2003 (2000 format)

A church database. On a form there is a list box that includes the names of 20 Retirement Homes in which members might live.

The secretary can click on any one in the list and print a report, for the Ministers, of those people from the church who live in that specific Retirement Homes.

What she would like to be able to do is click one command button and print a report for all Retirement Homes at once. At the moment, this can't happen.

Here is the SQL code for the list box.

SELECT tblRetirementHome.RetHomeID, tblRetirementHome.RetirementHome, [StreetNbr] & " " & [Street] & ", " & [City] AS Address, tblRetirementHome.StreetNbr, tblRetirementHome.Street, tblRetirementHome.City
FROM tblRetirementHome
ORDER BY tblRetirementHome.RetirementHome;

One of the difficulties is that there are a few Homes in the list in which none of the church members live.

The code behind the current button to Preview All is

Dim stDocName As String
    stDocName = "rptAddressSelect(AllAddresses)"
    DoCmd.OpenReport stDocName, acPreview

The report "rptAddressSelect(AllAddresses) is based on a query with the following SQL

SELECT qryDirectory.FullNames, qryDirectory.HouseNbr, qryDirectory.Street, qryDirectory.HomePhone, Left([tblTrinity]![Street],4) AS SA1, Val(Trim(Right([tblTrinity]![HouseNbr],Len([tblTrinity]![HouseNbr])-InStr([tblTrinity]![HouseNbr],"-")))) AS SA2, Val(Trim(Left([tblTrinity]![HouseNbr],Len([tblTrinity]![HouseNbr])-InStr([tblTrinity]![HouseNbr]," -")))) AS SA3, [SA2] & " " & [SA1] AS SA4
FROM qryDirectory
WHERE (((qryDirectory.Street) Like [Forms]![frmAddressSelect]![Text2] & "*" And (qryDirectory.Street) Not Like "New Ad*") AND ((Val(Trim(Right([tblTrinity]![HouseNbr],Len([tblTrinity]![HouseNbr])-InStr([tblTrinity]![HouseNbr],"-"))))) Like [Forms]![frmAddressSelect]![Text19] & "*")) OR (((qryDirectory.Street) Is Null))
ORDER BY Left([tblTrinity]![Street],4), Val(Trim(Right([tblTrinity]![HouseNbr],Len([tblTrinity]![HouseNbr])-InStr([tblTrinity]![HouseNbr],"-")))), Val(Trim(Left([tblTrinity]![HouseNbr],Len([tblTrinity]![HouseNbr])-InStr([tblTrinity]![HouseNbr]," -"))));

This SQL does not work. It results in an "expression too complicated to evaluate" error. When it runs from the form the error number is 3107

[Text2] refers to a text box on the form in which the secretary can enter the first few letters of the Street upon which the Retirement Home is located.

[Text19] refers to a text box on the form in which the secretary can enter the street # on the street upon which the Retirement Home is located.

Any suggestions would be appreciated.

Tom

 

[THWatson]

MajP
These are the fields in tblRetirementHome
RetHomeID,RetirementHome,StreetNbr,Street,
Second Address,City,Province,PostalCode

I think your "best bang for the buck" solution is the tipping point. I mentioned this was one of those niggly little things that cause me (also you) considerable time in order to save the secretary 10 minutes a few times a year.

I had thought of putting an additional entry in tblRetirementHome - an entry called maybe "OwnsHome" - and then, as you suggest, putting a combo box on frmTrinity from which the secretary would choose either the specific Retirement Home or "OwnsHome." And set up an FK.

The problem with that is the margin for error. It would be too easy to, on the fly, assign someone to the wrong place, especially when you consider that one street has 3 Retirement Homes on it.

I counted them up this morning. There are 25 members out of 500 who live in Retirement Homes.

I'm going to work on the band-aid.

(One thing that bothers me a little bit is the "IsNumeric" operator. I was thinking of where the right string might be something such as 147B. However, the likelihood of this ever being the case with a multiple-occupancy is very, very remote. So it's probably not worth worrying about.)

Tom

 

[MajP]

It was just an example. I just assumed you wanted to double check it was a number portion of the address, but actually I think SA2 returns the street name not the number. Maybe something like.

Public Function getSA2(HouseNbr As Variant) As String
If Not Trim(HouseNbr & " ") = "" Then
getSA2 = Trim(Right(HouseNbr, Len(HouseNbr) - InStr(HouseNbr, "-")))
End If
End Function

 

[MajP]

so used in a query this returns an empty string if you pass in a null value and will never error out.

 

[THWatson]

MajP
I think I have talked myself into splitting off the Unit/Apt/Suite/Room bit into a separate field.

The vast majority of members live in private homes. Out of roughly 350 active individuals/families in the church, only 23 are in Retirement Homes at the moment.

In any event, I will do this and then see where we go from there.

Tom

 

[MajP]

I think it will serve you well to do so. However, I would still make the custom functions so that you can use them to pre-populate those fields with your existing data. Also if you do break it out, you may want a custom function to concatenate it back into a "mailing label".

 

[THWatson]

MajP
Right about the function.

The thing that's got me by the tail is this: It has to do with having a combo box on frmTrinity from which the Secretary selects the Retirement Home.
1. One street, Metcalfe Street, has 4 Retirement Homes on it. Several members in private homes also live on that street.
2. Assuming that the Secretary would normally learn that member A has moved to a Retirement Home...she would usually be told the name of the home, not the address.
3. The interesting thing is there is a complex of 3 buildings - a graduated care complex - called The Elliott, The Ellington, and The Ellridge.
They sit on a plot of land in this manner: The Elliott out by the street (street # 170), next The Ellington (# 168), further back in The Ellridge (# 172). I have no idea how they came to be numbered thus, as it isn't intuitive.
If each had a unique Postal Code that would help, but 2 do while The Elliott (#170) doesn't.
People get these 3 mixed up all the time.

I guess I can't safeguard everything against GIGO. Maybe I'm trying to set the bar too high.

Tom

 

[THWatson]

STEP 1
I have separated out the Unit/Suite/Apt/Room# portion into its separate field. So now tblTrinity has fields:
AptNbr
HouseNbr
Street

STEP 2
To tblTrinity add a RetHomeID field to link it to RetHomeID in tblRetirementHome

tblRetirementHome needs only 2 fields
StreetNbr
Street

- - - - - -

STEP 3
On frmTrinity, a process is needed by which the RetHomeID is filled in whenever the HouseNbr and Street fields match the StreetNbr and Street fields in tblRetirementHome. I assume the RetHomeID would be hidden and just filled in automatically.

Where I'm hooked a little is how to approach making sure this all works properly on frmTrinity. I would like it to work so that the secretary does not need to know whether or not the address being entered is that of one of the Retirement Homes.

What would be a routine that would kick in whenever an address was added or edited - a routine that cycled through the 23 Retirement Homes, looking for a match between HouseNbr and Street that has been entered on frmTrinity and StreetNbr and Street in tblRetirementHome?

Tom

 

[THWatson]

For Step 3, I think I will use a DLookup function. Gets around the necessity of doing a loop.

Dim strAddress As String
Dim varTemp As Variant
strAddress = Me.HouseNbr & " " & Me.Street & ", " & Me.City
varTemp = DLookup("RetHomeID", "tblRetirementHome", "CombinedAddress = '" & strAddress & "'")
If IsNull(varTemp) Then
Me.RetHomeID = Null
Else
Me.RetHomeID = varTemp
End If

A couple of things to work out on it yet, in order to allow for anomalies in data entry.

Tom

 

[THWatson]

I have done considerable work on this.

1. Created a new field in tblTrinity for the Apartment/Suite/Unit/Room number
2. Linked tblTrinity with tblRetirementHome by RetHomeID (one to many)
3. On frmTrinity, the RetHomeID field is hidden. The

BeforeUpdate event has the following code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
   On Error GoTo Form_BeforeUpdate_Error

If IsNull(Me.HouseNbr) Or IsNull(Me.Street) Then
Exit Sub
End If

Dim strAddress As String
Dim varTemp As Variant
Dim streetTemp As String
Dim retStreetTemp As String
Dim retStreetNbrTemp As Variant
Dim pcodeTemp As Variant

streetTemp = Nz(Trim(Left(Me.Street, InStr(Me.Street, " "))))
retStreetTemp = Nz(DLookup("TrimStreet", "tblRetirementHome", "TrimStreet = '" & streetTemp & "'"))
retStreetNbrTemp = Nz(DLookup("StreetNbr", "tblRetirementHome", "StreetNbr = '" & Me.HouseNbr & "'"))
pcodeTemp = Nz(DLookup("Code", "tblRetirementHome", "StreetNbr = '" & Me.HouseNbr & "' And TrimStreet = '" & streetTemp & "'"))

strAddress = Me.HouseNbr & " " & streetTemp & ", " & Me.City & " " & Format(Me.Code, "@@@ @@@")

varTemp = Nz(DLookup("RetHomeID", "tblRetirementHome", "TrimCombinedAddress = '" & strAddress & "'"), "")
Me.RetHomeID = varTemp

If (Me.HouseNbr = retStreetNbrTemp) And (streetTemp = retStreetTemp) And (Me.Code <> pcodeTemp) Then
    Call MsgBox("You have entered the address for a Retirement Home." _
                            & vbCrLf & "" _
                            & vbCrLf & "The Postal Code for that address is " & Format(pcodeTemp, "@@@ @@@") _
                            & vbCrLf & "That will be entered as the Postal Code for this record." _
                            & vbCrLf & "" _
                            & vbCrLf & "    Please verify all data to ensure that the member" _
                            & vbCrLf & "shows on the list of persons in that Retirement Home." _
                            & vbCrLf & "" _
                            & vbCrLf & "ALSO enter a Unit, Apt. or Room # if you have one." _
                            , vbExclamation, "Retirement Home address check")

Me.Code = pcodeTemp
strAddress = Me.HouseNbr & " " & streetTemp & ", " & Me.City & " " & Format(Me.Code, "@@@ @@@")
varTemp = Nz(DLookup("RetHomeID", "tblRetirementHome", "TrimCombinedAddress = '" & strAddress & "'"), "")
Me.RetHomeID = varTemp

End If

   On Error GoTo 0
   Exit Sub

Form_BeforeUpdate_Error:
    
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document Form_frmTrinity"
    
End Sub

4. There is also a command button on frmTrinity that opens a frmRetirementHomeList on which there is a list box which contains the names and addresses of the respective Retirement Homes. (this is to cover the situations where the secretary knows only the name of the Retirement Home to which the member has moved.)
When the secretary double clicks a row in the list box, the StreetNbr, Street, City and Postal Code fields are replaced by those of the relevant Retirement Home.

5. It goes without saying that I had to modify several queries and reports to accommodate the additional Apartment/Suite/Unit/Room number field.

- - - - - - - -
One small bugbear remains. There has been an inconsistency of data entry as to the Street name and Direction.
Sometimes Avenue is entered as Avenue, sometimes as Ave., sometimes as Ave. The same holds for Street (Street, St., St) and Road (Road or Rd. or Rd)
Street directions are sometimes East or West and sometimes E. or W.
Therefore a Street will appear as Abernathy Street West, or Abernathy St. W. or whatever.

Canada Post prefers abbreviations and no punctuation and Uppercase letters. i.e. AVE, ST, RD, N, W, S, E

My Question:
I can fix existing entries easily. Aside from breaking out the Street into 3 fields (Street, Street Type, Direction) is there a way to force, for example, an entry of Avenue or Ave. to AVE, Street or St. to ST, etc.?

Tom

 

[THWatson]

The other thing I can do, I suppose, is to put a label on the bottom of frmTrinity, indicating Canada Post guidelines for entering Streets and Street Directions.

Tom

 

[MajP]

I would on the after update run a replace function

Public Function getCleanAddress(theAddress As Variant) As String
Dim strAdd As String
If Not Trim(theAddress & " ") = "" Then
strAdd = theAddress & " "
strAdd = Replace(strAdd, "Avenue ", "AVE ")
strAdd = Replace(strAdd, "Ave. ", "AVE ")

strAdd = Replace(strAdd, "Street ", "ST ")
strAdd = Replace(strAdd, "Str. ", "ST ")

getCleanAddress = Trim(strAdd)
End If
End Function

You could improve this function by having a table that you read.

tblReplacements
badText
goodText

Street ST
ST. ST
St ST
Avenue AVE
Ave AVE
Ave. AVE

Then loop through the table

 

[THWatson]

Thanks, MajP

I'll have a go at that.

Tom

 

[MajP]

However I would beef it up, because you want to allow them to confirm the change

So read in the string
do while not problem table.eof
see if a problem exists
pop open a form showing the original text and then the proposed text (you could even hilite the problem)
allow the user to confirm or deny the change
if confirmed updated str
next problem
loop

Here is why.
North View Drive (N View Drive)
Avenue of the Stars Plaza, Suite 1 (AVE of the Stars?)
South Bend Blvd NW (S Bend Blvd NW?)

If not this will be like MS autocorrect features that you want to toss the machine out the window.

 

[THWatson]

Well, here's what I have been experimenting with...

Public Function getCleanAddress(Street As Variant) As String
Dim strAdd As String
If Not Trim(Street & " ") = "" Then
strAdd = Street & " "
strAdd = Replace(strAdd, "Avenue ", "AVE ")
strAdd = Replace(strAdd, "St. ", "ST ")

getCleanAddress = Trim(strAdd)

End If
End Function

I tried it on the AfterUpdate event for both the Street field, and for the form itself...using

Call getCleanAddress(Me.Street)

Nothing changes. The Replace function doesn't seem to kick in.

Where am I going wrong?

Tom

 

[dhookom]

It looks like you might be changing a value but not putting it back anywhere. It's like making a copy of something, changing it but leaving it in the copy room. I would expect to see something like:

   Me.Street = getCleanAddress(Me.Street)

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Of course!

Thanks, Duane.

Tom

 

[THWatson]

Actually, I think this may be a better formulation.

Public Function getCleanAddress(Street As Variant) As String
Dim strAdd As String
If Not Trim(Street & " ") = "" Then
strAdd = Street & " "
strAdd = Replace(strAdd, " Avenue", " AVE")
strAdd = Replace(strAdd, " St.", " ST")

getCleanAddress = Trim(strAdd)

End If
End Function

It's a matter of where to put the spaces.

Replace(strAdd, "St. ", "ST ") results in DelhiST whereas it needs to be Delhi ST

Also putting the space prior to St. eliminates the possibility of "St. Agnes St." being changed to "ST AgnesST"

Tom

 

[THWatson]

Also, seems as if the "Me.Street = getCleanAddress(Me.Street)" has to be put on the AfterUpdate event for the field.

For whatever reason, if I put it on the AfterUpdate event for the form, the replacement occurs but then the form freezes on that record.

Tom

 

[PHV]

on the AfterUpdate event for the form
I'd try the BeforeUpdate event of the form

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

 

[MajP]

actually I think you need to do the spaces on both sides

" St ", " ST "

If not "123 Story Lane" becomes "123 STory Lane"

And again that is why you need to prompt the user to accept or reject the change like "MS grammar check"

So St. Agnes does not become ST Agnes. And if they used St. to mean Suite not Street it does not become
123 Elm Ave., St. 101
123 Elm Ave, ST 101