Solved

Access Run Time Error 2950

Posted on 2016-07-22
15
35 Views
Last Modified: 2016-07-26
Hi, I have a database that has a command button that opens a report. When I click on the command button, I am prompted to enter a date from and to (for example 1/5/2016 to 1/30/2016. I am getting a message error # 2950 and/or a message that that the expression is typed incorrectly or that it is to complex to be evaluated. I tried moving the database to a trusted location but this did not work. Any thoughts?
0
Comment
Question by:Conernesto
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The query is probably the problem.  Try running the query instead of the report.  If you still get the message,  modify the query to hard code the dates and run it.  Do you still get the error?

Post the SQL
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Pertaining to Error 2950:

1. Moving the DB to a trusted location Is good.  Let me ask the obvious,  does your user name have write permissions to the folder? It probably does, but you may want to check it just the same.

2. Which version of MsAccess are you?

3. Is your [Open Report] command button, calling a Macro or is it calling VBA program code?
0
 

Author Comment

by:Conernesto
Comment Utility
The query does open. I have access version 2010. My command button is calling a Macro. If I try to open the report without the command button, I get the same error. I don't know if my user name has a write permissions folder.
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Some people have had success rectifying error 2950 by converting he macro to VBA code.

Courtesy of Office.com:
https://support.office.com/en-us/article/Get-started-with-Access-programming-92eb616b-3204-4121-9277-70649e33be4f

Click on the "Convert macros to VBA code" hyperlink.

Courtesy of Office-Forums
http://www.office-forums.com/threads/solved-in-access-2010-convert-macros-to-visual-basic-is-grayed-out.2085243/

"The solution I found was, rather than loading the form in Design View,
I clicked the Macro button under Create on the Ribbon which brought up
the Macro Design window. Under the Action Catalog window, there is a
section called "In this Database." I clicked down to the form and
textbox which contained the macro I wanted to convert, then double-
clicked. The macro displayed in the left hand window and the "Convert
Macro's to Visual Basic", was not grayed out so I clicked it and the
conversion was simple after that. Thought I'd pass this along to
others who may have had this frustration. "
0
 

Author Comment

by:Conernesto
Comment Utility
I clicked the Macro button under Create on the Ribbon and selected to convert macro to vba. I got the message there is nothing to convert. I get the same error message if I bypass the command button and try to open the report directly. Do I need to recreate the form?
0
 

Author Comment

by:Conernesto
Comment Utility
I meant so say recreate the report.
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Please zip and attach your database to this private message so we can have a look see at what is going on.

If the zip file is to large to attach here, then please email attach it and send to wsh2gaf@gmail.com.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
I would suggest making a form with a combobox for selecting a date, filled with Date values from the appropriate table.  This eliminates the possibility of errors when entering dates free-form.  Then filter the report's record source query by the selected date, either by saving it to a custom database property and using GetProperty in the query, or (less reliably) referencing the combo box on the form directly.  Here is some code for creating and retrieving custom database properties.

Option Compare Database
Option Explicit

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties


Public Sub SetProperty(strName As String, lngType As Long, _
   varValue As Variant)
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 11-Oct-2014
'Called from various procedures

On Error GoTo ErrorHandler

   'Attempt to set the specified property
   Set prps = CurrentDb.Properties
   prps(strName) = varValue

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
    If Err.Number = 3270 Then
      'The property was not found; create it
      Set prp = CurrentDb.CreateProperty(Name:=strName, _
         Type:=lngType, Value:=varValue)
      CurrentDb.Properties.Append prp
      Resume Next
   Else
   MsgBox "Error No: " & Err.Number _
      & " in SetProperty procedure; " _
      & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Public Function GetProperty(strName As String, strDefault As String) _
   As Variant
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 11-Oct-2014
'Called from various procedures

On Error GoTo ErrorHandler
   
   'Attempt to get the value of the specified property
   GetProperty = CurrentDb.Properties(strName).Value

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3270 Then
      'The property was not found; use default value
      GetProperty = strDefault
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in GetProperty procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Public Function ListCustomProps()
'Created by Helen Feddema 3-Oct-2006
'Modified by Helen Feddema 3-Oct-2006
'Lists DB properties created in code (as well as built-in properties)

On Error Resume Next
   
   Set dbs = CurrentDb
   Debug.Print "Database properties:"
   
   For Each prp In dbs.Properties
      Debug.Print vbTab & prp.Name & ": " & prp.Value
   Next prp

End Function

==================================
Usage examples:

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private lngDataType As Long
Private strPropertyName As String
Private strPropertyValue as String
Private varPropertyValue As Variant

Date
====
   If IsDate(Me![txtStartDate].Value) = False Then
      strTitle = "Invalid date"
      strPrompt = "Please enter a valid start date"
      Me![txtStartDate].SetFocus
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   Else
      dteStart = CDate(Me![txtStartDate].Value)
   End If
   
   strPropertyName = "PropName"
   lngDataType = dbDate
   Call SetProperty(strPropertyName, lngDataType, dteStart)

   GetStartDate = CDate(GetProperty("PropName", ""))

Text
====
   strPropertyName = "PropName"
   strPropertyValue = CStr(cbo.Value)
   lngDataType = dbText
   Call SetProperty(strPropertyName, lngDataType, _
      strPropertyValue )

   strDocsPath = GetProperty("PropName", "")

Long
====
   strPropertyName = "PropName"
   lngDataType = dbLong
   Call SetProperty(strPropertyName, lngDataType, lngID)

   lngID = CLng(GetProperty("PropName", ""))

Integer
=======
   strPropertyName = "PropName"
   lngDataType = dbInteger
   Call SetProperty(strPropertyName, lngDataType, intMonth)

   intID = CInt(GetProperty("PropName", ""))

Boolean
=======
   strPropertyName = "PropName"
   lngDataType = dbBoolean
   Call SetProperty(strPropertyName, lngDataType, "True")

   blnValue = CBln(GetProperty("PropName", ""))


Saving to a custom property from a control's AfterUpdate event
==============================================================
Private Sub txtDate_AfterUpdate()
'Created by Helen Feddema 2-Sep-2009
'Last modified 2-Sep-2009

On Error GoTo ErrorHandler
   
   If IsDate(Me![txtDate].Value) = True Then
      dteSingle = CDate(Me![txtDate].Value)
      strPropertyName = "SingleDate"
      Call SetProperty(strName:=strPropertyName, _
         lngType:=dbDate, varValue:=dteSingle)
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window


Also see my Filter by Form sample database:

http://www.helenfeddema.com/Files/accarch248.zip

Here is a screen shot of the form:

Filter form
0
 

Author Comment

by:Conernesto
Comment Utility
Attached is a copy of my database (db). The db will open to a MenuReports_frm. There are two command buttons that will prompt you for a date. If I enter dates "5/1" to "5/31" the report opens. If I enter "6/1" to "6/30", I get the error message.

Thank you for looking into this.
Copy2Sales-and-Use-Tax-Database.zip
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I played with this for an hour.  There seems to be some corruption in the report. I rebuilt the query, I exported and reimported the data.  Neither fixed the problem.  The query works fine but the report fails when certain records are selected.

I would start again.  This time, use best practices naming standards and don't use macros.  Also rather than prompting for the date range, add two controls to the form and reference those.  That will allow you to add code to the button click event to ensure that a valid range was selected.
0
 

Author Comment

by:Conernesto
Comment Utility
What macros are you referring to? How would I add two controls?
0
 
LVL 14

Accepted Solution

by:
wsh2 earned 500 total points
Comment Utility
Thank you for sending your database.. {smile}

Your SQL query is not testing for a zero [Taxable %] or a zero [Sales Tax Rate] value, Since you are performing calculations that use these fields as a divisor, MsAccess is encountering a divide by zero error when the query executes.

To correct this you want to use an IIF clause in your SQL statement which should look like the following:
SELECT [Sales Tax HR_tbl].[Pay Period End], 
       [Sales Tax HR_tbl].[Work Locn City], 
       [Sales Tax HR_tbl].[State], 
       [Sales Tax HR_tbl].[Location], 
       [Sales Tax HR_tbl].[Unit], 
       [Sales Tax HR_tbl].[Line Of Bus], 
       [Sales Tax HR_tbl].[Sum Amount], 
   IIF([Taxable %]<>0,	
       ROUND([TaxableSales]/[Taxable %]*100,2),
       0) AS [GAmount],
       [Sales Tax HR_tbl].[Taxable %], 
   IIF([Sales Tax Rate]<>0,	
       ROUND([Sales Tax]/[Sales Tax Rate],2),
       0) AS [TaxableSales], 
       [Location HR_tbl].[Sales Tax Rate], 
 ROUND([TaxableSales]*[Sales Tax Rate],2) AS [Tax], 
       [Location HR_tbl].[JurisdictionCode], 
       [Sales Tax HR_tbl].[Use Amount], 
 ROUND([use amount]*[sales tax Rate],2) AS [Use Tax], 
       [Sales Tax HR_tbl].[AdvancedPymt], 
       [Location HR_tbl].[Sort Jur Code], 
       [Sales Tax HR_tbl].[Sales Tax]
  FROM [Sales Tax HR_tbl] 
 INNER JOIN 
       [Location HR_tbl] 
    ON [Sales Tax HR_tbl].[Location] = [Location HR_tbl].[Location]
 WHERE (
       [Sales Tax HR_tbl].[Pay Period End] 
       BETWEEN [Enter Begin Date 6/15/09] 
       AND [Enter Ending Date]
       ) 
   AND [Sales Tax HR_tbl].[State]="NY"
;

Open in new window

I have taken the liberty to update your database with the corrected SQL.

Enjoy!
Sales-and-Use-Tax.zip
0
 

Author Comment

by:Conernesto
Comment Utility
I can not thank you enough. So thank you. I will be more careful with these calculations.

ConErnesto
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Can you please return to this question and either close it out or post any additional problems you may be having.

Thank you.
0
 

Author Closing Comment

by:Conernesto
Comment Utility
Thank you very much.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now