Re: Ignore empty fields in Criteria append

Tech-Archive recommends: Fix windows errors by optimizing your registry


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" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)


"rej2008" <rej2008@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CBC5ECE8-93CF-4E98-8D46-FF019CB5CD2E@xxxxxxxxxxxxxxxx
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]



.

Relevant Pages

  • Re: MailMerge problems between Mac and PC
    ... I have encountered numerous problems over the years with mailmerge files, but these two have me stumped. ... An excel file that is a simple database of information on donors has no formulas, just data such as name, address, and amount given. ... On a PC, the formatting for the dollar amount given is lost. ... If I do the merge on a Mac, ...
    (microsoft.public.mac.office.word)
  • Re: print out
    ... Edit, Move or Copy Sheet, create a copy ... If you are just copying a selection, with paste special, you will have to paste the formatting ... > When I print a excel file, the print out is coming with doted pattern. ...
    (microsoft.public.excel.misc)
  • Re: table format
    ... >Yes reformatting the excel file would fix the problem...however I want the ... >excel spreadsheet to remain showing the full dollar amounts. ... >> Does changing the formatting in the source file make a difference. ... >> Brian Reilly, MVP ...
    (microsoft.public.powerpoint)
  • Re: Excel serial date formats
    ... Another way of dealing with serial dates can be found in the attachment at: ... Unless you're working with a data source that is limited to serial dates, ... >>> Excel file, and the formatting doesn't transfer to Word. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: eliminating trailing zeroes imported into word document from excel file
    ... containing numbers and have formatted the cells that contain the ... when I access the Excel file in a word document using Word's ... the display formatting and retrieve the complete contents. ...
    (microsoft.public.word.docmanagement)