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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

shieldscoAuthor Commented:
Rey - it did not return the null records
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
@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 MVPDesigner and DeveloperCommented:
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 MVPDesigner and DeveloperCommented:
@Rey,

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

Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.