Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pass Thru Query (Access XP / SQL 2000)

    Hi,

    i have a SQL server with an HR database and have created a view on the database as:
    SELECT p.staff_number
    , p.known_as as [Forename]
    , p.surname as [Surname]
    , p.start_date
    , p.leaving_date
    , p.Manager_Staff_Number
    , master.dbo.nz(m.known_as,' No details') as [MForename]
    , m.surname as [MSurname]
    , master.dbo.nz(p.location,' No details') as [Area]
    , master.dbo.nz(p.area, 'No details') as [Area2]
    FROM personnel_records p left outer join personnel_records m
    on p.manager_staff_number = m.staff_number

    when i create a pass thru query in Access i get the error
    The size of a field is too long.

    this error goes when i remove the three lines referring to my function master.dbo.nz

    can anyone suggest a way around having nulls returned in the recordset which affect the reports based on this query please?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass Thru Query (Access XP / SQL 2000)

    What about including m.known_as, p.location and p.area "as is" in the view, and using Nz in the control source of text boxes on the report?

    =Nz(m.known_as, "No details")

    If you need to sort or group the report on one of these fields, you can use such an expression in the Sorting and Grouping window too.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass Thru Query (Access XP / SQL 2000)

    good idea Hans but i get #Error when i put any sort of expression into the textbox on the report, any ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass Thru Query (Access XP / SQL 2000)

    Does this work?

    <code>=Nz([known_as],"No details")</code>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass Thru Query (Access XP / SQL 2000)

    unfortunately not.

    the known_as and surname fields are concatenated in one textbox as
    [MForename] & " " & [MSurname]
    and the NZ function doesnt work in any part of it.

    The area field is retuned as a textbox which has grouping based on it and setting the control source on the data tab to
    =nz([area],"No details")
    results in #Error when the report runs.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass Thru Query (Access XP / SQL 2000)

    Do you see area in the list of fields?

    If you have a text box with control source

    =Nz([area],"No details")

    that text box should NOT be named area, for that would cause a circular reference.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass Thru Query (Access XP / SQL 2000)

    How stupid do i feel now?


    VERY

    sorry Hans, forgot about the drag/drop onto a report creating controls with the field names. now renamed to txtArea etc and all is well.

    thanks

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pass Thru Query (Access XP / SQL 2000)

    Hi,
    Could you not just use:
    <pre>COALESCE(m.known_as,' No details')
    </pre>

    to convert the nulls in your view?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass Thru Query (Access XP / SQL 2000)

    Great, now i feel twice as stupid. [img]/forums/images/smilies/sad.gif[/img]

    OK, thanks for the 'SQL Functions you didnt know about' lesson!!!

    cheers Rory

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pass Thru Query (Access XP / SQL 2000)

    Hey, I know exactly how you feel... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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