General :: Textbox Displaying Result Of Sum Function In Scientific Notation
Apr 3, 2014
I am maintaining an Access 2003 application that is running on Windows 7 64Bit Enterprise OS setup.
I have a form in the application that displays a number of records with each record having a unique id and a field called ClockHrs which is stored as a Number (Long Integer) type.
I have a textbox that displays the sum of the ClockHrs field.
The textbox contains no code behind.
The Control source property of the textbox is set to '= SUM([ClockHrs])'
ClockHrs
10
10
30
15
10
15
25
===
115
The textbox displays the result of the sum 115 in scientific notation as 1.15 +02 .
View Replies
ADVERTISEMENT
Mar 4, 2013
I have a "Currency" field in a table and it holds large numbers (on which I will need to do basic arithmetic operations at some point, so I must store them as a numbers). I have set the "Standard" format on the TextBox used to display it on a form because I want to have thousand separators; I've also set "0" for decimals as I don't need to display them.
So everything displays as I want it even with the largest number that can be stored all the decimals are showing in the text field, but when I click on the field to edit the value instead of having the complete value it is displayed as scientific notation. I would like to display the complete value when editing it instead of the scientific notation, how can I achieve this, if it is possible?
A numeric example: If a user enters 1234567891012, the value displayed is indeed 1234567891012 but if they click on the field again the displayed value while editing is 1.23456789012E+11 (and it switches back to normal notation when the focus changes to another field). I would like to show 123456789012 all the time. I know that Access is capable of it most likely because if I set the Format to "Fixed" the values are always displayed completely (no scientific notation), but unfortunately I would like the thousands separators to show and it is not possible with the "Fixed" format.
Two last details, the scientific notation while editing does not kick in unless there is more than 11 digits in the number and the field width (and/or TextBox width) are sufficient to display up to 20 digits.
View 14 Replies
View Related
May 19, 2013
Have a query which looks up an address using the text input into a textbox by the user.
What I'm now struggling with is getting the query result into the textbox.
Am still new to access and am hoping this is something fairly simple.
View 2 Replies
View Related
Nov 24, 2014
I am building a Inventory Management Application for Tyre Shop. I have SaleMainTbl and SaleDetailTbl both used for preparing daily sale summary. I have Mainform based on SaleMainTbl with TxnDate and Total Amount (Sale) and the TxnDate is in one to many relation with SaleDetailTbl. FormSaleDetail is multiple row(continuous) form that makes billwise summary of each day having -TxnDate--BillNo--ItemSold--Company--Qunatity--Rate--Amount fields. I have inserted this form in FormSaleMainTbl.
So FormSaleMainTbl is Main form and FormSaleDetail is subform. TxnDate in FormSaleDetailTbl is automatically taken from SaleMainForm. I have further added text boxes in Main Form to show company wise sale for each day for which there is a query build one for each company that takes the currently loaded date from FormSaleDetail and calculates the Sale (Sum) of each brand (Company) of Tyres. All these objects are working very fine. However I have to close the MainForm and reopen it for result of query to appear in the appropriate text box in Main form.
Is there way to do this as soon as record is entered or at least at the end of completing the entry of each days sale transactions without closing the form. So the gist of the problem is realtime display of query result in text box on a form or updating the form screen immediately on updating any record or at the most after completing the updating of form but without having to close the form.
View 5 Replies
View Related
Dec 16, 2005
Hi all,
First timer here, so please forgive any daftness on my part. I'm a novice to access setting up my first database. This forun has been brilliant and helped me a great deal. However despite lots of searching I've not been able to sort out this problem.
I have a Form (New Client Details) with a primary key (ClientID), bound to a table (Client Details) and a Subform (Episode of Care Subform1) with a primary key (EpisodeofCareID) bound to another table (Episode of Care). They have a Master/Child link e.g. a client can have multiple episodes of care.
Using a search method I found on this forum I have created a search form (Client Search) which searches on First Name, Surname and Date of Birth. The search is operated by a command button (CmdSearch) with results shown in a Listbox (SelectSearchClientInfo). So the list could contain a number of entries for the same client if they have had multiple episodes of care.This works fine, however I also wanted to be able to select from the list and display all details for that selected record on the 'New Client Details' Form. I've used code found on this site but when I run it the form opens but will only display the first record for that particuler client. This is the code I've tried.
Can anyone help me out and show me whay I'm doing wrong?
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "New Client Details"
stLinkCriteria = "[ClientID]LIKE" & "'*'&" & "'" & Me.[ClientID] & "'" & "&'*'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, , , "[ClientID] = " & [Forms]![Client Search]![SelectSearchClientInfo], , acDialog
Many Thanks, This is such a good site!!
John
View 14 Replies
View Related
Aug 18, 2013
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
View 4 Replies
View Related
Jul 31, 2006
Hi Folks
More questions from the newbie :eek:
I have a very simple table with a field that will have only the values open or closed
I created a query based on this table and used the count function (to count open or closed cases) and in the next querie field I used the group by open.
This gives me a count of all open cases
I can do the same for closed cases.
I want this count to be displayed on a Switchboard form but i cant get my nehad round how to get it there.
Can anyone help me out ?
Many thanks
Jimmy
View 6 Replies
View Related
Jun 17, 2015
I am creating a driving school database and have four different tables. Student, Instructor, Lesson and Lesson Type.
In the footer of my subform which works out what lesson type the student has taken and from which instructor, I have created a calculation to multiply the number of hours a student does to what type of lesson they take.
=([LengthOfLesson]*[cost]) (This works ok)
I then want to add all of these options together. I have tried:
=sum([OverallTotal]) in the footer (This doesn't seem to work)
and then
=[Booking].[Form]![OverallTotal] to show it in the form
The name of the subform is correct (booking) and name OverallTotal is also correct but I keep getting #error message.
View 3 Replies
View Related
Jun 2, 2013
1 I have a delivery method column and I want to only show 1 type for each method. How would I do that? Is it in expression builder?
View 1 Replies
View Related
Dec 25, 2005
I have a SQL query in a string of a VBA procedure
(e.g. MyQuery = "select sum(Amount) from Investment")
and I want the result to be shown in a TextBox of my form Me.
I know several ways to run the query and to send the result into the TextBox (e.g. with QueryDef and RecordSet). However I guess Access should offer a more direct way to do such a simple operation.
Does anyone has a solution to do it the simplest way ?
Thanks.
View 7 Replies
View Related
Feb 2, 2006
Hi,
I have a form with a textbox and I want to fill that text box with the calculations from values of other text boxes. However when I put the equation
= Sum (box1 + box2 + box3) into the textbox's control source I don't get any results even though box1, box2 and box3 have values in there.
I also need the result to be saved in a field on my table, but I got no idea how to do it.
Please help!
Thank you
dfuas
View 2 Replies
View Related
Jun 14, 2005
I'm pretty sure there's a simple solution to this, yet I can't seem to find it.
I have a table that contains the Months of the year with an MonthID:
1|January
2|Febuary
...
I also have a form that contains a combobox with the Months.
When the user chooses the month, a query gets created, returning the MonthID.
I want to store the MonthID, in an invisible textbox, so that I can use it for my calculations.
If I use a textbox, I can't seem to display the result and if I use a listbox, I can't access that value.
How would I make it work?
This is the code used on the combobox:
Private Sub cbToMonth_Change()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("getMonthID")
strSQL = "SELECT Months1.MonthID " & _
"FROM Months1 " & _
"WHERE (((Months1.Month) In ('" & Me.cbToMonth.Value & "')));"
qdf.SQL = strSQL
Me.monthIDTo.Value = "getMonthID"
Set db = Nothing
Set qdf = Nothing
End Sub
View 3 Replies
View Related
May 8, 2013
I currently have a search form that when criteria is entered it then opens up a query with matching results. What i would like to have it do is have a macro that runs the query so that it gathers the results and then displays the results into a form with a textbox called search results.
View 2 Replies
View Related
Aug 5, 2013
I have a quote form that has a button which opens a simple search form with just one textbox which gives the results in a subform. (the search is for the company name and the subform results give the company name with the full address (company, add1, add2, town, county, postcode)
I would like to have a button on the search form, to copy the correct result into a textbox on the quote.Until now I have just had a cmbox on the quote with the companies and addresses listed. Unfortunately, the users are not checking this list to see if the company already exists and are adding a new company but with slightly different information, so I am getting multi companies. (i.e, smith ltd, smith limited, or Hants, Hampshire etc).
I need a button on the main search form that copies the company name from the search results subform and copies it into a textbox on the quote.I have tried this on a button but it doesn't like it:
Forms![quotes test].[company].Value = Me![COMBINED SEARCH subform].[company name]
View 1 Replies
View Related
Jul 30, 2013
I am designing a book collection database where each book can contain multiple authors. I used three tables; tblBooks, tblAuthors, tblBookAuthorJOIN. The tblBookAuthorJOIN allows me to create a subform with drop down combo boxes in the Books form linking multiple authors to each books indexed field. The Author table contains the usual FirstName, LastName, MiddleName fields. I would to be able to display the names of each author in the forms header but I am having difficulty.
For example a book may have two authors such as:
Authors table would contain values such as
Last Name: Grisham
First Name: John
Last Name: Twain
First Name: Mark
Textbox in header would display: John Grisham; Mark Twain
The authors would be displayed in the following format in the same order as listed in the subform datasheet.
Attached is a graphic of the form.
View 3 Replies
View Related
Apr 19, 2005
Anyone know an efficient way of setting the value of a textbox to the result of an sql query?
I am using the following, but it seems to be slow when populating a large form:
Make.RowSource = "SELECT BarcodeDATA.Make FROM (Customers RIGHT JOIN CustomerSales ON Customers.CustomerID = CustomerSales.CustomerID) LEFT JOIN BarcodeDATA ON CustomerSales.ItemNum = BarcodeDATA.ItemNum WHERE (((CustomerSales.ItemNum)=[Forms]![FormCheckConsignmentStatus]![ItemNum])); "
Make.Requery
Make.Value = Make.Column(0, 0)
Let me know if there is a better way to do this.
View 1 Replies
View Related
Mar 10, 2014
I have a form, which is bound to a table, with an unbound textbox.
I am trying to get the number of months an employee has/had worked.
In the table are two dates, Company Start Date, and Resignation Date.
There are three scenarios in which I need to calculate the months:No Resignation Date
A Resignation Date in the future
A Resignation Date in the Past
See attached PDF
The Employees are under a one-year contract which ends the day before the one-year anniversary.
I originally tried using DateDiff in the Control Source, but the IIF seamed too limited for my needs.
Code:
If [Resignation Date] = "" Then
If Month([Nichii Gakkan Start]) = Month(Date) Then
If Day([Nichii Gakkan Start]) < Day(Date) Then
MIS = DateDiff("m", [Nichii Gakkan Start], Date) - 1
[Code] ....
MIS = The name of the textbox in question.
View 6 Replies
View Related
Aug 25, 2015
I'm simply trying to display the data from an open form named frmEventInput fields: [EventName] and [Description] on a new form that includes 2 text boxes with the control sources and names: [txtEventName] and [txtDescription]
It says Error 2448, You can't assign a value to this object!
Private Sub Form_Load()
Dim LResponse As Integer
LResponse = MsgBox("Do you wish to open 'Event Participant Registration Form'?", vbYesNo, "Continue")
If LResponse = vbYes Then
DoCmd.GoToRecord , , acNewRec
[Code] ....
View 1 Replies
View Related
Nov 5, 2012
I need to add a condition to the already existing criteria:
The existing Criteria is: Count the rows where the value in a column is either "Yes" or "MayBe",
AND now I would like to add another condition:Check if in the same 'Report' tab, but a different column, the value is "Europe"
Essentially, I would like to count how many rows in Column 5 have the answer "Yes" or "MayBe" WHEN the Continent is "Europe"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=SUM(COUNTIF('Report'!R[1]C[5]:R[2498]C[5],""Yes""),COUNTIF('Report'!R[1]C[5]:R[2498]C[5],""MayBe""))"
View 3 Replies
View Related
Jun 14, 2013
I'm working on a project that uses a barcode scanner. The user will scan a barcode which will choose an item from a combo box. The result of what it enters will populate a text box. I have that part working fine.
The next step is to get it to open a form based on the value in the textbox. The textbox will have about 7 different possible values and each one should open a different form.
The way this is supposed to work:
User approaches a machine and scans the barcode. He is presented with a form giving him options based on the type of equipment the machine is. A compressor for example would present him with a set of options for compressors (gauge readings, maintanence, etc).
I have an unbound form with two controls:
cboAssetNumber
txtType
How to code this and which events to code it in. Users will be going from machine to machine and using the form over and over again so I need to somehow manage the clearing of the form to begin again with another barcode scan.
I saw another thread about a barcode scanner and one response was to program the scanner to send the barcode and then send an "Enter" press to move focus to the next control. I've looked through the documentation and haven't been able to find if that is possible. Right now it sends the number and that's it. It's a "Motion" Tablet.
View 14 Replies
View Related
Jan 29, 2008
Hi there !
I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet.
Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.
So I try this :
*here is my function
Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant
Dim requetteSQL As String
Dim rst As New ADODB.Recordset
requetteSQL = "SELECT libelle " _
& "FROM operation;" _
rst.Open requetteSQL, cnn
get_nom_operation = rst.GetRows
End Function
* and here is the code that call the function
Dim res As Variant
Set res = get_nom_operation(conn)
Dim i As Integer
'i = 1
For i = LBound(res) To UBound(res)
Set Obj = Me.Controls.Add("forms.CommandButton.1")
With Obj
.Name = "monButton" & i
.Object.Caption = res(0,i)
.Left = 14
.Top = 25 * i
.Width = 60
.Height = 20
End With
'ajout de l'objet dans la classe
Set Ge = New gere_event
Set Ge.CButton = Obj
Collect.Add Ge
i = i + 1
Next
But it doesn' work, and I don't know why...
The error doesn't show where the code is stoped, but only show the call to display this form.
someone could show me some way to find the solution please ?
Thanks a lot.
View 1 Replies
View Related
Apr 1, 2006
Hi there,
I've read through the forums on saving the results of a calculation into a field is a bad idea. I somewhat understand the reasoning for it. But I don't think such situations arise for everyone.
But I have gone with the suggestion and created an updatequery, which simply goes through and updates the calculated value into a table. Now each time the updatequery is run its asking for permission as to the fact if I'm sure I want it to be run. How do I turn this option off. And should I call this updatequery afterupdate or beforeupdate?
Thanks
View 3 Replies
View Related
Nov 30, 2004
It's written a Function which takes an Integer as Arg, runs a Select Statement and returns a String.
Such that the Control Source of the Forms reads....
=ConvertCompanyNumbers([12]) = where [12] is the Name of the field. Not My choice to have numbers as Fields.. but there it is.
Anyway.. I'm getting an #Name? error... also tried [Ctl12] but again same error.
I know the function works, as I can use something like =ConvertCompanyNumbers(12) and it displays the appropriate result.
What is up with this?
View 2 Replies
View Related
Oct 17, 2014
I have a form based on query. On form i am retrieving data from another table using DLookup in a unbound text box. So I want to save the result of DLookup function in another field/table on same form.
View 8 Replies
View Related
Feb 4, 2015
I am using the dcount function as the example I display below. The problem is that it returns the correct result (i.e. 59) but the data type is text (59 is on the left side) . I need this to be number.
=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")
View 3 Replies
View Related
Jul 10, 2014
I am currently working on a form. I used textbox to set the value of currency and sumif to total nett price for all of data with that specified currency.
How can I get the value of the textbox?
I use formula :
=Sum(IIf([Currency]="USD",[NettPrice],0))
And it's worked. It sums up all net price with USD currency
However, the currency types are so many and it's impossible for me to list them down one by one, so I use this formula :
=Sum(IIf([Currency]=[txtCurrenct],[NetPrice],0))
But it keep showing 0 result.
View 10 Replies
View Related
