Omiting empty cells in a query to show on Report

[Brian62]

I am trying to create a query that will remove empty cells in all columns off of another query that collects the data from multiple tables. I like to combine the cells with data in a report.

Example:

Name Date Column1 Column2 Column3 etc
Sally 10/01/10 Text no text text .....
Ted 10/02/10 no Text Text No text

I like it to show something like this on a report:

Name Date
Sally 10/01/10 Text Text Text
Ted 10/02/10 Text Text Text


Is there a way I can do this?

 

[vbaInet]

Without the column1, column2... labels right?

 

[Brian62]

Yes, that is correct! Thanks..

 

[vbaInet]

Use a long textbox and concatenate the fields like this:

=[Field1] & Chr(9) & [Field2] & Chr(9) & ... etc.

 

[Brian62]

When I run the query I am getting blocks in front and after the columns that have no data. "=" represents the blocks.========Could Not Locate=============Record not found========

 

[vbaInet]

It won't work in the query, use it in the control source of the textbox. However, it just clicked that even though it works, the field values that follow after the first will still not be aligned properly when compared to records below.

See my first reply (i.e. post #4) in the following thread regarding this issue:

http://www.access-programmers.co.uk/forums/showthread.php?t=202489

 

[boblarson]

Use the + operator instead of the ampersand (&) as it will remove nulls:

=[Field1] + (Chr(9) + [Field2]) + (Chr(9) + [Field3]) ..etc.

 

[boblarson]

the field values that follow after the first will still not be aligned properly when compared to records below.

True......

 

[Brian62]

It's almost there. It got rid of all but the begining, between and end of the Query/report. Here is what it looks like with the "=" represents the box.

=Doc #36: File 1, Page 3 signature and date missing.=Doc #37: File 1, "Office use Only" not filled in.=

 

[vbaInet]

If that doesn't work for you then do it in code in the Print event of that section. But I don't think you took note of my last point about using this method which Bob also pointed out?

 

[Brian62]

Would I write the code the same way? Which code builder do I use in Print Event; Macro Builder, Expression Builder or Code Builder? Thanks!!!

 

[vbaInet]

Did you pick on my last point about using this method?

 

[Brian62]

Not sure what it means.. This is only a guess.. Does it mean that the problem I have will remain? If not, how would I correct it if it's possible?

 

[vbaInet]

Use this in your report and see how the records look:

=Field1 + "     " & Field2 + "     " + ... etc

That is, 5 spaces inside the quotes.

 

[Brian62]

I tried it in the query and in the reports text box control source and it would not work. Confused...

 

[vbaInet]

It won't work in what way? It errors or nothing happens?

 

[Brian62]

It comes up and shows the code that I wrote. It is not showing anything. Not sure where to put the code. I guess I need clearer directions. I have been doing databases a long time but never this kind of coding.

 

[vbaInet]

The code (with a prefixed "=") should go in the control source of the textbox.

 

[boblarson]

I tried it in the query and in the reports text box control source and it would not work. Confused...

If you put it in the Report's text box control source you need to make sure that there are NO CONTROLS named the same as your fields you are using in the concatenated value. It will generate a #Name error if you have any controls named the same as the fields you are trying to use (that goes for all calculated controls).

If you are using it in a query, you don't include the = sign.

 

[Brian62]

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)