Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Control Names (XP)

    I'm trying to figure out a query that produces the report. I have it working. I'm concatenating City, State and Zip fields. When the fields are selected via the list in the expression box, they are entered like this: [Leads Query]![City] and the query asks for my date parameter twice and produces an error in the text box holding the combined data. However, when I delete [Leads Query]! and just leave the [City] everything works and the date parameter is asked only once. What's different? Can you unconfuse me?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control Names (XP)

    |Post your SQL. Otherwise we're just guessing.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Names (XP)

    Charlotte,

    Here is the one that works: =Trim([City] & ", " & [State] & " " & [Zip])

    Here is the one that doesn't: =trim( [Leads Query]![City] & ", " & [Leads Query]![State] & " " & [Leads Query]![Zip] )

    The parameter that the query requests is a date. These are sales leads and I just want to print the ones I entered today (or whatever).

    So, when I use the expression that doesn't work , the query requests the parameter twice and I get an #ERROR. It doesn't matter whether the fields are from the query or the table.

    Is the above the information you requested?

    Thanks again.

    Peggy

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control Names (XP)

    I'm confused. I thought you were talking about a query, so where is the "=" coming in? Are these controls on a form or report or calculated fields in a query?
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Names (XP)

    The control is on the report. The query is the basis for the report. I copied the idea for my concatenation from the sample db and it used the =trim on the sample label report to combine city, state, zip. I'm sorry I wasn't clear. I don't know enough yet as to what information is needed to answer my question.

    Does this help clear things a bit?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control Names (XP)

    You aren't referring to fields in the query in the controlsource on the report, you're referring to fields in the report's recordset. Does that make it clearer? That's the reason you don't use the table/query name when concatenating the fields. Does that help?
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Names (XP)

    Let's see if I have this straight. The data is coming from the results of running the query? The results the report is going to print?

    If that is correct, then I'm still curious as to why when I used the [leads query]![city] etc that it forced the query to ask for my date parameter for a 2nd time.

    I'll understand if it's "just because" <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>

    Thanks for the info. Still trying to bend my brain to the way Access thinks.

    Peggy

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control Names (XP)

    Sort of. The report has a data source, which happens to be the query. The report's controls don't refer to the query, they refer to the fields in the report's recordsource.

    I can't explain the date parameter, because I have no idea whether you have such a thing in your underlying query or if this has something to do with the report itself. However, if you have an error, then it will trigger over and over for each record.
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Names (XP)

    Okay. But *what* is the recordsource? The underlying table? The field names in the top row of the query? Obviously the terminology has my stymied, darn it!

    Thanks for your patience.

    Peggy

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Control Names (XP)

    The recordsource is a table or query or select statement. The fields, however, belong to the report, not to the underlying query or table. In the design view of the report, you have a fields list that shows the fields available in its recordsource.
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Names (XP)

    Ah HA! <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> . That's why when I had the [leads query]![city] the parameter ran twice. I was pulling information from the query which had already run once when I started the report and then with the extra identifier it had to run again since I was asking information of the query. So the information available in the recordsource of the report doesn't need further identification.

    I think I have it now.

    Whew. I was wondering if I'd ever get it. Your explanations were great! Thanks.

    Peggy

Posting Permissions

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