Ignore empty fields in Criteria append

  • Thread starter Thread starter rej2008
  • Start date Start date
R

rej2008

I have a database with following fields.
PARENT_LName (Joe)
PARENT_FNAME (Smith)
Child1 (Shawn)
Child2 (Samantha)
Child3 (Tony)
Child4
Child5

I want to setup a criteria to report a list of all parents and their
childrens name as follows:
PARENT CHILDREN
Joe Smith Shawn, Samantha, Tony

I created a Query and tried to append the children's names using an
Expression such as the one below, but it didn't work, because it fails for
anybody who has less than 5 kids, because the expression doesn't get
evaluated if it sees an empty null field.

How can I tell it ignore Null fields and display the children's names
whether there is only 1, 2 or more.

Expr3: [Child1] + ", "+ [Child2] + ", "+ [Child3] + ", "+ [Child4] + ", "+
[Child5]
 
There are two distinct concatenation characters in Access.

& ignores Nulls, + doesn't.

In other words,

"x" & Null returns "x"
"x" + Null returns Null

Try:

Expr3: [Child1] & (", "+ [Child2]) & (", "+ [Child3]) & (", "+ [Child4]) &
(", "+ [Child5])
 
Thank you so much. That was helpful.

I am not an expert on Access, So if I can expand on that question:
- How do I export the data with some formatting, in to an Excel file.
I wanted to create a macro that would actually run this Qry and save it to
an Excel file and automatically open the Excel file.

- Couldn't figure out how to record a macro either as I am confused by their
new menus in Access 2007.

- is it true that Access 2007 will not allow you to save Report results as
Excel files? How do we then save the report results with certain formatting
to an excel file?

Thank you.

Douglas J. Steele said:
There are two distinct concatenation characters in Access.

& ignores Nulls, + doesn't.

In other words,

"x" & Null returns "x"
"x" + Null returns Null

Try:

Expr3: [Child1] & (", "+ [Child2]) & (", "+ [Child3]) & (", "+ [Child4]) &
(", "+ [Child5])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


rej2008 said:
I have a database with following fields.
PARENT_LName (Joe)
PARENT_FNAME (Smith)
Child1 (Shawn)
Child2 (Samantha)
Child3 (Tony)
Child4
Child5

I want to setup a criteria to report a list of all parents and their
childrens name as follows:
PARENT CHILDREN
Joe Smith Shawn, Samantha, Tony

I created a Query and tried to append the children's names using an
Expression such as the one below, but it didn't work, because it fails for
anybody who has less than 5 kids, because the expression doesn't get
evaluated if it sees an empty null field.

How can I tell it ignore Null fields and display the children's names
whether there is only 1, 2 or more.

Expr3: [Child1] + ", "+ [Child2] + ", "+ [Child3] + ", "+ [Child4] + ",
"+
[Child5]
Click to expand...
Click to expand...
 
It really is hopelessly optimistic to expect to export an Access report
(which is, after all, basically a representation of a piece of paper!) to an
Excel file. I know that Access versions passim have been capable of doing
this in a manner of speaking (I have no idea if it's still there in A2007),
but in my experience it was always pretty useless: the results looked
nothing like the report, and were so haphazardly laid out in the spreadsheet
as to make it pretty much unusable.

When you export *data* from Access (as opposed to a report), you are
exporting *only* data, not formatting. The best place to apply formatting
to an Excel spreadsheet is...in Excel! Possibly you could create a macro in
Excel to do this, depending on what you want. You can also manipulate Excel
from within Access by means of automation, but it's a fiddly business and I
suspect that if your Access expertise is at the level of macros then you
will find it beyond you.

I've never used macros in Access (they are very much a second-rate
alternative to using VBA) so I don't know much about them. But, I don't
believe it is actually possible to record a macro in Access, any version.
However, in Access 2007 you can export a query to Excel, and you can save
the export steps to make it easier to repeat (and one of the steps is to
automatically open Excel when the export is complete).

But, why do you want to export the data to Excel anyway? Have you
considered that whatever analysis or manipulation is to be done in Excel
might be possible within Access, with the results being presented as a
nicely-formatted report?
 
Thank you kindly for your detailed reply. I am not too familiar with VBA yet.
I had a simple macro that would save the report results in to an Excel file,
which workd fine in A2003, but not anymore in A2007. The command was:
DoCmd.OutputTo acOutputReport, "rptNewEmailList", acFormatXLS, Filename, -1

Apparently that feature is not supported in A2007 anymore.

I am sure I should be able to do almost any of the analysis of data within
Access itself. But when it comes to sharing data, it becomes a problem,
especially with people who have no experience or no access to Access!

I just need to generate some reports or Queries and save the results to an
Excel sheet so that I can send it out to few people for further use.

Yes, I noticed the export feature in A2007. But I didn't know how to save
those steps so that I can repeat those same steps in the future, and that was
essentially my question.
 
To be honest if you've got something that works for you in one version of
Access but not in the next version, my first advice would be, go back to the
previous version! Sounds like a classic case of "if it ain't broke, don't
fix it".

If you go through the export process in Access 2007, the final thing the
wizard does is to *ask* you whether you want to save the steps.
 
Yes, I agree. Unfortunately I am dealing with two different computers. One at
work running A2003 and the other one at home running A2007. I think I will
just have to create a Query and format the output the way I want it and then
create an export script in A2007. Hopefully the script works in A2003 as well.

Yes, I did see the wizard where it asked for saving the script.

Thank you. It is disappointing that there isn't one method that works on
both versions of the Access. At some point, people will have to upgrade their
Access to a newer version and this could mean a lot of issues for people who
have a lot of scripts that potentially may not work in A2007. After all these
years in Software business, one would think that Microsoft will have a better
solution that leave their customers without too many options than to recreate
everything.
 
You are quite correct. There is a lot about Access 2007 which is going to
disappoint people who are upgrading, there are already a lot of questions in
these newsgroups along the lines of "it worked in Access 2003 but doesn't
work in Access 2007".

If I were you I would have both Access 2003 *and* Access 2007 installed on
your home computer, but I realise that this is not a practical solution for
everyone.
 
Back
Top