Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509

    Unanswered: Problem build fixed length file.

    I have a problem. I am making a fixed format download file using OutputTo for download to a vendor. The following is the formatting information on the detail line of the report.


    =Format$([record_type],"!@@") & Format$([counter],"@@@@@@") & Format$([cust_code],"!@@@@@@@@@@@@@@@") & Format$([last_name],"!@@@@@@@@@@@@@@@@@@@@@@@@@") & format$([first_name],"!@@@@@@@@@@@@@@@@@@@@@@@@@") & Format$([ssn],"!@@@@@@@@@@@@@") & Format$([ticket_number],"!@@@@@@@@@@@@@@@@@") & Format$([amount_due],"@@@@@@@@") & Format$([insurance],"!@@") & Format$([com_part],"@@@@@@@@") & Format$([com_discount],"@@@@@@@@") & Format$([date_ordered],"!@@@@@@@@") & Format$([date_delivered],"!@@@@@@@@") & Format$([state],"!@@@@@") & Format$([store_cd],"@@") & Format$([norwestid],"@@@@@@@@") & Format$([def_months],"@@") & Format$([programcode],"@@@@") & Format$([dealer_number],"@@@@@@@@@@")

    The problem is when the first name is null, then it doesn't produce that field and everything that follows is out of alignment. I am a newbie to access programming, but will the following work to detect if it is a null and then force out 25 characters?

    =Format$([record_type],"!@@") & Format$([counter],"@@@@@@") & Format$([cust_code],"!@@@@@@@@@@@@@@@") & Format$([last_name],"!@@@@@@@@@@@@@@@@@@@@@@@@@") & IIf(IsNull([first_name])," ",Format$([first_name],"!@@@@@@@@@@@@@@@@@@@@@@@@@")) & Format$([ssn],"!@@@@@@@@@@@@@") & Format$([ticket_number],"!@@@@@@@@@@@@@@@@@") & Format$([amount_due],"@@@@@@@@") & Format$([insurance],"!@@") & Format$([com_part],"@@@@@@@@") & Format$([com_discount],"@@@@@@@@") & Format$([date_ordered],"!@@@@@@@@") & Format$([date_delivered],"!@@@@@@@@") & Format$([state],"!@@@@@") & Format$([store_cd],"@@") & Format$([norwestid],"@@@@@@@@") & Format$([def_months],"@@") & Format$([programcode],"@@@@") & Format$([dealer_number],"@@@@@@@@@@")
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  2. #2
    Join Date
    Dec 2003
    Posts
    268

    NZ Function

    Try using the NZ Function. It says if a value is null simply convert it to something else i.e. empty string, 0 or whatever.

    nz(Value, convert to value)

    IIf(IsNull([first_name])," ",Format$([first_name],"!@@@@@@@@@@@@@@@@@@@@@@@@@"))

    to

    format$(nz([first_name]," "),"!@@@@@@@@@@@@@@@@@@@@@@@@@")

    See if that works. Or you could change your iif statement

    format$(iif(isnull([first_name]),[first_name]," "), "!@@@@@@@@@@@@@@@@@@@@@@@@@")

    Just a coule of ideas.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    will this will give you 25 spaces if the field is Null ??
    NZ(SomethingThatMightBeNull, String$(25, " "))
    LATER: you wanted "@" so try:
    NZ(SomethingThatMightBeNull, String$(25, "@"))


    watch out for empty strings ""
    ...neither NZ() nor IsNull() will fix empty strings since they are not Null
    try testing for Len(SomethingThatMightBeEmpty) = 0

    and if your value could be Null or Empty, try testing for
    Len(NZ(SomethingThatCouldBeNullOrEmpty, "")) = 0

    izy

    ALSO LATER: sorry mjweyland for the almost-duplicate post - i type slowly
    Last edited by izyrider; 10-25-04 at 13:35.
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •