Modules & VBA :: Number Rounding Error When Pulling Double From Recordset?
Jan 28, 2015
I have 2 similar numbers in a table with the following parameters:
Double, Fixed, DecPlaces = 2
One number (GLminor) = 0.10
Second Number (GLmaj) = 0.50
When i use the following code to create and pull the numbers from a recordset the (Gmin) is OK and displays as 0.1 but the (Gmaj) has a problem and displays as 0. So when i'm attempting to use it in an equation (OtherNumber) / (Gmaj) i'm getting a divide by zero error.
Code:
sSQL1 = "SELECT * FROM tblRScales WHERE ID = " & RScaleID
Set rs1 = CurrentDb.OpenRecordset(sSQL1, dbOpenDynaset)
rs1.MoveFirst
Gmaj = rs1!GLmaj
Gmin = rs1!GLminor
I've tried using CDec(OtherNumber) / CDec(Gmaj) and still get the same error.
As both numbers have the same properties in the table parameters i'm completely lost as to why one seems OK while the other seems to round down to 0.
For some reason I cannot enter decimal values without the number rounding up or down. I've already set it to 'Number' and 'double' and have also tried 'Fixed' set to 2 decimal places. Right now the only way it works is if I set it to 'Text'. However I need to do some calculations with these values eventually.
What's strange is I have another table in the same database that is NOT rounding and is working just fine.
I have been trying to convert string into double number format. I am running a SQL query in VBA that returns a double number format; however my understanding with SQL queries in VBA is that they return string only. The results are showing up perfectly fine when I run the query in the query editor; however when I try using the returned value in further calculations in VBA I keep getting a "Type Mismatch" error.
I haven't created classes in a while, and I don't see what is the problem
I'm modifying a function to get fractions from double. It worked well, but it returned a string.
I need it to be in parts, so I created this class
Code: Option Compare Database Public Entier As Integer Public nominateur As Integer Public denominateur As Integer Public Function getText() getText = Entier & " " & nominateur & "/" & denominateur End Function
I changed the function type and the assignation:
Code: Function GetFraction(ByVal Num As Double) As nombreEnFraction Set GetFraction = New nombreEnFraction If Num = 0# Then GetFraction = "None" Else Dim WholeNumber As Integer Dim DecimalNumber As Double
[Code] ....
For some reasons, when I get to the end of the function, I get a 91 error, like if it was nothing
But when I use a spy, I see values in the object until the end.
In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.
In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.
Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?
I"ve got a unbound combo box (customername) that has two events (on click); AfterUpdate and NotInList.
The AfterUpdate uses a 'me.recordsetclone' in order to display other data in controls associated with customerid/customername from the Customer table.
The NotInlist, does just that, adds a new customer name to the list along with a new autonumber (used as customerid).
The problem is that I'm getting two entries (different autonumbers). I can't figure out what I'm doing wrong. If I remove the 'AfterUpdate' event, then I can't bring up the customer's data, since then there's no connection to the customername/customerid.
If I remove the Not in List, then there's no way to add a new customer. I reconfigured NotInList many many times and still I get 2 entries. One entry has Customer 'name only', The second entry has name, address, city and the rest of the fields as entered.
I tried setting an index (on Customer table) with CustID and CustomerID, making them unique, but still I'm getting 2 entries whenever I use the 'NotInList'..........Help Help,,
Here are these events:
Code: ( text ) Private Sub customername_AfterUpdate() Dim rst As Object
Set rst = Me.RecordsetClone
rst.FindFirst "CustomerID=" & Me![customername] If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark Else 'Not found! End If Forms![Add or Delete Customer]![customername].Requery End Sub
Code: ( text ) Private Sub customername_NotInList(NewData As String, Response As Integer) Dim strSQL Dim FirstName As String Dim LastName As String Dim SpacePosition As Integer Dim lngNextID As Long
' Find the highest Cust ID in the Customers table and add 1 lngNextID = DMax("[CustID]", "Customers") + 1 custID = lngNextID
SpacePosition = InStr(NewData, " ") If SpacePosition = 0 Then MsgBox "Your entry requires a space to separate First and Last name." & _ vbNewLine & vbNewLine & _ "Please enter a First and Last Name or choose an entry from the list.", _ vbInformation, "Invalid Data !" Response = acDataErrContinue Exit Sub End If
' Trim the data into first and last name using the space position. FirstName = Trim(Left(NewData, SpacePosition - 1)) LastName = Trim(Mid(NewData, SpacePosition + 1))
If FirstName = "" Then MsgBox "You have not entered details for the first name" & vbNewLine & vbNewLine & _ "Please fix entry.", vbInformation, "Invalid Data !" Response = acDataErrContinue Exit Sub End If
If LastName = "" Then MsgBox "You have not entered details for the last name" & vbNewLine & vbNewLine & _ "Please fix entry.", vbInformation, "Invalid Data !" Response = acDataErrContinue Exit Sub End If
MsgBox "An account for this person does not exist....." & vbNewLine & vbNewLine & _ "Now creating new Customer Account.", vbInformation, "Unknown Customer Details....." strSQL = "Insert Into Customers ([custID], [FirstName], [LastName]) " & _ "values ('" & custID & "','" & FirstName & "','" & LastName & "');" 'MsgBox strsql CurrentDb.Execute strSQL, dbFailOnError Response = acDataErrAdded
I have a linked table to a DB2 database. this table contains key-pair values and has about 140k records.
I use a Sub to update the value of a specific record.
The sub starts by opening the needed DAO recordset Then it uses the rs.Findfirst method It checks if rs.Nomatch is not true (so the records exists!) Then it starts updating the record with rs.edit rs!value1 = myvalue1, rs!value2 = myvalue 2 rs.Update There is where I get the '3021 No current record' error
I use the same sub on the same table to update to different parts. One part works the other gives me the error.
I have a table that allows for data to be automatically imported into it or manually entered. When data is automatically entered into the table the number field shows the number as a decimal, but when I manually entered data into the column it rounds it up or down to the nearest whole number. I have checked the the decimal place setting on the particular item and it is set to allow for 2 decimal places the format is fixed and the number itself is Long integer
I have three tables the tblStudentInfo (holding the persons main data), the tblEducation (holding the students education records) and the refInstution (holding all university's and college information).I am pulling PersonID, name, etc from the tblStudentInfo, education records from tblEducation.
the problem is that in the query it displays the institution ID instead of the Institution name. How do i get the query/rpt to show the name instead of the ID?
On the form Institution this is pulled from the refInstitution table and the ID InstName etc is a combo box 0; 1.0; etc so the display on the form shows the name of the university instead of the ID number.
I am trying to pull a number from a text box. I listed the expression for the box below. [Current Record] is a number and Count(*) is a number. I couldn't figure out what field gave me the total records so I did a count instead. If I want to pull the value from just the count portion, what expression would do that?
I have created a sub form in a form that allows data entry for order details. I have a column that holds order amounts that automatically rounds up or down, and I don't want this. I have examined the back end table properties for this particular field, and the number field is set to general.
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode: ? me.fsubsrNavSHLBHL("NavSH_QQ") NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line: fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window: ? me.fsubsrNavSHLBHL.Form.RecordSource Select * from vsrNavigatorSHLBHL where Well_ID =91229
hey guys i have the following line in my query for removing seconds from a time/date stamp and all works great except one thing if the time is 10:02 it is showing as 10:2 anyway of getting it showing correctly?
Table 1: Account Number Start Date End Date Cost data** Budget data**
Table 2 (Imported excel file with cost/budget data): Account Number Cost data** Budget data**
Table 1 is the main table that will be viewable in this database. The idea here is that new Account Numbers can be added to Table 1 throughout the year. It then pulls the cost/budget data into Table 1 based on the matching Account Number between table 1 and 2.
So, if the Account Number (Table 1) = Account Number (Table 2) then it pulls the cost/budget data into the cells on that row. I am trying to make this automated since this data is updated weekly and imported into Table 2 from excel.
I would like a user to specify the total number of records they need from a query result. The result must be random, therefore cannot filter on any field or data in the table.
On a form, the user enters the total # of records they need and selects a button that executes this:
DoCmd.RunSQL.SELECT DcountRDP.RDP_IND, DcountRDP.GRP_ST_CD, DcountRDP.AUDIT_STATUS, DcountRDP.AUDIT_TYPE, DcountRDP.AUD_ID, DcountRDP.ACURT_IND, DcountRDP.AUD_DT FROM DcountRDP WHERE DcountRDP.MaxRecords = [Forms]![Open_Frm]![IL]
This is not working at all.
How can I make it possible for the user to enter a total needed or be prompted to enter a total and then randomly produce the first top # of records that they specify?
I am trying to check for when a user trys to enter a duplicate number.
The control that I am checking is in a subform on the main form:
Main: frmCandidateInfo Sub: frmTestInfo
Control on the subform is: RankOrder
I am trying to check the control entry against the table entry:
Table: tblTestEvents Field: RankOrder
Here is what I have:
Code: Private Sub RankOrder_BeforeUpdate(Cancel As Integer) Dim lngRankDup As Long lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!frmCandidateInfo!sfTestInfo!Form!RankOrder, 0)) If lngRankDup <> 0 Then MsgBox TestEventID & " already exists in the database" End If End Sub
I know that the error is due to the argument not being correct, but I am not sure how to fix it.
I'm having an issue with the database being read only and a type mismatch due to OpenRecordset. I know this database use to work when it when it was just
CurrentSerNum = RecSet(0) +1 was doing the incrementing.
I need the database to increment in a different way now.
Q=manufacture location 1st number is new=0 or used=1 2nd and third number =82 4th is single digit year aka 2015 = 5 6 and 7th are work week aka 25 for this week 8,9,10 are incremental numbers for the units made that workweek, so 001,002,003. Then week 26 starts the increment starts back at 001.
Code: Private Sub MakeThoseProducts_Click() On Error GoTo Err_MakeThoseProducts_Click Dim NumericSerial As Double Dim WorkWeek As String Dim WorkYear As String Dim WorkWeekNumber As Integer Dim WorkYearNumber As Integer Dim Location As String
I currently have a combo list on a purchase order form that has the following event procedure(s) in it so as to open the inventory window as needed to add/edit inventory.
Not in List: Private Sub Combo55_NotInList(NewData As String, Response As Integer) MsgBox "ERROR: ITEM not in list. Double-click this field to add an entry to the list." Response = acDataErrContinue End Sub
On Double-Click: Private Sub Combo55_DblClick(Cancel As Integer) On Error GoTo Err_Combo55_DblClick Dim lngCombo55 As Long
If IsNull(Me![Combo55]) Then Me![Combo55].Text = "" Else lngCombo55 = Me![Combo55] Me![Combo55] = Null End If DoCmd.OpenForm "INVENTORYITEMSform", , , , , acDialog, "GotoNew" Me![Combo55].Requery If lngCombo55 <> 0 Then Me![Combo55] = lngCombo55
This works GREAT on other forms I have however, on this particular form it gives me the following error:
ERROR: You have tried to assign the null value to a variable that is not a variable data type.
I have started from scratch several times....can anyone help? Is there anyway to add a button to the form to open the inventory form and then requery the combo box when closing the inventory form?
I want to write a email where there are 2 or 3 different ordernumbers for same email, i want to include the email in the mail part as single column table. how to do it? also can i use result of one recordset for other recordset?
the user searches records and they are filtered..http://www.londonheathrowcars.com/searchform.jpghttp://www.londonheathrowcars.com/searchquery.jpgand when they double click i want the record to open in this form..http://www.londonheathrowcars.com/showform.jpghttp://www.londonheathrowcars.com/showquery.jpgtrying to put this code on my listbox but gettin the openform cancelled 2501 error on the bold line.. anyone see why?Private Sub QuickSearch_DblClick(Cancel As Integer)Forms("searchform").SetFocusForms("searchform").RecordSource = vbNullStringDoCmd.OpenForm "showform", , , "[jobref] = " & Me![QuickSearch].Column(0)End Subnote:on the search form.. the form has no record source.. the listbox has the record source of the searchquery..also on the show form.. the form has the record source of the showquery.
I need to declare a variable (max_nog_leveren ) but that variable may not be rouded or only after 5 digits so as example 15,xxxxx
I am now using it as a "Integer" but that is not working very well..
Code: Dim iRet As Integer Dim strPrompt As String Dim strTitle As String Dim max_nog_leveren As Integer Dim strQuery As String Dim nettohoeveelheid_toevoegen As String
I'm trying to make a button on a form that pulls data from a table in a database on a network share to the current open database (with the form).
Database on network share info:DB location: est.com est estdb.accdb Table in DB: Table1
The current open database has the exact same table:Table2.In the following VBA line I get Runtime Error 3126: Invalid using of bracketing in name '' (The quotes at the end are 2x ')
Code: DoCmd.RunSQL "INSERT INTO Table2 SELECT [ est.com est estdb.accdb].Table1.* FROM [ est.com est estdb.accdb].Table1"
I tried everything with changing the [, " and even the path name of the external DB as variant of type string. Nothing seems to work.
