Records With Null/blank Fields Only!??
Jun 8, 2005
I'm working on a db logging replies to a questionnaire. Judging by the responses I am receiving it appears that some sections are not completed at all. I need to be able to include these blank responses when it comes to analysis.
My design splits the questionnaire into sections, each section has a data entry form with its own underlying table. Each table has a primary key (autonumber field) which relates to each organisation that has replied.
If an organisation has failed to complete a section, I still need to create a new record in that section(table), triggering the autonumber field, hence referring back to the organisation.
I know I haven't explained this very well, but if anyone can make sense of what I'm saying and can give me any suggestions on how to make this happen, I would be most grateful.
In anticipation of your replies
Ride
View Replies
ADVERTISEMENT
May 15, 2013
All. Using access 2010. I have a query that returns 92 records. When I put in the criteria for one field to leave out records with “approved” which totals to 9 records, the query only returns 10 records. It is not returning the records that are blank(not null) for that field. I want those records. Why is this happening and how can I get the blanks for this query?
View 2 Replies
View Related
May 22, 2015
I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)
So something like this :
Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.
So I tried this :
Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)
How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?
Only thing I can think of is to use (untested) :
Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))
But there's a number of boolean fields in there, all requiring the same treatment.
View 14 Replies
View Related
Aug 4, 2015
The unbound Parent form has a listbox (SQL Server) , the selection updates the SubForm with a primary key ID. The Subform is a ReadOnly view from an Oracle Linked table. It populates with a Select * From Oracleview where ID = listboxID The code is at the link shown below update: The subform uses textboxes linked to the read-only record.
The main form can populate, no problem. However the SubForm data source is either populated OR is Null.
If it is Null, the subform stays blank. Two Objectives:
1. Set a boolean variable flag in the parent (main form) that subform record exist or that it is null. e.g. Parent obtain recordset count property from Sub form
2. Display the subform with blanks.
[URL]
View 3 Replies
View Related
Oct 9, 2014
I am in the process of building Append Queries for new records, and I know I can do an Update Query to enter specific information. However, how can I update multiple records from a (externally sourced) linked table to fill in blanks of an existing table? I have created a query to identify records with 1 or more matching criteria which contain the blank fields. I now want to update those blank fields. The data in each blank field is different for each record (same type, just different data).
View 1 Replies
View Related
Jan 27, 2014
I have a query which selects a material ID and material name from one table and the associated manufacturer, supplier, and packaging type from three other tables. Some of the manufacturer, supplier, and packaging data were imported from an Excel spreadsheet and did not have data for those fields, so those fields are blank. When I run the query, I only get the records which have all fields filled out. How can I get the records where the material ID and material name are filled in, but the manufacturer, supplier, or packaging type are blank? Here is the query I'm using currently:
Code:
SELECT tblMaterialSpecifications.ID, tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply, tblManufacturer.Manufacturer, tblSupplier.Supplier, tblPackaging.PackageType
FROM tblPackaging INNER JOIN (tblSupplier INNER JOIN (tblManufacturer INNER JOIN tblMaterialSpecifications ON tblManufacturer.ID = tblMaterialSpecifications.ManufacturerID) ON tblSupplier.ID = tblMaterialSpecifications.SupplierID) ON tblPackaging.ID = tblMaterialSpecifications.PackagingID
WHERE (((tblMaterialSpecifications.ActiveInactive)=-1))
ORDER BY tblMaterialSpecifications.Critical, tblMaterialSpecifications.MaterialSupply;
View 1 Replies
View Related
Nov 14, 2013
I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:
Code:
Private Sub Command18_Click()
On Error GoTo errorcatch
Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"
Me.FilterOn = True
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to.
View 3 Replies
View Related
Mar 17, 2014
I am looking to collate marketing data from different areas of our DB into a Marketing Hub. We have to collect various feedback at different times, if a client on a programme completes a course, they give feedback for that course.13 weeks after a client completes the programme we have to get them to complete a destination survey. On both these forms it asks would they be interested in doing any more courses. This data is then stored in the Courses table and Projects table respectively.
I want to create a query to become the record set for a new form which only shows those students that have stated they would be interested in doing other courses.Five of the options or Yes/No fields, and one is a text field (which courses, other essentially).How does one do a query that only shows records where any one of 6 fields is not null?
Code:
SELECT Students.ID, Students.[First Name], Students.[Last Name], Courses.[Sage Accounts], Courses.[Sage Payroll], Courses.[First Aid], Courses.[Food Hygiene], Courses.[CV Services], Courses.[Interview Skills], Courses.[Which Courses]
FROM Students INNER JOIN Courses ON Students.ID = Courses.ID;
View 2 Replies
View Related
Sep 18, 2013
My subforms are randomly adding blank records and one subform I would like to stay blank repopulates with data, though not necessarily the most recently added record. Both of these seem to happen when I navigate to other main records in the database and then return to this page.Using Access 2013, I have a large form with 10 pages. On one of the pages, I have two subforms. This is set up to gather many-to-one data. The top subform is my data entry form with three fields (two combo and a text) and a command button. The bottom subform is a datasheet displaying the three fields.
The two combo boxes are cascading, and they work great. The text box is there to collect additional info for each selection.
The command button works to
1) save the record,
2) requery both subforms in order to display the new data on the datasheet,
3) clear the combo and text boxes, and 4) set focus back to the initial combo box. It all works!
But then when I leave that main record, the horror begins: blank records (from the "many" table) show up on the datasheet and the data entry fields do not stay blank. I suspect my problem is in the command button. I added this code to the OnClick for the command button:
Code:
Private Sub addMinistryItems_Click()
'save record
If Me.Dirty Then Me.Dirty = False
'requery both subforms
Forms![BCD MAIN 2013]!Child572.Form.Requery
Forms![BCD MAIN 2013]!Ministries1.Form.Requery
[code]....
View 1 Replies
View Related
Jul 18, 2013
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
View 3 Replies
View Related
Mar 13, 2007
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.
I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).
I am using a form to query the table...no problem. The form has text boxes the user filter down the data
The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!
Things I have tried:
1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing!
2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)
what else can I do?
Thanks
View 5 Replies
View Related
Mar 15, 2007
Hi. This might sound stupid but it is a real issue for us.
In our datafile some fields' values were updated to " " . A government entity wants the value to be blank, instead.
How do I change this?:confused:
Likewise, there are some instances where the reverse is true.
How do I change those?
Russ
View 6 Replies
View Related
Jun 18, 2015
In a query, how do I return a blank or null.
IIf([Field1]=[Field2],NULL,[Field3]
They are all numerical values that I need to format into percentages however Field3 could contain zero so I can't replace Null with zero. I simply want to return nothing if Field1 is equal to Field2.
View 3 Replies
View Related
Feb 21, 2014
I have a form with a subform. The subform is used to report a sales total, for today and for the month. When there are no sales today (a null result in one of the fields) the entire subform goes blank.
How can I make it report a zero in the field instead of blanking the entire subform?
View 1 Replies
View Related
Oct 29, 2013
I am attempting to use VBA code to make the label in my report hidden if the text box is blank. I am very new to coding, and am not sure how I would express this in code. I have been looking at a few examples of how to get this done, but it doesn't seem to work. Where to insert the code. Attached is the image of the properties for my label and text box that I want hidden if text field is blank. I al just lost trying to figure this out.
View 7 Replies
View Related
Sep 15, 2015
I am trying to show a certain subform based on whether or not a field is blank or null. This database is not owned by me--it's what we use to run queries and reports on the data on a centralized server for the library. It's not even in the same city--so changing the tables is not an option for me.
So, I have a subform (subform1) that is linked to the main form based on 2 fields (BibID and Item_Enum). The trouble is that the second field (Item_Enum) is sometimes blank, so the subform will not display. So I created another subform (subform2), which is a duplicate of the first one, but this time only linked on the first field (BibID). So, if that second field (Item_Enum) is blank, I want subform2 to display and subform1 to be hidden. If the second field (Item_Enum) is not blank, I want subform1 to display, and subform2 to be hidden.
I've tried putting the VBA code on different events for the form, but I can't get it to work consistently.
There are two problems, one of which I think I have fixed--but I would prefer a better solution. The VBA code doesn't run unless the form is newly loaded. I've tried placing the VBA code on different form events, but nothing works. I want the code to activate whenever a new record is displayed. The work around is that the form IS newly loaded every time the user scans a barcode for a new book. That works now, but if I need to change the way the user looks up new records, this might not work anymore.
The second problem is that the VBA code doesn't display subform1 when Item_Enum is blank. It doesn't display subform2 either, but that's the desired result.
Private Sub Form_Load()
If Me.ITEM_ENUM = "*" Then
Me.subform2.Visible = False
Me.subform1.Visible = True
Else
Me.subform2.Visible = True
Me.subform1.Visible = False
End If
End Sub
I've tried changing the condition to Me.Item_Enum = null, and changing the false/true results accordingly, but that didn't work either. In that case, only subform2 is visible, whether it should be or not.
View 2 Replies
View Related
Apr 18, 2006
Hello all,
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
Thanks.
View 4 Replies
View Related
Nov 16, 2007
I think the title pretty much sums it up....
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
The query is refusing to return any results...
Any ideas?
View 10 Replies
View Related
Mar 19, 2007
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
How can I do this?
Thanks,
Dave
View 3 Replies
View Related
Aug 2, 2007
Hi everyone,
I have a a table called [Email Database] with many fields
I need to create a query that replaces all Null values with blanks or ""
I'm not that familiar with SQL so any help would be appreciated!
View 1 Replies
View Related
Jan 19, 2006
Hi - I have what maybe a relatively easy problem to solve.
I have a list of locations each with a unique id. I also have another table with location details in it. There is a relationship setup between the two tables. However in the first table I have some locations that do not have a match in the second table. Therefore that field is left blank. When create a query to show information from the two tables, any records that have a blank location match field do not get shown in the output. Is there anyway to display records with blank fields?
Thanks
Nick
View 4 Replies
View Related
Mar 21, 2006
In an Input form, how can I blank out certain fields such that the field data is shown, but grayed out, and other fields are shown normally?
View 1 Replies
View Related
Jul 29, 2005
Hi,
Is there a way to get blank fields to sort to the bottom rather than the top when sorting alphabetically in access?
TIA!
View 2 Replies
View Related
Oct 26, 2004
I am a firefighter who has been tasked with creating a database to track repairs and inventory on breathing apparatus(SCBA). There are several(5) related pieces of equipment that I think should go on the same table. Three of these will have nearly identical fields. The other two will have additional field specific to them. Should I create one table including all five pieces and just leave the extras fields blank or is there a better way.
Thanks.
Mike
View 2 Replies
View Related
Oct 28, 2005
Hello, I have a table (Projects) with original projects and amended projects. All information regarding each project is inputted in the fields for original projects. Only limited fields are inputted in amended projects;new loan amounts or a new closing date may arise infrequently for amended projects. When there is a new closing date for an amended project, and it becomes part of a query all other fields are blank for that record, How do I populate the original project data into the blank fields of an amended project. I frequently have to create queries based on the fiscal year of closingdate field and I need to know the commitmentdate (field) and other dates that are in the original projects records. I hope this is not confusing thanks in advance. :confused:
View 1 Replies
View Related
Nov 4, 2006
Hi Friends,
Sounds Impossible But i believe there must be a genius out there to solve this
I was wondering if there is a way to stop displaying fields on a form which contain spaces or blank values. I m using a query that takes data from a table named School
I have a table which have 5 fields. lets say: Field1, Field2, Field3, Field4, Field5
My fields from 1 to 3 have data but field4 and field5 do not have a value in it. What i wish to achieve is to show only those fields which have a value in it. Blank or field with spaces must not be displayed. Is It Possible.
Regards,
Darno
View 1 Replies
View Related
