Omiting empty cells in a query to show on Report

[boblarson]

First I'm trying to do this in the query. Here is the code I wrote:

Findings: [Com-Doc #2] + “ “& [Com-Doc #12-5] +” “& [Com-Doc #25]

This is the error I am getting: "The expression you entered contains invalid syntax"

When I put the same code in the countrol source in the Report with the = sign I get the same error. When I take away the = sign the error shows me the entire code I put there (Syntax error in query expression 'code.....).

When I put this code in the query I get only one finding when I know there are multiples. Note with or without the $ sign.

Findings: [Com-Doc #2] & Chr$(0)+[Com-Doc #36] & Chr$(0)+[Com-Doc # 37] & Chr$(0)

A couple of things - You don't use the $ with Chr anymore. That is OLD legacy code which exists just for backwards compatibility. Second, I don't know why you're trying to use Chr(0) anyway.


Can you upload a copy of your database with the table (and bogus data) and the query you are trying to use? You might even include the report (remember to include any extra tables that might be in the query or report as well). I think that would help us try to get down to it. I'm more of a visual, hands-on kind of guy when it comes to troubleshooting.

 

[Brian62]

I have attached the database. Note the queries and the Report are named Test().
If possible I like each finding separated by a ; and a space if possible

Example: Finding1; Finding2; Finding3......etc.

Thanks for your help!

 

[Brian62]

Has anyone been able to figure this problem for me? I appreciate your help you have all provided to me. Thanks!

 

[boblarson]

Has anyone been able to figure this problem for me? I appreciate your help you have all provided to me. Thanks!

I'm afraid I have not been able to do much with it. The sheer number of fields you are trying to do this with and the fact that they have # signs and - signs in the names and the fact that the columns are being returned in a way that makes it hard to determine what is what.

I just don't know if this is really feasible at this point.

 

[vbaInet]

I'm afraid I have not been able to do much with it. The sheer number of fields you are trying to do this with and the fact that they have # signs and - signs in the names and the fact that the columns are being returned in a way that makes it hard to determine what is what.

I just don't know if this is really feasible at this point.

... Plus you've ignored or haven't taken into consideration the point we've been hammering about it not showing properly aligned.

 

[Brian62]

The number of fileds can't be helped but I can remove the # and - symbols if needed. If they are removed can it be done then? I know it's a lot to ask but this will save me so much time. I have cut my work load by more than half with Access as I have done in every position I've held but this I know is more complicated because of what I have asked.

Is there another way you of to make this work? Thanks again!

 

[Brian62]

I'm not sure what you mean by properly aligned. Can you explain? Thanks!

 

[vbaInet]

I'm not sure what you mean by properly aligned. Can you explain? Thanks!

Here's an example of what it would look like:

[B][COLOR=Red]Field1 [/COLOR]   [COLOR=Navy]Field2[/COLOR]    [/B] 
[COLOR=Red]Jumping over the fence[/COLOR]   [COLOR=Navy]Michael did[/COLOR]
[COLOR=Red]In the country of the blind the one eyed man is King[/COLOR]   [COLOR=Navy]Mangoes[/COLOR]
[COLOR=Red]I'm famous[/COLOR]   [COLOR=Navy]Who do you think you are?[/COLOR]

Notice the colour coding I've used to indicate field1 and field2. That's how your report will look so you won't have field2 aligned properly in that row.

 

[Brian62]

So another words there is no way to make this work the way I would like it to?
We were so close except for the boxes between the fields. If it could be replaced with the semi-colon ( then it would be perfect.

 

[vbaInet]

What if there's a semi-colon in one of your fields?

 

[Brian62]

There is only a semi-colon if there is more than one finding for that field or it is just a period at the end of the text (finding).

Example: could not locate; need document.

 

[vbaInet]

I understand what you mean by separating by semicolons, but my question is if you a field contains a semi-colon then you won't know which is which.

An example:

FieldA - could not; locate
FieldB - need document

Concatenated - could not; locate; need document.

Do you see now? So my question is, do any of your fields contain semi-colons or would they ever contain semi-colons?

 

[Brian62]

Yes some of my fields may contain semi-colons only when there are multiple findings for that document.

The individual being inspected will have all findings for each document (if any) in the findings field (in the query or report) from multiple fields on one line, if possible. Usually there are only a couple of findings per individual I inspect. Most may have no findings at all.

I am just trying to consolidate all findings on the report for each individual as a group so my report is not 30 pages or so. This will cut it down to just one page so the coordinator can get the findings corrected without printing so many pages. I hate wasting paper.

I know I may have repeated myself but I am just giving a reminder. After all the replies you and I may get lost in the results we are trying to accomplish.

 

[boblarson]

Too bad you didn't set this up in a normalized fashion with the findings as ROWS in a table instead of columns. Then it would be EASY to consolidate because you can have the controls on the report for the records and just set the Can Grow, Can Shrink properties of the controls and the section they are in to YES.

So, you would have your main report and then the findings would have been a subreport which would only have shown what existed. Much better that way.

 

[Brian62]

How can you set up a table to show things in rows versus columns? I never seen that before. It's always in columns that I knew of..

I also tried to insert a sub-report with the can grow and can shrink to yes and since I have many colums/rows there would be a large separation between each finding.

 

[boblarson]

How can you set up a table to show things in rows versus columns? I never seen that before. It's always in columns that I knew of..

I'll show you below

I also tried to insert a sub-report with the can grow and can shrink to yes and since I have many colums/rows there would be a large separation between each finding.

The problem with you trying to use a subreport in this instances is you have too many fields in the row to do it right. It just doesn't work.

So, here's a very generic sample because I can't identify your whole business process and so this is just a sample:

Table

tblStudyAudit
StudyAuditID - PK (Autonumber)
fields included here which are not finding based but
instead identify unique study attributes (like patient ID, Date of Birth, Study date, etc.)

tblFindingType
FindingTypeID - PK (Autonumber)
FindingDescription - Text (this would be identifiers like ComStaffStudy, Doc24, etc. - in other words most of your column headings and each as its own row).

tblFindings
FindingID - PK (Autonumber)
StudyAuditID - FK (Long Integer)
FindingTypeID - FK (Long Integer)
FindingValue - Text

and so when you went to pull the data you could pull all of the finding data as ROWS from the tblFindings table and then have the tblStudyAudit as the main report with the tblFindings table (linked to tblFindingType to show the descriptions of what it is) as the subreport and it would display as rows and only as many as you needed for whatever was filled out.

 

[Brian62]

I think I understand what you are proposing but with all the tables I have I don't think I can since I only can have so many columns per table, it maybe impossible for me to do.

The doc#'s are yes, N/A or No and the Com-Doc's (com means comment) are the findings for each of the doc#'s I think makes it impossible to do with approx 80 columns for each. Also being broken down by individual.

Example:

Name

Column1 Column2
Doc #2 Com-Doc#2
Doc #37 Com-Doc #37
etc.... etc.....

-------------------------------
Name

Column1 Column2
Doc #2 Com-Doc#2
Doc #37 Com-Doc #37
etc... etc....

I had to break down the tables to 10 since there are too many columns of data that is inputted as you may have seen. I am running out of options.....

 

[boblarson]

I think I understand what you are proposing but with all the tables I have I don't think I can since I only can have so many columns per table, it maybe impossible for me to do.

The doc#'s are yes, N/A or No and the Com-Doc's (com means comment) are the findings for each of the doc#'s I think makes it impossible to do with approx 80 columns for each. Also being broken down by individual.

Example:

Name

Column1 Column2
Doc #2 Com-Doc#2
Doc #37 Com-Doc #37
etc.... etc.....

-------------------------------
Name

Column1 Column2
Doc #2 Com-Doc#2
Doc #37 Com-Doc #37
etc... etc....

I had to break down the tables to 10 since there are too many columns of data that is inputted as you may have seen. I am running out of options.....

You aren't understanding. I MEAN ONE TABLE for the findings. Not 10 not 80 fields in the table, not 20 fields in the table, but 4 fields in the table. You don't put Column 1, Column 2, etc. Read the table structure again. You would have fields like this:

[B]tblStudyAudit[/B]
StudyID    StudyDate   
1             4/15/2010
2             12/19/2010
 
 
[B]tblFindingType[/B]
FindingTypeID   FindingDescription   
1                    Doc2
2                    ComDoc2
3                    Doc37
4                    ComDoc37
5                    Doc28
6                    ComDoc28
 
 
tblFindings
FindingID    StudyID    FindingTypeID    FindingValue
1                 1            1                    Yes
2                 1            2                    My Doc2 comment here
3                 1            3                    No
4                 1            4                    My Comment About Doc 37
5                 1            5                    N/A
6                 2            1                    Yes
7                 2            2                    My StudyID 2 comment about Doc 2
8                 2            3                    Yes
9                 2            4                    My Comment about Doc 37

And so on.

You don't need 10 tables nor do you need more than 4 to 6 fields in any of the tables (most likely).

 

[Brian62]

So if I understand this correctly, Findingdescription is where I can have the column in names in a row instead which would amount to about 160 rows for the Doc's and Com-Doc's. I understand the rest.... The YES and N/A are as one and No is by it's self. I could also use these as a drop down for my form. I think I am on the right track. I've done this before but not with so many columns. It seems like it is like creating a table with just names. I can see where this would be easier if I got the full understanding of what you are saying.

Thanks for being VERY patient with me!

tblFindingType
FindingTypeID - PK (Autonumber)
FindingDescription - Text (this would be identifiers like ComStaffStudy, Doc24, etc. - in other words most of your column headings and each as its own row).

 

[boblarson]

So if I understand this correctly, Findingdescription is where I can have the column in names in a row instead which would amount to about 160 rows for the Doc's and Com-Doc's.

Yes, that is correct.

I understand the rest.... The YES and N/A are as one and No is by it's self.

What do you mean No is by itself?

I could also use these as a drop down for my form. I think I am on the right track. I've done this before but not with so many columns. It seems like it is like creating a table with just names. I can see where this would be easier if I got the full understanding of what you are saying.

Yes, it then allows you to use a subform for entering the APPLICABLE values. So, if there is no answer required for one, then no row has to be created for that.

Thanks for being VERY patient with me!

I understand how the normalization concepts can be very confusing. I've been there so I know. I try as best as I can to keep that in mind when trying to help. Sometimes though it hurts my brain to have to think too hard about a particular poster's problem and how to explain it. But I think you might be catching on with this.

With Access you want to have a mindset of thinking THIN and TALL (meaning few fields and lots of rows) over SHORT and FAT (meaning many fields and few rows).

Access, as a relational database, works much better with normalized data, and it makes pulling it out with queries and reports much easier.