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;