Fine tune an Access db query

[ChewDoggie]

I have the following shaped query to print some labels. However, some of the records are incomplete and don't have an ADDRESS and/or CITY and/or STATE and/or ZIP in the table. Does anyone know of a way I can fine tune this query so that it ignores these incomplete records? Here's what the query looks like now:

sql = "SHAPE {SELECT eventid FROM localseries where seriesid = " & ThisSeriesID & "}"
sql = sql & " APPEND ({select eventid, fname + ' ' + lname as fullname, addr, city + ', ' + state + ' ' + zip as citystatezip"
sql = sql & " from racer INNER JOIN registration on racer.racerid = registration.racerid where registration.eventid = "
sql = sql & Trim(rs!eventID) & " and registration.status = 'Y' order by state, zip, city} as rsRacerData RELATE eventid to eventid)



Many Thanks!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[PHV]

In JetSQL I'd use this where clause:
WHERE (addr + city + state + zip) Is not Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ChewDoggie]

Thanks PHV !!!

Problem there is that if the addr is missing but the rest (city, state, zip) isn't, then it's "not null" and it's returned.

As it turns out, I modified the query to grab each item (city, state, zip) individually and tested the data before I printed it and it looks like it worked out. HEre's the code:

Do While Not rsRecords.EOF
    Set rsracerdata = rsRecords("rsRacerData").Value
    If Not rsracerdata.EOF Then
        Do While Not rsracerdata.EOF
            If rsracerdata!FullName = "" Or rsracerdata!addr = "" Or rsracerdata!city = "" Or rsracerdata!State = "" Or rsracerdata!zip = "" Then
                rsracerdata.MoveNext
            Else
                If cnt = 0 Then
                    rsLabel.AddNew
                End If
                rsLabel.Fields(cnt).Value = rsracerdata!FullName
                cnt = cnt + 1
                rsLabel.Fields(cnt).Value = rsracerdata!addr
                cnt = cnt + 1
                rsLabel.Fields(cnt).Value = rsracerdata!city & ", " & rsracerdata!State & " " & rsracerdata!zip
                If cnt Mod 8 = 0 Then
                    rsLabel.Update
                    cnt = 0
                Else
                    cnt = cnt + 1
                End If
                rsracerdata.MoveNext
            End If
        Loop
    Else
        rsEvents.AddNew
        rsEvents!eventID = rsRecords!eventID
        rsEvents.Update
    End If
    rsracerdata.Close
    rsRecords.MoveNext
Loop

Thanks for you help though...it's appreciated!





Many Thanks!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[PHV]

Seems you prefer store ZeroLengthString instead of Null.