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

 

[MajP]

If this was me and I was doing this a lot, I think I would first try building custom functions to return SA1,SA2,SA3
So at least my SQl would look very simple. Like
Select
qryDirectory.FullNames,
qryDirectory.HouseNbr,
qryDirectory.Street,
qryDirectory.HomePhone,
getSA1([tblTrinity]![Street]) as SA1,
getSA2(tblTrinity]![HouseNbr]) as SA2
getSA3([tblTrinity]![HouseNbr) as SA3
From
Directory
...
ORDER By
getSA1([tblTrinity]![Street]),
getSA2(tblTrinity]![HouseNbr]),
getSA3([tblTrinity]![HouseNbr)

 

[MajP]

something like
Public Function getSA2(HouseNbr As Variant) As Long
Dim SA2 As Variant
SA2 = Trim(Right(HouseNbr, Len(HouseNbr) - InStr(HouseNbr, "-")))
If IsNumeric(SA2) Then
getSA2 = CLng(SA2)
End If
End Function

 

[THWatson]

MajP
Yes, I get what you're suggesting.

Before I get to that step though, I need to understand what's not working with the way I have things now.

Why does the following query column end up in a "Data mismatch in criteria" error?

SA2: Val(Trim(Right([tblTrinity]![HouseNbr],Len([tblTrinity]![HouseNbr])-InStr([tblTrinity]![HouseNbr],"-"))))

when the exact same column works fine in a query where and individual street and HouseNbr of a Retirement Home is selected?

I think it has to do with the possibility that no church member resides at that address, thus resulting in a Null value.

Tom

 

[THWatson]

The following SQL - to Preview/Print one Retirement Home selection at a time - works fine. It calls for entries to satisfy the [Text2] and [Text19] boxes.

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] & "*"))
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]," -"))));

Irrespective of how I structure the SA1, SA2 and SA3, if I want All Retirement Homes to print, I think I need to use this SQL along with a routine that loops through and prints those with church residents in them...and gives a message for those where no church member resides.

Make sense?

Tom

 

[dhookom]

Tom,
You've been a TT member for almost 10 years and have logged in 5,617 times. You have started 164 threads and posted 2,113 replies. Isn't it about time you stop referencing controls with names like Text2 and Text19? You're not a rookie so kick it up a notch ;-)

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
You're more than absolutely right. That database has been around for quite a while, and I just never bothered fixing something that was working.

Thanks for the critique!

Tom

 

[dhookom]

You're excused. Just don't look at any of my older application ;-)

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Bad function example.

Public Function getSA2(HouseNbr As Variant) As Long
Dim SA2 As Variant
if not trim(HouseNbr & " ") = "" then
SA2 = Trim(Right(HouseNbr, Len(HouseNbr) - InStr(HouseNbr, "-")))
If IsNumeric(SA2) Then
getSA2 = CLng(SA2)
End If
end if
End Function

Before I get to that step though, I need to understand what's not working with the way I have things now.
Why does the following query column end up in a "Data mismatch in criteria" error?
think it has to do with the possibility that no church member resides at that address, thus resulting in a Null value.

Huh? Of course it will fail on a null value, and that is exactly why I suggested a custom function. The query is a mess, full of vba functions. Do yourself a favor and build custom functions that will allow you to error check, debug, check for nulls, and reuse them.

 

[dhookom]

Custom functions pull the complexity out of queries where it shouldn't be. You can comment your code and use it over and over and over in queries, other code, control sources, etc.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
I neglected to ask how your grandson is.

Also, I neglected to tell you that I became a great grandfather a week ago. I have 8 grandchildren. One is 20 and she had a son a week ago.

Good thing Janice and I were married when we were 12 of this couldn't have happened.

Tom

 

[MajP]

This is also one of those times where I think I would store the value in the table.

If my address field is
"xxxxxxx xxxxxxxx xxxxxxx-1234"
And I am doing a lot of searches and display of 1234, I am definitely storing 1234 in a separate field. This can be don using an update query and the custom function I described. I would almost argue that your data is not normalized because you are treating house number as a atomic piece of data.

 

[dhookom]

MajP is again correct about the fields. If you plan to query something separately, it should be in its own field. Most of use (I hope) would never think of storing the last and first names or city and state in the same field.

BTW: Jackson (6) is very good and expecting his first cousin in March

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Perhaps clarifications are in order.

First of all, I don't store First and Last Names, nor City and Province, in the same field. Would never think of that.

As for the issue of, using Maj P's example, "xxxxxxx xxxxxxxx xxxxxxx-1234" the database has a HouseNbr field. Perhaps 3/4 of the time the entry in the field is a one, two, three, four, or even five digit number.

There are, however, the rest of the instances where a Unit or Apartment or Room # is included in the HouseNbr field.
811 - 19
3 - 170 etc.

The full address for 811 - 19 is "811 - 19 Woodlawn Rd."
The full address for 3 - 170 is "3 - 170 Metcalfe St."
when the HouseNbr and Street fields are combined.

Let's use the example of "3 - 170 Metcalfe St."
On the form, the secretary has 2 options. She can enter the first few letters of the street "Metc" in Text2 (which I will rename) and then "170" in Text19 (which I will rename) and click Preview or Print and get a list of church residents who reside at "170 Metcalfe St." The other option is to double click the Retirement Home at 170 Metcalfe St. and the work will be done for her.

So the purpose of SA1 is to extract the left side of a HouseNbr, the "3" from an address such as "3 - 170" ... and the purpose of SA2 is to extract the right side of a HouseNbr, the "170" from "3 - 170"

Is it your suggestion that there should be a separate field for the Unit, or Room, or Apartment portion of a HouseNbr?

Tom

 

[MajP]

Based on the way you use them I would. "Atomicity" of 1NF is debateable, ends up being what fits your needs. It is atomic in one use but not in others. To me it would simplify what you are doing throughout the DB. I might not force the user to see or even input Apt, House, Room number but I might run an update query prior to execution of other queries to update the data.

"Some definitions of 1NF, most notably that of Edgar F. Codd, make reference to the concept of atomicity. Codd states that the "values in the domains on which each relation is defined are required to be atomic with respect to the DBMS."[9] Codd defines an atomic value as one that "cannot be decomposed into smaller pieces by the DBMS (excluding certain special functions)."[10]

Hugh Darwen and Chris Date have suggested that Codd's concept of an "atomic value" is ambiguous, and that this ambiguity has led to widespread confusion about how 1NF should be understood.[11][12] In particular, the notion of a "value that cannot be decomposed" is problematic, as it would seem to imply that few, if any, data types are atomic:

* A character string would seem not to be atomic, as the RDBMS typically provides operators to decompose it into substrings.
* A date would seem not to be atomic, as the RDBMS typically provides operators to decompose it into day, month, and year components.
* A fixed-point number would seem not to be atomic, as the RDBMS typically provides operators to decompose it into integer and fractional components.

Date suggests that "the notion of atomicity has no absolute meaning":[13] a value may be considered atomic for some purposes, but may be considered an assemblage of more basic elements for other purposes. If this position is accepted, 1NF cannot be defined with reference to atomicity. Columns of any conceivable data type (from string types and numeric types to array types and table types) are then acceptable in a 1NF table—although perhaps not always desirable. Date argues that relation-valued attributes, by means of which a field within a table can contain a table, are useful in rare cases.[14

 

[THWatson]

MajP
Thanks for the explanation, and the references.

I have to think through what fits the needs, and also how many objects would need altering if I separate out the "Unit/Room/Apartment" piece into a separate field.

Irrespective of how I do it, I would like to end up at the place where the secretary can select All of the Retirement Homes in the list box and get a report of church residents in each of them...bearing in mind that 4 or 5 of the Homes have no church members residing in them.

It's never an issue when the secretary selects an individual Home from the list. There's either data or there's not. So one approach is for her to do it that way. But my thinking was that the program should be able to simplify things for her by doing the whole bunch with the click of one command button.

Truth be told, she would only need to do this about twice a year...so it's one of those niggly little things.

Turns out I spend a lot of time trying to save her several minutes!

Tom

 

[MajP]

Irrespective of how I do it, I would like to end up at the place where the secretary can select All of the Retirement Homes in the list box and get a report of church residents in each of them...bearing in mind that 4 or 5 of the Homes have no church members residing in them

I am a little confused on the problem. Originally it was a query to complex to evaluate, then a datatype mismatch, but this seems unrelated or different.

If each home has this tblRetirementHome.RetHomeID, and people are assigned to retirement homes I would think you obviously have a foreign key associating people to retirement homes? Is there not a foreign key in the directory for fkRetHomeID?

 

[THWatson]

Hmmm, sorry that I caused confusion. Didn't mean to do so.

My initial post said

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.

Perhaps I didn't explain it properly.

Choosing one Home at a time always worked.

The issue was the inability to use one command button to get a list of church members who reside in the various Retirement Homes, recognizing that there are Homes in which no church members reside. I had a command button on there but when it was pressed the "too complex to evaluate" error occurred...and in the query the "data type mismatch."

But you have raised a concern that I hadn't thought about before and need to look at - having a FK related to the RetHomeID.

I was treating, for example, "170 Metcalfe Street" as an address just like any other address...albeit an address at which a Retirement Home exists. However, you make a good point that there isn't anything else at that address but a Retirement Home, so there's no reason that couldn't be done, and it may well solve the entire problem.

Again, I apologize for any confusion. Thanks for sticking with me on this!

Tom

 

[THWatson]

MajP
The table which holds Members demographical data - first and last name, address, when joined, etc. is tblTrinity. The form on which data is entered/edited is frmTrinity.

A check box could be added. This check box would be the FK to tblRetirementHome...and would be checked whenever the address is the same as one of the Retirement Homes.

The problem I see with that is that the Secretary needs to know, by memory, the addresses of all 20 Retirement Homes, so she knows that if a member moves from A to B that B is a Retirement Home. Might work (probably would) with the regular Secretary, but relief fill-ins have created database problems before. Also if the current Secretary left and a new Secretary was hired, she'd be a while getting up to speed.

Unless there's some method by which this check box gets filled in automatically when the member's address matches the address of one of the Retirement Homes??

Tom

 

[MajP]

There are, however, the rest of the instances where a Unit or Apartment or Room # is included in the HouseNbr field.
811 - 19
3 - 170 etc.
The full address for 811 - 19 is "811 - 19 Woodlawn Rd."
The full address for 3 - 170 is "3 - 170 Metcalfe St."
when the HouseNbr and Street fields are combined.

So I have to say that this is not properly designed to meet your needs and thus these complicated work arounds. If it was designed correctly then it would be a whole lot easier.
Room #
Apt #
Street

So if I under correctly, on "19 Woodlawn Rd" there is a retirement home. Lets say it has ID "RetHomeA". If that was the case then I would have a table.

TblRetirementHomes
RetHomeID
StreetAdress

Data
RetHomeA 19 Woodlawn Rd

This then links to tblTrinity by street. Also I would think on my input form the frmTrinity I have a pull down for my street which includes the street addresses for the retirement homes.

However if most or a lot of people live in retirement homes then I would think I design it the other way. I assign people to retirement homes on frmTrinity which pulls the street address from tblRetirementHome. Then in tbl trinity I store Room# and Apt#.

However, I have no idea how much bang for the buck you would get out of a redesign. You may be better off putting a band-aid on what you have.

So going back to the band aid, I strongly believe by simplifying your queries with custom functions you will solve the too complex to evaluate. These functions can trap a null error which will likely solve the data type mismatch.

Here is why

Public Function getSA2(HouseNbr As Variant) As Long
Dim SA2 As Variant
if not trim(HouseNbr & " ") = "" then
SA2 = Trim(Right(HouseNbr, Len(HouseNbr) - InStr(HouseNbr, "-")))
If IsNumeric(SA2) Then
getSA2 = CLng(SA2)
End If
end if
End Function

if this thing fails because of a null address or no number it returns a 0.

This obviously bombs out

Val(Trim(Right([tblTrinity]![HouseNbr],Len([tblTrinity]![HouseNbr])-InStr([tblTrinity]![HouseNbr],"-"))))