Update All Null Values to 0 in a Table

I have a table that contains a list of employees and their working hours by month. At the end of each month a new column gets added. I would like to have a query update all the null values in the table without having to list each individual column name, since these will change.

I can't do it in previous steps because a crosstab pulls the data and a Nz function doesn't work. Off the crosstab, I create a make table. In that table I made is where I need to reset the nulls to 0's. Help!


Post your answer or comment

comments powered by Disqus
Hi all. I'm looking for a block of code that will update all null fields in a table to a specific number such as 9999 for instance.

Under normal circumstances I would just create an update query with several iif statements to accommodate this. Or I could create a table that has each field set to a default of zero and then delete and append new data as needed.

My problem is that I have a table that fluctuates in the number of fields. For instance, in one run I may have Field 1 thru Field 24, while a second run may return Field 1 thru Field 50 (this is due to some cross table queries I generate). So I need a function that will loop through each field and replace null values with a value that I specify. Does anyone have any ideas how this can be accomplished?

Thanks so much in advance...

I have a query that returns all customer concerns that have cost the company money by either shipping free parts or paying an invoice. The query returns all data and some fields are blank. For example, if I ship parts only the invoice data is blank as vice versa. I want the null value to show $0.00 in both the query and the report. Where and how do I do this.

I tries to do it under the field in the query under crieteria. This is what I had.

Nz([PartsCost],0)

Thanks for your help.

hi

i've joined two tables and have some null values .....

I want these null values to be 0 to be used in a calculation

anyone got any ideas how i could do this

cheers

Andy

I have a pfunction in updating the data of blank fields in a table that met a certain criteria - the current process is slow and I am looking for a way to improve the processing time.

What I am attempting to accomplish - I have a list of employees and the training courses they are required to take, however, not all employees are required to take the same list of courses. My output needs to display all employees (and employee info) with the column headings = the ever changing list of Training Course Numbers. hence the Crosstab query I am using to update the temp table of the final results.

My current function compares the required courses for each employee inputs the completion date or leaves blank if not completed, however, it the employee is not Required to take the course in question, then I need to place a value = "NR" in the field. It is not as simple as replace all blanks with NR, since some required courses may not have been completed yet.

The big issue is that you need to compare the fieldname to the name of the course to determine if the code needs to replace the blank value with the NR, Since the crosstab query will determine the name of the column heading. It would be great if this could be simplified by using a query(s), however, I have had no luck with this method.

See Attached for example of Data from the Table. YELLOW cells = NOT Complete - Blank needs to be NR -


Any and all suggestions is greatly appreciated.

Karen

NOTE: "Ilp Learning Cd"(fieldname) = CourseNo
BEMS = EmployeeID (pk)


	Code:
	Public Sub FillInNRs()
On Error GoTo ProcError

'Purpose: Add "NR" (Not Required) for each employee record in the temporary linked table, zTempData,
'         where there is no course assignment for this employee (ie. no record in the select query "qryEmpMgrCourseList").

Dim db As DAO.Database
Dim rs1 As DAO.Recordset  'Temporary work table: zTempData
Dim rs2 As DAO.Recordset  'qryEmpMgrCourseList
Dim fld As DAO.Field
Dim i As Integer
Dim j As Integer          'Used to store a count of how many courses are in rs1 (zTempData)
Dim strSQL As String
Dim strSQL1 As String

   Set db = CurrentDb()

'Open rs1 (read/write ---> dbOpenDynaset). Note: If zTempData has no records, then we notify user and exit procedure.
   strSQL = "SELECT * FROM ztempdata ORDER BY BEMS"
   Set rs1 = db.OpenRecordset(strSQL, dbOpenDynaset)
   j = rs1.Fields.Count - 1  'We subtract 1 because field references are "zero-based".
   rs1.MoveLast: rs1.MoveFirst

   If rs1.EOF = True Then
      MsgBox "There are no required courses for current employees to process.", vbInformation, "No Records To Process..."
      GoTo ExitProc
   End If

'Open rs2 (read only ---> dbOpenSnapshot)
   strSQL1 = "SELECT BEMS, [Ilp Learning Cd] FROM qryEmpMgrCourseList ORDER BY BEMS, [Ilp Learning Cd]"
   Set rs2 = db.OpenRecordset(strSQL1, dbOpenSnapshot)
   rs2.MoveFirst

'Check field values in zTempData (rs1). This recordset includes a dynamic number of fields.
'The first four fields, fields 0-4, are constant: Org, MgrName, EmployeeName, BEMS and PercentCompleteByName

   Do Until rs1.EOF
      For i = 6 To j
         If rs1(i).Name  rs2("[Ilp Learning Cd]") Then 'Or IsNull(rs1(i).Name) Course is "NR" (Not Required) for this BEMS
            rs1.Edit
            rs1(i) = "NR"
            rs1.Update
         Else
            rs2.MoveNext
                'We have reached the end of rs2, so add "NR" to any remaining fields in rs1 before moving on.
              If rs2.Fields("BEMS")  rs1.Fields("BEMS") Or rs2.EOF Or IsNull(rs2.Fields("Bems")) Then
                Do Until i = j
                  i = i + 1
                  rs1.Edit
                  rs1(i) = "NR"
                  rs1.Update
               Loop
               rs1.MoveNext
            End If
         End If

      Next i
   Loop


ExitProc:
'Cleanup
   On Error Resume Next
   rs1.Close: Set rs1 = Nothing
   rs2.Close: Set rs2 = Nothing
   Set db = Nothing
   Exit Sub

ProcError:
    If Err.Number = 3021 Then
       Err.Clear
       Resume Next
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
              vbCritical, "Error in procedure FillInNRs..."
        Resume Next
        Resume
    End If
End Sub



Hey guys.

I have a table that contains data on people including their birthdate. What I want to do is to have a report that groups people into age ranges and show it in a table. I've managed to do this via a function and a query but what I can't seem to do is show the group titles if no one falls into that group.

Here is the SQL query:


	Code:
	SELECT Count(Child.DoB) AS CountOfDoB, Child.AccessedThisQuarter, AgeGroup([DoB]) AS AgeGrps
FROM Child
GROUP BY Child.AccessedThisQuarter, AgeGroup([DoB]), DateDiff("yyyy",[DoB],Now())+Int(Format(Now(),"mmdd")

First of All, Hello i'm new here

Secondly, english is only a second language for me so sorry if sometimes i'm not clear enough

and third my question :P

I have a table called Submission Tbl_Submission and there is a field there called Price

What I would want to is : When i Open a Forms, the automatic number of tbl_submission is taken only as a reference to enter automatic info in the other text fields and then in a sub-forms I add things the costumer want, like let's say, a chair, a desk and the quantity and the price.

Out of the subforms I have the Sub-Total in a textbox and 2 others for the taxes.
What I want is in another texte box I Do Sub-Total+Taxe+taxe

I already did that, but what i want is that this value the TOTAL to be recorded in my Tbl_Submission and I don,t know how to Calculate a Value and Link it to Table field at the same time

Again sorry for my english thx

I have a query which displays the names of sales reps in order of sales productivity in the Rep field. I want to put all of those names into one record, so that name 1 would go to the Rep1 field, name 2 to Rep2 field, and so on. That way I can base a form on that table/query and be able to display them all on one form, in the order of productivity.

I know that I could use a subform which would create the required display without any conversion, but because of other calculations I want to do based on those names it is an impractical solution.

If there is a better way to accomplish this of course I'm open to it, as long as my goal of getting the values from one field of multiple records in a table/query to display as multiple fields on one record is attained.

All assistance would be appreciated.

Hi,

I need to create a form with a text box and a command button; in the text box i will enter a value and when i click the button, i need it to lookup the value i write in a query and write it to a specified table's 1st column (has a column name) while also writing the other values in specified columns in the query which i lookup.
I need to know where and how to start. The tables and queries are all ok. Could anyone please help?

I have a form where user picks start/end dates for a certain person for a certain task.
What I want to do is to check and see if that person is already doing something on a particular day. I’m storing all the start/end dates in a table where I have employee ID as a foreign key.
Is there a way I can lookup already allocated date value for certain employee ID in that one table?
Thanks.

Hi, bit new to VBA but learning quickly, I need to know if a value already exists in a table if it doesnt I want to send an email.
Something like this:

If forms![myform].value in table![mytable]column then
docmd.runmacro "sendemail"


I dont know what command to use for the in bit.
Hope you can help.
Rich

I think this problem is easily fixed, but I can't figure out how to do it.

Very briefly, my database is used to help people repair the machines in a factory. The main purpose is to print reports that will tell the technicians which machines need fixing and what's wrong with them. There are hundreds of machines, so they're divided into categories. I have a pair of combo-boxes (the second dependent on the first) set up. The second, dependent one captures the machine type (which was selected in the first combo-box), the machine name, and an ID number for the machine. The bound column is the one with the machine ID number. So the table it's bound to saves only the ID number, not the machine name. (I have it set up like this only because I followed an example I found online. I've tried to set it up without using the ID number at all, but I can't get the dependent combo-box to work any other way.)

Here's the problem: in the report I need the machine name to show up, not the maching ID number. The ID numbers are just randomly assigned, and the technicians don't know which machines they refer to. So when the report is generated, it needs to say what the name of the machine is, not the ID number. Do I need to set up a table that identifies the ID number with the name? Can I do this in the query?

Any help would be appreciated.

I have asked this question before. BobLarson sent a great example but without coding or SQL query experience my attempts at getting values to appear in a form's text box did not work. I have attached an example of my form and the table of field values that I would like to see populate the form's text boxes for each record.

Thanks! I appreciate your expertise. I will get it I am sure.


Hello,

I have a form where, when I select an item from the combobox, a textbox is filled with [comboboxname].[column](3). I set up the control source of the textbox with [comboboxname].[column](3)

Because this information can change during the time, I need to save it in a table as it is at the moment of selection.

I tried to execute an "insert into" command but only the bounded fields are saved.

Assume that the textbox name is "TxtAp" and the combobox name is "CmbTitle". In the fourth column of the CmbTitle is the price. The control source of TxtAp is "=[CmbTitle].[Column](3)". The price appears in TxtAp when I select a title from CmbTitle but when I try to execute the INSERT INTO command, the price is not written in the table.

I tried like this:

Dim LTxtAp As Single

LTxtAp = Me.[CmbTitle].[Column](3); nothing
LTxtAp = Me.TxtAp; nothing
LTxtAp = Me.TxtAp.Value; nothing

Any ideea, please?
Regards,
Robert

I have a need to assign custom numeric values to dates in a table so that I can check that table for matching numeric values. The following is what IS, and the need:

At work, everyone is assigned to a day off group because work is staffed 24 hours a day and day off groups 91-99 work 6 days in a row follwed by 3 days off, day off groups 8,9,10 work 5 days a week with two days off. There are 12 day off groups, 91-99,8,9,10.

8,9,10 relate to fixed days off each week, 8 = Sat&Sun, 9 = Fri-Sat, 10 = Sun&Mon.

Day off groups 91-99 shift by 1 day off group per day. An example would be 05 Jan 2010 (Tuesday) contains the following day off groups: 91,92,93. Wednesday would then contain day off groups 92,93,94 and so on. Every Saturday contains 8,9 as well as three of the other day off groups which could look like the following:

Saturday contains 8,9,91,92,93. This corresponds to who is not working that day because that is their day off group. The next Sunday would then contain 8,10,92,93,94.

What I'm trying to wrap my mind around is how can I populate a table to contain each day of the year and their corresponding day off groups if I provide the values on 01 Jan 2010.

This seems like it should be simple but I'm having a prob trying to find a way to assign values to a start, how to increment days and assign values...populating I can handle, it's the logic to assign the values to the days.

Is this a bit much?

I have two columns in a table: StartDate and EndDate. Using the validation property in table design view I wish to make sure the date entered in the EndDate field cannot be a date occurring before or equal to the date in the StartDate field. However, I am unable to get this to work. I have tried "> StartDate" and "> [StartDate]" (without quotes) in the validation property but to no avail.

Thank you in advance for your help.

Hi Folks,

Just a quick question, is there a limit to the number of fields you can have in a table, if so does anyone know what that limit is?

Thanks

John

I am designing an inventory database at the moment to book stock in and out. I have all of the components logged in a table (Stock Item - JPG attached).

Some of the stock items have individual asset numbers but are part of 1 bespoke machine we call "Noah". If I put in a new stock item i.e. Noah, how do I link all of the other stock items included in the machine (Numbers 105 - 112 in the attached JPG) to "Noah" so when that stock item is chosen, the rest of the linked items are booked out of stock?

Any help would be greatly appreciated - Many thanks in anticipation!

Jan

i want to export data field to field in a same table.
one field is hyperlink data type where other field structre hyperlink

Hi guys.

Does anyone know if it's possible (if so, how) to store a calculation in a table and run that calculation in VB?

I have about 20 different calculations that I would like to store in a table rather than hard code them so they could be edited by a general user who knows no VB.

For example I have a table called t_JobFunctions with a field called Calc1. In Calc1 I have entered this text: stockoutRatio * stkoutAdj * 100000 * hrsInFctn. The text contains variables which I will lookup from within the same procedure where I would like to use the calculation.

Is there a way I can now run this formula? I had to set the data type in the table to text so when the code looks up the calculation it is in string form so I cannot populate the variables and calculate the result.

Any help will be dearly appreciated. Thanks.

Hi

Ive made a booking system in Access and need to convert it so it can handle multiple users on a network. The problem i have is that when someone selects a block of time on one computer i need the other systems to lock out those blocks on their screens. I have a table which records each user's activities so i could always use the On Counter event on the form to repeatedly check if there is any blocks to be locked out according to data in that table. The obvious problem with this is the huge overhead.

Is there any other way round this? or is there any way of detecting changes to data in a table so that i can run code to lock out the blocks only when theres a change?

Any help or suggestions much appreciated!

Hi folks,
Is there a way to have a calculated field within a table? I have a table that I've added four fields to: AmountIT, AmountRE, AmountKK, AmountRes. I want to be able to sum these four fields to give AmountTotal, but is there a way to have in a table?

I would like to know if there is a maximum nb of columns to use in a table or at least a prefered max nb. For example i have a table "customers" and inside i have "name" "last name" "age" "gender"..and so on is there a limit?
By the way i'm using access 2007

Thx for the explanation in advance.

I am writing a logbook to keep track of flying hours, and I need to countback in a table to find out when a 90-day period which covers three landings will expire.

So, if I have three entries as follows:

01/12/01
03/12/01
04/12/01

The 90-day period will cover those three, and the 90-day expiry will be 01/03/02.

What I want to do is countback three records, then add 90 days to it. How can I make a query countback three records and give me that result so I can add 90 to it?

How can i update all the null valued fields of a table to zero?? or is there any other method??? by the way i am using access 2000.


Not finding an answer? Try a Google search.