runtime error 94 (ignore or skip if null)

J

JNT

tblMarketplace:
column name: Marketplace HOU_CCC ATL_CCC
field value Bay Area CEN Atlanta, Ga
DesertSW GLD FLD
Mountain West Houston, Tx
No Central Cal
Pacific Nw
So California

VB CODE:

qry = "SELECT tbltopmarketplace.* FROM tbltopmarketplace WHERE
((tbltopmarketplace.[CSC COD])=F_aging2()) AND ((tbltopmarketplace.[Key
Indicator]) IS NULL);"


db.QueryDefs.Delete "qryexportTopAccounts"
Set qdf = db.CreateQueryDef("qryexportTopAccounts", qry)

tbl = "tblmarketplace"
myexportfile = "C:\My Documents\Top HOU CCC Key.xls"
Set db = CurrentDb
Set rst = db.OpenRecordset(tbl)
rst.MoveFirst
Do Until rst.EOF
vaging2 = rst.Fields("HOU_CCC")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryexportTopAccounts", myexportfile, True, vaging2
rst.MoveNext
Loop
rst.Close

Set rst = Nothing
Set db = Nothing

Exit_cmdRunQuery_Click:
Exit Sub


Err_cmdRunQuery_Click:
If Err.Number = 94 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdRunQuery_Click
End If

I am trying to export out into excel by "HOU_CCC" field in tblmarketplace.
Because there are blank fields it gives me the runtime error 94 (invalid use
of null).

How do I go about ignoring that code?

Thank you,
 
D

Douglas J Steele

If you want to skip those rows that don't have a value, try:

Do Until rst.EOF
If Not IsNull(rst.Fields("HOU_CCC")) Then
vaging2 = rst.Fields("HOU_CCC")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryexportTopAccounts", myexportfile, True, vaging2
End If
rst.MoveNext
Loop
 
Top