Adding 2 Fields Together - You'd Think It Would Be Easy, Wouldn't You?
Aug 30, 2006
Ok, I'm absolutely positive I'm missing something totally simple here... Someone give me a clue...
I have a database whereby I track academic performance of students in our program. I am trying to use last year's table to calculate how many hours each student earned during all three semesters.
In many cases, students did not enroll in all three semesters, so I am dealing with Null values in many of the fields. The easiest way would be to do something with the Nz function such as follows:
HrsEarnedLastAY: Sum(Nz([HrsEarnedFall],0)+Nz([HrsEarnedSpring],0)+Nz([HrsEarnedSummer],0))
But what I am getting is the numbers in a string! Say, a student earned 12 hours in the fall, and 10 hours in the spring, and didn't attend at all in the summer. The answer I'm getting in the calculated field is 1210, rather than 22. I've tried about 50 permutations of the above expression and I truly can't see why it isn't adding the fields together... I have also tried this with parenthesis around each Nz: (Nz([HrsEarnedFall],0)) , same result.
The Sum function does not recognize Null fields, but I thought I could use the Nz function to account for that. If I can't use Nz with Sum, how the heck do I add these numbers?
What I need to do is create a table with these hours totaled, and then update my main table from it. I could conceivably add the fields together in a report, but then I don't have any way to update my main table other than simple data entry.
I really do not want to have to go back into almost 700 records and add in zeros in multiple fields just because I'm missing something stupid.
You'd think an application as powerful as Access would be able to add 2+2 and get 4...
Any help would be most appreciated...
View Replies
ADVERTISEMENT
Jan 3, 2006
Hi! Everyone on this forum has been very helpful so far, and I could really use some expertise now. I'm trying to update an existing DB designed by someone else (no longer here). I need to add a button to the switchboard for 2006, but I don't understand the code that has been written for the form I am trying to update. I've posted the code below. If anyone can help me decipher it enough to add my button, I would really appreciate it. My new button should be the 9th one. Thanks in advance:
************************************************** *
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.
' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
End Sub
Private Sub Form_Current()
' Update the caption and fill in the list of options.
Me.Caption = Nz(Me![ItemText], "")
FillOptions
End Sub
Private Sub FillOptions()
' Fill in the options for this switchboard page.
' The number of buttons on the form.
Const conNumButtons = 9
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Dim intOption As Integer
' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption
' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL)
' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rst.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
While (Not (rst.EOF))
Me("Option" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
rst.MoveNext
Wend
End If
' Close the recordset and the database.
rst.Close
dbs.Close
End Sub
Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.
' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
' An error that is special cased.
Const conErrDoCmdCancelled = 2501
Dim dbs As Database
Dim rst As Recordset
On Error GoTo HandleButtonClick_Err
' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)
rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
' If no item matches, report the error and exit the function.
If (rst.NoMatch) Then
MsgBox "There was an error reading the Switchboard Items table."
rst.Close
dbs.Close
Exit Function
End If
Select Case rst![Command]
' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst![Argument]
' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rst![Argument], , , , acAdd
' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rst![Argument]
' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rst![Argument], acPreview
' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "WZMAIN80.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions
' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase
' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rst![Argument]
' Run code.
Case conCmdRunCode
Application.Run rst![Argument]
' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."
End Select
' Close the recordset and the database.
rst.Close
dbs.Close
HandleButtonClick_Exit:
Exit Function
HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If
End Function
************************************************** ******
View 5 Replies
View Related
Sep 11, 2005
I have a code for a free text search form which will return records in another window when the search button is clicked.
CODE
Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."
Else
'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
'Filter frmRhinitis based on search criteria
Form_frmRhinitis.RecordSource = "select * from tblBaseline where " & GCriteria
Form_frmRhinitis.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
MsgBox "Results have been filtered."
End If
End Sub
However, even if no records match the criteria the window will return a "Results have been filtered" but return a blank form. How do I include code that will return a "Match cannot be found" MsgBox if the search string isnt found?
Thanks :)
View 3 Replies
View Related
Feb 18, 2007
I suppose I am hoping for a simple answer here when it could be a myriad of things but fingers crossed.
I have a database - split back end, front end both files under 4000kb. It is sitting on a server.
On some computers it will open up to the Switchboard (so I have ruled out access (as in security access) problems) but the command buttons on the switchboard are all inactive. This only happens on some computers (so a person can sign on to one workstation and all is OK and then move to the next and the above happens). There are not multiple sessions open all at once - maximum probably 3.
Any ideas???????
View 1 Replies
View Related
Mar 12, 2006
I have a field called Badge_Number in Table A.
I have a field called Badge_Number in Table B.
In Table A Badge_Number has entries 101, 102, 103, 104, 105
In Table B Badge_Number has entries 102, 103
How can I do a query to get a list of entries that are in A but that are not in B. (Query result would be 101, 104, 105)
This is the only thing I have to do in Access, so after trying to learn this for a while, I have decided it would be much easier to get help at this time.
View 2 Replies
View Related
Jul 26, 2006
I promise I have searched, but I hav spent 10 minutes reading through posts that are unrelated...
What is the code to have multiple fields updaterd based on what is input into a field?
---
Example:
A ZIP Code Field, which updates City & State on the form when entered. (I have a table that has over 39,000 ZIPs w/ City & State already there)
---
I have several applications for this, but if someone could explain this use to me, I will be able to figure it out.
Thanks a million!!!
View 1 Replies
View Related
Jan 29, 2014
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code:
SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
That is the query.
View 14 Replies
View Related
Aug 15, 2013
I reached the limit of 255 fields in a table. I just need to add one more field so I deleted several fields I no longer needed thinking I would then be able to add one more new field. However, I am still unable to add one more field. How to free up fields that are no longer needed?
View 8 Replies
View Related
Jun 6, 2007
Hi,
I am creating a small database to house results of certain tests. Is there a function in access that allows me to add fields if a result is out of the specification required. In other words I still need to record the out of spec results but I also need more fields to appear for the next lot of results to be entered, for example.
When cooking a batch of product I need to test the pH at the 30 minute mark if the pH is too low I will continue cooking the product for another 30 minutes and then test the product again. The cook time is not a constant so I never know how many samples are required, therefore I don't know how many fields i would need in advanced.
View 3 Replies
View Related
Mar 2, 2006
Im doing a project at school and it involves adding 2 entities and then the answer being displayed in another entitiy i have searched the forums but with no luck is it possible at all.
View 1 Replies
View Related
May 30, 2006
i have a form which has 2 subforms on it. In each of these subforms, i have a field which displays a total. Now i would like that in the footer of the main form i will create an unbound text box which will add the two total fields, and displays a grand total.
Is this possible pls ?
Thank You
View 6 Replies
View Related
Aug 8, 2007
I need to add new fields to and existing form. I have added the fields to the table the form is based on. When I am in design view for the form I open the fields icon to add the fields but the fields are not showing up. How can I get the fields to show up so they can be added to the form?
Thanks
Henry
View 4 Replies
View Related
Nov 10, 2005
My company has a microsoft access table with about 1200 IDs containing names, addresses etc.
Now I want to print this out in an excel sheet because I want to create address stickers, but I want every cell in the excell sheet to add information about the columns in the database together. I might be very unclear, let me demonstrate what I mean:
The Acess file might look like this:
id | name | address
1 | john | Street 01
2 | sara | Street 02
Now I want to have 2 excell cells which contain the information like this:
name_address
name_address
In my example, 2 cells with the following information:
john Street 01
sara Street 02
So I dont want 4 cells, I want to add the information together from 1 Id to 1 cell, but I also want to add linebreaks to it, It must eventually become an address sticker
How can this be done?
View 3 Replies
View Related
Dec 28, 2005
Hello, I have what I believe to be a very basic, albeit wordy question regarding an Access based mailing list that I am designing. For the scope of this project, I have 7 fields: Position (the position of the person recieveing the mailer, So far all I have is the "Principal", these are all schools) Name (The name of the school), Address, City, State, Zip and SchoolID (this is the primary key and will be hidden)
My question is: How can I add multiple positions per School (Name)? I need to add 1st through 6th grade teachers as well as the councilor to each location. So each of these people will be sent a mailer. Any help on this would be greatly appreciated. Feel free to ask any questions needed.
Thank you
View 11 Replies
View Related
May 15, 2007
His is itpossible to make abutton in access and then assign a equation to it to add the dat in two of the fields together, for example adding the "stock level" and the "re-order level together? ifso could some please tell me how greatly appreciated cheers
View 1 Replies
View Related
Feb 6, 2006
I have 2 fields in a table 1. called CommAmtDue and the other called CommAmtPd, When the user inputs a value in the CommAmtPd I want the CommAmtDue - CommAmtPd = CommAmtBal
I want this value to be stored in another field in the same table called CommAmtBal
I can do the formula in a query but how can I get the value to be stored in the field to user for later.
Thanks
View 14 Replies
View Related
Aug 15, 2007
Hi,
I have tried to add up fields in a table but am struggling to get it to work. Can anyone help please.
In design view of tables, I have four fields (that I will be using in a a form eventually);
RaffleIncome
AlmsIncome
OtherIncome
TotalIncome
I want to get the TotalIncome field to automatically be the total sum of any or all of the other three.
I have tried putting =[RaffleIncome]+[Almsincome]+OtherIncome} in the control box of the TotalIncome field (a guess) in a form I made, and it gives me the total sum of the others three but does not save when I look at it in the table view. (that is, the TotalIncome column remains £0.00)
I am learning as I go along and am okay figuring some stuff out, but this has got me a bit frustrated, and I guess I cannot see the wood for the trees !
Thanks anyone
Adrian
View 6 Replies
View Related
Dec 15, 2005
All,
I am using an Make-Table query to add many new fields to a Table using Fieldname: "".
However, when I use this, it sets the Allow Zero Length to No.
Is there code that I can use to stop this happening?
I am using this method for multiple fields and across multiple tables.
Your help appreciated,
Jem
View 1 Replies
View Related
Nov 2, 2006
hi all
i can do simple additions in access but i have been asked to calculate fields in different tables
all i need to do is
i have 2 tables in my database
Table 1:main_database
Table 2:Security 2_3
and i have in table 1 got 1 field called Estimated security value
and in table 2 i have 2 fields called Sec 2 estimated value and Sec 3 estimated value
all i need to do is add all of the fields together in query or field in a form
i have tried =([Estimated security value]+[Security 2 & 3]![Sec 2 estimated value])+[Security 2 & 3]![Sec 3 estimated value]
but i get '#name error
thanks in advance
chris
View 3 Replies
View Related
Sep 25, 2006
I am trying to take a form that has 4 text boxes and add the first three together and have the sum show up in the 4th box. Is this possible?
Textbox1 +
Textbox2 +
Textbox3 =
Textbox4
I am tryimg yo add up total support hours for my network shop. thanks for any help.
View 2 Replies
View Related
Mar 20, 2008
This is going to sound like a bizarre question, but, here it goes. Is there a way to add more than 255 fields to a table? Or is 255 the cutoff? Also, if you have two table with different information on one client, is there a way to add a client to one table and have it automatically add it to the second? Sorry about all of the wierd questions. Let me know if you know anything.
View 3 Replies
View Related
Feb 21, 2015
Say you make a table with 10 columns. Then you make a form, and all 10 columns show up. Then you go back to the table and add 2 more columns. How do you get the 2 new columns in the form?
View 1 Replies
View Related
Oct 16, 2005
trying to add 2 fields
=[field1] + [field2]
works but I want a space between the 2
how do you do that?
jon
View 1 Replies
View Related
Feb 1, 2006
I have created my tables, relationships and I have gotten halfway through my forms and I have realised that I have missed out a field in a table.
How can I go back and change this without screwing everything up (I believe that would be the best terminology to discribe an error :))?
Thanks :D
View 2 Replies
View Related
Dec 4, 2007
So I have a table containing 2 fields containing the first name and last name of a customer. I do need to add these names in the same table into a new field called Name. Can someone help me out?
View 5 Replies
View Related
Mar 25, 2008
Hi there
I am completely new to access so if this question seems really simple then i apologise. If this has appeared elsewhere on this forum i apologise again!
Can you tell me if the following is possible
I have a table called AA1 containing the following fields:
(PKey)Refno
StudentNo
PC1
PC2
PC3
PC4
PC5
PC6
Range1a
Range1b
What i would like to do is create another two fields called PC total and Range total whereby i create a formula that will add the totals of the PCs for each student )which will then be entered into the PC total column and the same for the ranges.
I am unable to do it using the query function as i have over 225 diffrerent fields overall so i thought if i could create a totals field i could use these for my queries therefore negating the need to include all the seperate fieids, pc1 pc2 pc3 etc...
the above fields are all in number format.
I have probably not explained myself very well and if you need any more information (im guessing yes) then please let me know.
thanks in advance
Jemma
View 2 Replies
View Related
