I'm trying to multiply the values of two number fields together, but it is not working. I used to be able to do this easily in earlier versions of Access. Here's the problem:
On the form, I need field A x field B to appear in field C. I've tried this as an expression in the build event function on the control, but it is not working. My code reads as follows: = (field A) * (field B).
I have a text box in a form that is producing a number from the query that the form is based on.In another Text Box i want to show the Value of the first Text Box multiplied by 3 because i am going to hide the first text box to only show the calculated number.
At the moment i have this in the control source of the text box [CountOfJobID]*3 but all i am getting is #####...I have set the format of the text box to General Number.
I am trying to create a clean database and code to generate a report.
I am trying to count the number of null fields in one of my queries:
However, because of this expression, I cannot carry other fields with it. So the end result looks like:
But I would really like it to spit out the following information:
Total Not Fixed: 241
Department: Sustaining Eng
is there a way to create an SQL query to simply add data: I have tried the following:
Code:
ALTER TABLE qrySustainingEngNotFixed2 ADD Dept TEXT(25)
Insert Into qrySustainingEngNotFixed2 (Dept) Values (Sustaining Eng)
SELECT TotalNotFixed, Dept
FROM qrySustainingEngNotFixed2;
The above isn't working. Keep in mind that I want this is just for display purposes. I pondered making a custom table and then making a Union Query, but I'm trying to do this all in one SQL statement.
Hi. I'm new to Access and I wonder if someone could help me with this problem I am having while constructing a database. We need to create a database of reservations to a program we offer. We will need to store the quantity of reservations per person. What we needed to do is multiply the quantity of reservations by the value of each reservation and get a 3rd field with the final amount. So we need a field with the result of the multiplication of the quantity of reservations by the value of each reservation, which is a fixed amount. I'm having problems getting access to do this, I get an "error" message on the resulting field. Can somebody help me with this?
Thanks.
How to multiply a group of records in an Table (eg. I have a table with tow fields Id and Value, how to multiply all Value records)
View 1 Replies View RelatedHi all, wondering if anyone can help me.
Im trying to a run a query to see the results of my table. but i am experincing problems, when use the SUM function.
I have constructed a query with three fields.
1) QTY.
2) Cost
3) Total.
what i would like is to be able to Multiply QTY & Cost to get the total.
so whan QTY Is 0 and Cost is £0.00. the query recongises it and changes the total should then be £0.00.
any advice on enabling the query is welcome.
In a nutshell, I have a form where the 'Record Source' is a table titled 't_02_0_Assets'. I have several fields in the table that have foreign key references that utilize the Lookup Combo Box display control to allow users to select from a drop down list in the form.
The issue I am having is that I can't filter the text in the foreign key fields (only the ID's ).
My attempted solution was to create a control on the form (text box) and bind it to each of the foreign key ID's using the DLookUp function and then reference this control in the filter code.
My question is... how do I reference this DLookUp textbox in my filter VbCode?
I have attached a '.jpg' image of various aspects of the form including the filter code on the 'On Change' event.
I had an existing database with 2329 records entered into it. All of the fields (220ish) were all in one table. Myself and my co-workers wanted to rebuild the database without losing the data. We wrote queries to transfer the data from the original database to the new database and split the data from the original 1 giant table to 9 smaller tables.
The transfer of data worked so I went to start making forms. When I went to add fields from different tables I had to built a relationship, which I did. All of the data that transferred over from the new database is in the form (now multiple forms linked by button) but I can't add new information. I get an error stating, "You cannot add or change a record because a related record is required in table ..." and the table referenced keeps changing.
I am just beginning to manipulate Access from Excel.
I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.
I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.
When I try to use the second version I receive the error message...
Number of Query Values and Destination Fields are not the same.
The string that works perfectly is:-
Code:
strSQL = "INSERT INTO Clubs (ClubNumber,ClubName,ClubGrade,ClubRegion,ClubPosition,ClubHasHistory,clubinleague,cluboriginalposition) VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"
The one that generates the error message, which is identical except that I have removed the list of fields is:-
Code:
strSQL = "INSERT INTO Clubs VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"
The execute command is:-
Code:
gcnConnection.Execute strSQL, , adCmdText + adExecuteNoRecords
I am receiving the error above when I try to execute the code below. I have checked the fields in the code as well as the table and I can't see what I am missing. The tblMeasure table which is the subform have the following fields:
MUserLoginID - NumberMainMeasureID - AutoNumber - Primary KeyMeasureName - TextMPositonName - TextMeasureScore - NumberMeasureWeight - NumberMeasureTotal - NumberMeasureDesc - MemoMStaffApraisedID - Number
The UserDeatils table which is the main form have the following fields:
StaffID - NumberStaffName - TextDepartmentName - TextStaffPosition - TextStaffGrade - TextStaffBDate - DateStaffEDate - DateStaffApraisedID - AutoNumber - Primary Key
The link between the two forms are StaffApraisedID to MStaffApraisedID then StaffID to MUserLoginID then StaffPosition to MPositonName. When I select a member and click the Duplicate button I received the error above. The area highlighted in red is where it errors out.
Code:
Private Sub cmdDuplicateData_Click()
On Error GoTo Err_cmdDuplicateData_Click
Dim OldStaffID As Integer, NewStaffID As Integer
OldStaffID = Me.StaffApraisedID
'Add new record to end of Recodset Object
[Code] .....
I need to a query on the week number, the week number is in the table. Imjust struggling with getting the current weeks data.
View 10 Replies View RelatedI am new to Access and I am developing a form. I have already read and found useful resources on internet but there is something I am not able to sort out.I have seen that the limit of fields I can enter in Access is 255 and my survey is far below that number.So no issue at all when I created the fields in Data view.However, the issues started when I created a form.
I created a form by Create>Form.The last few fields, at the very bottom of the form, are squeezed onto each other as if there was not any space available. Is there a limit to the number of fields I can have in a form? There should not be if I am allowed to enter up to 255 fields. How can I go about it? Should I alternatively create 2 forms? I would rather not to though.
I have a table that has fields for number input
On my form I have in a TAB form 3 fields to input a figure, in the table these are set up as a Text NOT a Number. If i set it as a Number it keeps rounding up? even after i enforce 2 decimal places it still keeps rounding up.
Field 1 - 42988.62
Field 2 - 0.00
Field 3 - -14330.84
I then created a unbound txtbox so I can add these up
=[ResBF]+[ResRefReC]+[ResSurDef]
what i get in return is this :- 42988.620.00-14330.84
I also tried =Sum([ResBF]+[ResRefReC]+[ResSurDef])
and that just comes up with #Error
I even tried in the table directly to have a calculated field and still having the same problems.
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.
View 7 Replies View RelatedI 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.
View 4 Replies View RelatedI search around online and did come across much info on code to how to hide fields. But, the problem is I cant seem to find a way to hide a certain amount of fields based on a number amount.
example: Lets say I have a 12 text fields to show 12 months of monthly payments, and lets say a client only has a 3 payment term. How can I list 3 under installment field and have remaining 9 fields auto hide since they wont be necessary? this way I only see what applies and not additional text fields.
I want to know if there's a way to sort alphanumeric fields where the number of digits aren't always the same.
Example: I have the following: 2a, 10a, 3a.
Currently, it sorts: 10a, 2a, 3a.
Is there a way to get it to sort 2a, 3a, 10a without adding a 0 before 2a and 3a?
I have a blank form that is usually entered from scratch, so all the fields are empty.
We do fill out the form with standard data occasionally, so I'd like to be able to assign a button or use a combo box to automatically fill those fields with predetermined data.
Two questions:
1) Can I use a 'on click' for a button to populate the data using this:
Code:
Me.Control1.DefaultValue = Chr(34) & Me.Control1 & Chr(34)
2) If I have a default value fill a combo box, will it save the bound column correctly since I didn't select it from the drop down itself?
I have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error
Private Sub Command5_Click()
If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then
MsgBox "You welcome"
Else
MsgBox "Wrong username or password"
End If
End Sub
i am trying to insert multiple values that i have selected in my listbox to my database access table when i click the "add record button" but the values does not appear in my database table.
i have 2 listbox, when i select the first list box(businessNature) it will display the records in the 2nd list box(lstCuisine). However, the records in the the lstCuisine list box is not entered into the table in my database.
(ps: in my property sheet for my lstCuisine listbox its multi select is simple)
Here is my codes:
Private Sub Add_Record_Click()
If IsNull(Name) = True Or IsNull(Mobile) = True Or IsNull(Email) = True Or IsNull(CompanyName) = True Or IsNull(BusinessNature) = True Then
MsgBox "Please fill in Business Nature, Name, Contact, Email and Company Name"
Else
DoCmd.GoToRecord , , acNewRec
End If
Dim conceptValue As String
[Code]...
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134
[code]....
A few sample outputs
one solution is taking rows 1-6 and 26.
OtherID
11098554
56200936
25138850
56148974
56530104
56148975
Another solution is taking rows 1-4 and 10-14.
OtherID
11098554
56200936
25138850
56024315
56115247
56148974
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
I have a form in which I have a combobox. The code in the row source is:
Code:
SELECT [tblBooks].AuthorFirst & " " & [tblBooks].AuthorLast AS Expr1 FROM tblBooks GROUP BY tblBooks.AuthorLast, tblBooks.AuthorFirst HAVING (((tblBooks.AuthorFirst & " " & tblBooks.AuthorLast)<>"") And ((Count(*))>1)) ORDER BY tblBooks.AuthorLast, tblBooks.AuthorFirst;
This is an unbound box.
The rest of the form contains bound textboxes and other bound comboboxes.
This form is processed by the following VBA code:
Code:
Private Sub cmdAddBook_Click()
'Place the authors first and last name in an array
splitAuthor = Split(cboAuthor.Value, " ")
'Create a new record with the form information
DoCmd.GoToRecord , , acNewRec
[Code] ....
What happens is that it places all the records (Except those in the array) in a field and overwrites the values in that field.
I have used the same GoToRecord statement on other databases and it works just fine. I suspect that it is due to the unbound combobox.
What I need to find a way to place all the data in the corresponding fields of a new record including separating the author's first and last name when retrieved from the combobox. I just need to figure out how to get the data in the table somehow.
A quick question... is it possible to sort a table by a memo field? I have a table and am able to sort by other, non-memo fields, however when I place my cursor in the memo fielld I would like to sort, the sorting option becomes 'greyed-out'. Is there a way around this?
View 7 Replies View RelatedI created a form with a query as it's source. I am able to open the tables themselves and update them, but when I go to a field on the form I'm unable
to update. What could be wrong since I have full update rights to these tables? I've checked the properties and they appear to be set correctly.
I have a continuous subform recording the information of various wireless products for an employee embedded in an employee main form. Please see screen shot enclosed.
There is a check box "BYOD/Personal" on the subform. I want to hide a number fields when this check box on independent record is checked. However, if I use the following codes, the changes apply to all records under that employee.
Private Sub BYOD/Personal_AfterUpdate()
If BYOD/Personal.Value = True then
Me.Provider.Enabled = False
Me.XXX.Enabled = False....etc.
Else
Me.Provider.Enabled = True
Me.XXX.Enabled = True....etc.
End If
End Sub
How can I just disable those fields on the subform of a specific record when the BYOD/Personal field for that record is checked?
I have a data base with names, email IDs, phone numbers of 5 types(example quality, security, IT,finance etc) of person from ten different project teams. I created a basic form for the end user to enter values and created fields for each of the person's name, email ID etc based on the personal type( liek quality, IT, finance etc) on each team. The background table contains records of almost 200 people now.
All of these people will be trained for one task and some will be trained for multiple tasks. I know how to make the combo box allow multiple values/selections but, i cannot list out all 200 personal names in the combobox's rowsource/value list. Is it possible to combine all different name fields and list out all personal(of one team), so that the end user can just select multiple names for that team who have been trained and so on. i need to set the rowsource of the combobox to the values of all personal from one team so the team leader can just go select who has been trained and who has not.