Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: CR/LF in a textbox & dynamically sorting a predefined query

    Good morning all,
    We have address information stored in the following format: Address1, Address2, City, State, Zip. The problem is sometimes Address1 is used for a company name and Address2 for the actual address. Other times Address1 is used for the address and Address2 is left blank. Typically on a report there would be a field for each, but when there isn't an Address2 there is a gap between the address and the city,state,zip:

    Ex 1)
    Bill's Inc.
    1234 Main St.
    Whoville, WI 45456

    Ex 2)
    1234 Main St.

    Whoville, WI 45456

    I can combine Address1 and Address2 in the controlSource of the textbox, but that looks like crap. What I would like to do is append a carriage return, line feed between the two, like this:

    =[Address1] & vbCrLf & [Address2]

    Is this possible? I tried the above, but no success. Maybe I need a function?
    Keep in mind this is a textBox on a report.

    2nd question: Hopefully this is simple. I have a pre-made query, but I need a way to sort it based on a comboBox selection. It is a very complex query with a bunch of 'GROUP BY' criteria. The only way I know how to sort it is by moving the field I want sorted to the front and I can only do that in design mode, as far as I know.

    Thanks for the help.

  2. #2
    Join Date
    Jan 2004
    I figured out the first question. I just made a function that accepted two strings and returned the needed result. I still need help on the 2nd question though. Thanks again.

  3. #3
    Join Date
    Jan 2002
    Bay Area
    This may help you with your Access question #2, sorting in a pre-made query based on a combo box selection.

    You can add an extra field to the query that tests what selection was made in the combo box (which lists the possible sort fields), then sort that field in ascending order with the "Show" box unchecked. I have attached an example.

    Here is the code in the query field that determines the sort order:

    IIf([Forms]![Form1].[cboSortKey]="First Name",[First Name],(IIf([Forms]![Form1].[cboSortKey]="MI",[MI],[Last Name])))

    Which says: if combo box displays "First Name", added query field will use the field [First Name], otherwise if the combo box displays "MI" (middle initial), query uses [MI] field, otherwise [Last Name] field is used.

    Attached Files Attached Files

Posting Permissions

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