Results 1 to 9 of 9
  1. #1
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82

    Seemingly random entries are coming up as "#Error"

    I have a query. the query calls a function I wrote. In datasheet view, this query gives me exactly what I want for about 98% of entries, but a handful display as "#Error" and I cannot figure out why, as I can see no difference between the records that generate the errors and those that don't.

    What some of the results looks like:

    ArtistID OriginalName
    660 #Error
    497 #Error
    498 Bach; Carl Philipp Emanuel
    499 Bach; Johann Sebastian
    500 Bach; P.D.Q.
    501 Barber; Samuel


    The query:
    Code:
    SELECT Artists.ArtistID, ConstructName(Artists.FirstName, Artists.LastName, Artists.BandName) as OriginalName
    FROM Artists
    WHERE (((Artists.Instrument_id)=6))
    ORDER BY Artists.OriginalName;
    The Function:


    Code:
    Public Function ConstructName(first As String, last As String, band As String)       
        ' make sure there are no spaces at the ends
        first = Trim(first)
        last = Trim(last)
        band = Trim(band)   
       
        If (Len(first) > 0) Then
            first = "; " & first
        End If
       
        If (Len(band) > 0) Then
            band = " - " & band
        End If
       
        ConstructName = last & first & band
    End Function
    ArtistID FirstName LastName BandName OriginalName DateBorn Instrument_id
    494
    ZZ Top
    ZZ Top
    5
    496 Graham Nash Crosby; David Nash; Graham -- Crosby; David
    5
    497 Larry Austin
    Austin; Larry
    6
    498 Carl Philipp Emanuel Bach
    Bach; Carl Philipp Emanuel
    6
    499 Johann Sebastian Bach
    Bach; Johann Sebastian
    6
    500 P.D.Q. Bach
    Bach; P.D.Q.
    6
    501 Samuel Barber
    Barber; Samuel
    6
    502 Bela Bartok
    Bartok; Bela
    6


    I thought perhaps there was some weird character in some records, so I tried clearing then re-entering one record but it didn't do anything. Any ideas on what this would be, or how to track down the issue? Weirdly enough, I use a very similar query in a different form and it works fine:

    Code:
    SELECT Artists.ArtistID, ConstructName(Artists.FirstName,Artists.LastName,Artists.BandName) AS OriginalName, Artists.Instrument_id
    FROM Artists
    WHERE (((Artists.Instrument_id)=5))
    ORDER BY Artists.OriginalName;

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You may need to account for Nulls. Try:
    (Len(band & "") > 0)

    ...etc.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I agree, and you'd also have to declare the variables as Variant rather than String.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    first and last are not actually reserved words but are relevant to SQL. So a bit of a longshot, but try changing the names in the function parameters to say fFirst, fLast.

    Also your function should really be typed as string, at the moment it is returning a variant which 'should' be interpreted as string but may not be. In Access sql, the semi colon is used to indicate end of a sql statement and it may be (again another longshot) that as a variant this is confusing access.

    what happens if you did the whole thing in sql

    trim(lastname) & Iif(trim(nz(firstname,""))="","",";") & trim(firstname) & Iif(trim(nz(bandname,""))="",""," - ") & trim(bandname)

  5. #5
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    None of the suggestions on the VBA code worked, but doing the whole thing in sql does; I thought there might be a way but since I know vba slightly better than sql I did the lazy-way-that-turned-out-to-be-more-work-than-expected.

    I do have one issue left, which is sorting. Right now I'm sorting on a field in the Artist table that I'd like to remove from it altogether (from when I had a single field for the whole name, instead of separate fields for each part. But right now I need that field for sorting, because apparently I can't sort by naming the constructed name and sorting on that, like this:
    Code:
    SELECT Artists.ArtistID,  trim(Artists.LastName) & Iif(trim(nz(Artists.FirstName,""))="","",";") & trim(Artists.FirstName) & Iif(trim(nz(Artists.BandName,""))="",""," - ") & trim(Artists.BandName) as FullName
    FROM Artists
    WHERE (((Artists.Instrument_id)=6))
    ORDER BY FullName;
    This, of course, was also an issue when I was using vba, but I didn't want to tackle that lesser issue until I figured out the greater one. Is there some way to sort on my constructed full name?



    Quote Originally Posted by Ajax View Post
    first and last are not actually reserved words but are relevant to SQL. So a bit of a longshot, but try changing the names in the function parameters to say fFirst, fLast.

    Also your function should really be typed as string, at the moment it is returning a variant which 'should' be interpreted as string but may not be. In Access sql, the semi colon is used to indicate end of a sql statement and it may be (again another longshot) that as a variant this is confusing access.

    what happens if you did the whole thing in sql

    trim(lastname) & Iif(trim(nz(firstname,""))="","",";") & trim(firstname) & Iif(trim(nz(bandname,""))="",""," - ") & trim(bandname)

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You cannot use an alias in an Order By clause but I'm pretty sure you *can* use the original construct that created the alias.

  7. #7
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    You're right, you can. Thanks.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're most welcome. Are you ready to mark this thread as Solved yet?

  9. #9
    cherold is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    82
    I didn't realized I could mark things solved. Thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Suppress "Error" message following "Cancel = True"
    By GraeagleBill in forum Programming
    Replies: 7
    Last Post: 03-23-2014, 05:40 PM
  3. Replies: 7
    Last Post: 08-14-2013, 03:57 PM
  4. Replies: 1
    Last Post: 03-14-2013, 12:39 PM
  5. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums