Access 2010 Drill Down

I'm using the code below to drill down to another form, however I get a run-time error 3075 In operator () in query expression [Projected RMFP Submission Date] In; when the value IS Null. The error occurs in: If DCount("*", "UFMS Weakness Remediation Summary", "[Projected RMFP Submission Date] In " & strList) = 0 Then....Any thoughts.

Dim strList As String
If Me.Projected_RMFP_Submission_Date & "" <> "" Then
strList = "('" & Replace(Me.Projected_RMFP_Submission_Date, " ", "','") & "')"
strList = Replace(strList, "'", "#")
 End If
 If DCount("*", "UFMS Weakness Remediation Summary", "[Projected RMFP Submission Date] In " & strList) = 0 Then
 MsgBox "No related record found", vbExclamation, "UFMS"
      Exit Sub
  Else
 End If
 DoCmd.OpenForm "frm_Remediation Summary", , , "[Projected RMFP Submission Date] In " & strList
shieldscoAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try this

Dim strList As String
  If Me.Projected_RMFP_Submission_Date & "" <> "" Then
  strList = "('" & Replace(Me.Projected_RMFP_Submission_Date, " ", "','") & "')"
  strList = Replace(strList, "'", "#")
 
   If DCount("*", "UFMS Weakness Remediation Summary", "[Projected RMFP Submission Date] In " & strList) = 0 Then
   MsgBox "No related record found", vbExclamation, "UFMS"
        Exit Sub
    Else
   End If
 
     DoCmd.OpenForm "frm_Remediation Summary", , , "[Projected RMFP Submission Date] In " & strList

 
  Else
   
 

 

   If DCount("*", "UFMS Weakness Remediation Summary", "[Projected RMFP Submission Date] is null") = 0 Then
   MsgBox "No related record found", vbExclamation, "UFMS"
        Exit Sub
    Else
   
         DoCmd.OpenForm "frm_Remediation Summary", , , "[Projected RMFP Submission Date] Is Null"

   End If

 End If
0
 
hnasrCommented:
You may have a default List to use if the strList evaluates to Null

For example:

defList = "(9991, 9992)" ' or any similar to strList but with values other than required ones, so expression will evaluate to 0.

Then modify,

In " & Nz(strList, defList) ) = 0 Then
0
 
Rey Obrero (Capricorn1)Commented:
the  End If is in the wrong place

Dim strList As String
 If Me.Projected_RMFP_Submission_Date & "" <> "" Then
 strList = "('" & Replace(Me.Projected_RMFP_Submission_Date, " ", "','") & "')"
 strList = Replace(strList, "'", "#")

  If DCount("*", "UFMS Weakness Remediation Summary", "[Projected RMFP Submission Date] In " & strList) = 0 Then
  MsgBox "No related record found", vbExclamation, "UFMS"
       Exit Sub
   Else
  End If
  DoCmd.OpenForm "frm_Remediation Summary", , , "[Projected RMFP Submission Date] In " & strList


  End If      '<< should be here
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
or do it this way

Dim strList As String
  If Me.Projected_RMFP_Submission_Date & "" <> "" Then
  strList = "('" & Replace(Me.Projected_RMFP_Submission_Date, " ", "','") & "')"
  strList = Replace(strList, "'", "#")
 
  Else
 
  MsgBox "You need a date on Projected_RMFP_Submission_Date"
 
  Me.Projected_RMFP_Submission_Date.SetFocus
 
  Exit Sub

  End If

   If DCount("*", "UFMS Weakness Remediation Summary", "[Projected RMFP Submission Date] In " & strList) = 0 Then
   MsgBox "No related record found", vbExclamation, "UFMS"
        Exit Sub
    Else
   End If
   DoCmd.OpenForm "frm_Remediation Summary", , , "[Projected RMFP Submission Date] In " & strList
0
 
shieldscoAuthor Commented:
Rey - it did not return the null records
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
AFAIK, Dcount() does not support the In() clause in the criteria.

I could never get it to work in Access 2003, 2007, 2010, or 2013.

You will need to open a recordset to get the count is you want to use the SQL IN() clause

Untested example:
Dim strList As String

Dim rs as DAO.recordset
Dim lngCount as Long


 If Me.Projected_RMFP_Submission_Date & "" <> "" Then

 strList = "(#" & Trim(Replace(Me.Projected_RMFP_Submission_Date, " ", "#,#")) & "#)"
 
set rs = CurentDB.OpenRecordset("Select *from [UFMS Weakness Remediation Summary] where [Projected RMFP Submission Date] In " & strList & ";")

lngCount  = rs.recordcount
rs.close
set rs = nothing



  If lngCount  = 0 Then
  MsgBox "No related record found", vbExclamation, "UFMS"
       Exit Sub
  
  End If

  DoCmd.OpenForm "frm_Remediation Summary", , , "[Projected RMFP Submission Date] In " & strList 

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
<Rey - it did not return the null records >
sorry, I misread your question..

you have to use different code  to show the null records..
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@Rey,

Have you tried using the IN() clause in the criteria of the DCount().  Can you get it to work?
0
 
Rey Obrero (Capricorn1)Commented:
@Boyd
yes, it works.
0
 
shieldscoAuthor Commented:
Works great Rey.. Thanks
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Interesting... I can't get it to work in Access 2003, 2007, 2010, or 2013.  

Maybe the issue I am having getting it to work is the same as shieldsco.

Would you mind posting working example code based on the North wind database that I could try.
0
 
Rey Obrero (Capricorn1)Commented:
@Boyd

here is a Northwind db A2010
Database4.accdb
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@Rey,

Thanks for the example.  Your example works perfectly. I am not sure why my testing failed.

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.