runtime error 94 (ignore or skip if null)

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11276-runtime-error-94-ignore-skip-if.html
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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11276
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,

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"JNT" wrote in message
news:39C56CF5-AAC9-4939-BC3C-9A74B7F18604@microsoft.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11276
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11276
use
> of null).
>
> How do I go about ignoring that code?
>
> Thank you,