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]

I feel pretty embarrassed coming back to this.

I got the getCleanAddress function working fine on the BeforeUpdate event of the form.

However, I wanted to pursue the good and bad text "table" approach, as I find there are 52 possible replacements, so I thought the table would be more efficient.

I have created a table, tblReplaceText. It has the following fields: textID, badText, goodText

I have created a Module called modReplace. I have been fighting with the following structure.

Public Function ReplaceText(Street As Variant) As String
Dim strbadText As String
Dim strStreetEntry As String
Dim strReplace As String
Dim strgoodText As String

If Not Trim(Street & " ") = "" Then
strStreetEntry = Mid(Street, InStr(Street, " ") + 1)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblReplaceText")

If Not Trim(Street & " ") = "" Then
strStreetEntry = Street & " "

Do While Not rst.EOF
[b]If strStreetEntry <> rst.badText Then[/b]

Next
Loop

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

End If
ReplaceText = strgoodText
End Function

The procedure errors out on the row in bold print highlighting the .badText piece of the code.

I suspect there might be other problems as well, as doing these recordsets properly has always been one of my greatest weaknesses.

I also tried the DLookup approach but haven't been able to get that working either.

Tom

 

[THWatson]

Actually, I don't believe the

strStreetEntry = Mid(Street, InStr(Street, " ") + 1)

piece will work either.

Whereas it will separate out "St." from "Morton St." it will be in difficulty with "Morton St. N."

So I have to revamp that expression.

Tom