Runtime error because of null criteria but...

[connie]

I have a form with 6 dropdown fields that feeds into a query. I want the query to be exported to Excel if the user selects that option via a command button. I am however getting the error:

Run-time error '3061':
Too few parameters. Expected 6.

I'm assuming this is because the form itself has 6 fields and I only tested a value in 1. However I want the user to be able to select as many or as little fields as desired and I have the query written to return everything for the fields that are Null via criteria in each of those fields as:

IIf([Forms]![frmBuildCustomReport]![cboContractor] Is Null,"*",[Forms]![frmBuildCustomReport]![cboContractor])

etc. So I'm not sure why the error is occurring. Do I also need something in the VBA like For Each Parameter If IsNull prm.Value Then "*"...?
Here is my code:

Dim dbs As DAO.Database
    
    Dim rstGetRecordSet As Recordset
    
    Dim objXL As Object
    Dim objCreateWkb As Object
    Dim objActiveWkb As Object
    
    Set dbs = CurrentDb
    Set objXL = CreateObject("Excel.Application")
    Set objCreateWkb = objXL.Workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook
    
    objXL.Visible = True
    objActiveWkb.Sheets.Add
    objActiveWkb.Worksheets(1).Name = "Penetrations"
    
    Set rstGetRecordSet = dbs.OpenRecordset("qryBuildCustomReport")
    
    objActiveWkb.Worksheets("Penetrations").Cells(1, 1).Value = "Test"
    objActiveWkb.Worksheets("Penetrations").Cells(2, 1).CopyFromRecordset rstGetRecordSet
    
    Set objActiveWkb = Nothing
    Set objCreateWkb = Nothing
    Set objXL = Nothing
    rstGetRecordSet.Close
    dbs.Close
    Set rstGetRecordSet = Nothing
    Set dbs = Nothing
    DoCmd.Close acForm, "frmBuildCustomReport"

Thank you SO much for any help!!

 

[Steve R.]

IIf([Forms]![frmBuildCustomReport]![cboContractor] Is Null,"*",[Forms]![frmBuildCustomReport]![cboContractor])/QUOTE]

As a start, the code above is incorrect. Also you may want to explore the NZ function.

IIF(isnull(FieldName), TruePart, FalsePart)

 

[connie]

IIf([Forms]![frmBuildCustomReport]![cboContractor] Is Null,"*",[Forms]![frmBuildCustomReport]![cboContractor])/QUOTE]

As a start, the code above is incorrect. Also you may want to explore the NZ function.

IIF(isnull(FieldName), TruePart, FalsePart)

Thanks. I did change those criteria as recommended....however I still get a blank Book1 open up in Excel and Access pops the same error message up.

 

[Brianwarnock]

This
IIf([Forms]![frmBuildCustomReport]![cboContractor] Is Null,"*",[Forms]![frmBuildCustomReport]![cboContractor])

should be

[Forms]![frmBuildCustomReport]![cboContractor] Is Null) or ([Forms]![frmBuildCustomReport]![cboContractor])

Brian

 

[connie]

This
IIf([Forms]![frmBuildCustomReport]![cboContractor] Is Null,"*",[Forms]![frmBuildCustomReport]![cboContractor])

should be

[Forms]![frmBuildCustomReport]![cboContractor] Is Null) or ([Forms]![frmBuildCustomReport]![cboContractor])

Brian

Thank you. I just tried your suggestion but still get Run-time error '3061' Too few parameters. Expected 6.

 

[Brianwarnock]

I was attempting to correct the syntax for a query however given your error you need to read

http://support.microsoft.com/kb/210244

Brian

 

[connie]

Thanks...wow that's complicated. It's also the troubleshooting for a parameter query, which mine isn't. All I want to be able to do is have the query exported to Excel with a command button...is there a simpler way to do it? I want the spreadsheet just to open with the query data pasted in, not save it anywhere or specify file paths or anything