I have recently encountered some issues with my database. When I load data from an .xlsm file to my front end table, I get additional records with no data only the ID. The data I loaded comes in fine, however I get many of these records with no data only the ID. This only happens in one of my tables.
View RepliesHow I can automatically remove blank records from access table??
View 3 Replies View RelatedI 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...
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
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
When I import an excel spreadsheet into my Access database it always creates a couple of new blank records. I import this spreadsheet frequently so I would like to set up a macro that will automatically delete any blank records it creates. Can someone tell me specifically how to do this?
Thanks.:)
Hi,
I'm new to Access and I've run into a little problem. I've created a table with a primary key (autonumber) which will contain 1520 records. At present I only have data for the last 8 records. Ideally I would like to create 1512 blank records, with only the autonumber entered e.g. 0001, 0002, 0003 etc.
Unfortunately I cant add the data that I have got to 0001 - 0008 as it relates specifically to the primary ID.
Thanks.
Hello,
I have an unbound combo box in the header of a continuous form. The box is set to value list, and when you select a value, the recordsource of the form is changed. This works fine.
However, if I there are no records in the underlying form, the combo box is blank. If I check the value in the immediate window or the after update, I can see that it is set properly and contains a value, it just doesn't display it. I am using Access 2000. I have tested on Access 2003 and cannot replicate the problem.
I have found one post here (http://www.access-programmers.co.uk/forums/showthread.php?t=84423&highlight=combo+box+blank) on the topic.
I found this (http://support.microsoft.com/default.aspx?scid=kb;en-us;287478) article on Microsoft which has my symptoms, but I am not opening a recordset.
Anyone know how to fix this??
I have a form that is opened by the following code:
Private Sub Status_AfterUpdate()
If Status.Value = "WIP - Snagged" Or Status.Value = "WIP - Suspended" Then
DoCmd.OpenForm "NotesJobChanged", , , acFormAdd
Forms!NotesJobChanged!txtJobNo.Value = Me!txtJobNo.Value
Forms!NotesJobChanged!Date.SetFocus
End If
This 'NotesJobChanged' form has three text boxes on it - JobNo, Date & Note. The JobNo is autofilled from the code above. If nothing is entered into the other fields and the form is closed this record is entered into the database with only the JobNo filled in.
What I want is a way to force the user to enter data into these fields. I don't want to set the 'Required' property of the field to yes as it generates an awful system error message. If the user attempts to close the form without filling it out can they be reminded that they have to, but with an option then to close the form if they really don't want to enter any data? If they do chose to close without filling it out can it be prevented from being added to the database.
Hope this makes sense :)
Many thanks in advance
Michael
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
how to have our query show ONLY the records with NO DATA in the company field?
View 12 Replies View RelatedI have written an invoicing module for my program and some users in the office are dirtying a record and then leaving the module without completing the invoice. (This leaves behind an almost blank record with a period in one field or a space, etc). When someone else starts an invoice from another workstation it skips this almost blank invoice and starts a new one, skipping the almost blank one. Is there a way to check for these orphans automatically before using another invoice number. This happens once or twice a day so it is of concern to my bookkeeper who checks the sequence of invoices to make sure none are missing before posting them to the accounting program.
View 4 Replies View RelatedHi guys, I have a query that displays records that fall between 2 dates (using 2 input text fields). How can I make it dispaly all records if the user just leaves the dates blank?
I've searched through some forums and found that I can make this happen by using IS NOT NULL. But when I tried putting it in the OR row in my date field (and all other places), the result is that the query displays all records that have a date value regardless of my other filters.
Can anyone point me in the right direction where I should actually place the IS NOT NULL? I attached my Query view for easier understanding.
Thanks a lot!
I am creating a report that is organized by project. The detail lines are to list payments applied to the project. How can I skip the detail section (or print a single blank line) if there are no payments in the separate payment table that match the project ID? Is there a way to tell that there were no matching payments and format accordingly? I currently get multiple blank lines.
View 2 Replies View RelatedI have two tables: Table #1) [Request Table] Customers request our company to stock a certain Part Number with a minimum value. Ajax - P/N "NP4i" - Min "30".
Table #2) [Sales History] All sales purchased and the "Qty-Total" purchased. For simplicity Fields are limited.I created a CrossTab Query that displays from [Request Table] P/N's requested/ min values (Row Headings) then showing P/N's purchased [Sales History] Value="Qty-Total" Sum and
Column=Format([Invoice Date],"mmm/yyyy") group by, showing last twelve months.
Ex: Customer name, Part Number, Minimum, May/2013, Apr/2013, Mar/2013 and so on.
AJAX, NP4i, 30 4 21 5 and so on.
The Crosstab query list only requested Part Numbers that have at least One sale wihin the last twelve months.If there are NO sales, the crosstab query will NOT show this Part Number.My boss wants to see the requested Part Numbers that were never purchased along with the ones purchased.He wants to address the NO sales, yet they requested we stock that particular Part Number for them.
Is there any criteria I can use to force the Part Number field on the [Request Table] side to populate even though there were NO sales within the last twelve months?
I have a query by form that has the criteria Like [Forms]![FormName]![ControlName] & "*" for each field. However the problem is that if one of the records have at least one blank field then the whole record wont show up in the query results. For example if there is a record with Fullname, and Age filled in but Address isn't filled in then if search Adam into the query by form the record wont show up because the address line is blank.
To make records that have a blank field show I know I could use Like Forms![FormName]![ControlName] & "*" Or Forms![FormName]![ControlName] Is Null but when I have used it, it has corrupted the query because I think if you use If Is Null many times in one query it becomes too complex for it to process.
Also, The other method is to use Nz in an expression but I cant do that because I want the query results to show up in a form where you can edit the records and the error message Field is based on an expression and can not be edited comes up if you try to edit the records. Anything else I could put into the criteria to show records that may have a blank field?
I'm creating a database for a school. it contains the following tables: 'Students' of which the primary key is StudentID, 'Subjects' of which SubjectID is the key and 'Exams' - ExamID.
Then, I have a table called 'Results' but with no primary key of its own - just 3 foreign keys from the other tables and another field which can be left blank let's call it 'Grade'. So the Results table would have the following fields:
StudentID*, SubjectID*, ExamID*, Grade
Now, I make a form which creates a new lets say ExamID and I would like to create records with all the combinations of all of the subjects and students, ready for the 'Grade' to be typed in.This is assuming the Exam isn't based on the Subject directly.
I am creating a query which uses 2 unbound text boxes to populate a Between function for 2 date fields. If I fill in the dates, it returns the corresponding data. If I leave them blank, however, it returns no records. Is there an easy way to tweak the query to return all records if left blank?
View 4 Replies View RelatedI have a report that is derived from a table (tblDocs) the structure of the table is:
EmpId docType docDate comments
(number) (number) (Date) (text)
This table will have several entries for each employee. There will only be a few times that a comment will be entered. On my report, I will have a field for the comments and I would like ALL of the comments for each employee combined into one text box in the report There will be blank entries in most of the records). I think the control source for this might be a query, but not sure. I set the properties of the text box to can grow.
I have code that I want to cycle through several fields and check instead of coding for each individual field. I thought this might worked on an earlier project but Im lost. When I try to add my code in the "If MyControls Then", I want it to hide the field on a report if it is blank. It doesnt allow me to do MyControls.Visible.
Code:
If Len(Reports!RecallReport!QAW1 & vbnullsting) = 0 Then
Reports!RecallReport!QAW1.Visible = False
End If
but I want the below code to go through each field With QAW in it and hide it if it is blank.Or having it go through each field individual. There are 10 Fields With QAW THen there are another 10 fiels with ShipDist. I would prefer a simple solution to check all at once.
Code:
Dim MyControls As Control
For Each MyControls In Me.Controls ' Iterate through each element.
If InStr(1, MyControls.Name, "QAW") Then
If MyControls Then
End If
End If
Next
I don't even know if I am on the right track or what.
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 RelatedI have a table of the following structure:
Key 1 Key 2 Key 3 Optional
data1 data2 data3 data4
The 'key' fields are a composite key of primary key fields from other separate tables. I have a form that generates a new 'Key 1' record and I would like to make a query that creates all possible 'Key 2' and 'Key 3' results (new combinations) while key 1 stays the same. How would I go about doing this? I know I can be done.
I cannot really give any real data as I haven't received it yet.
Very suddenly today, when I'd done seemingly nothing, all of the records from my form that I'd built from a blank form disappeared, with the exception of the first record. When I try to just close everything down, nothing changes. When I put in a new record to the form, however, it's saved in the table. But when I save and close, the form goes back to only having the first record! This is very annoying, for easily perceivable reasons. I even tried creating the form from scratch, to see if it's some residual SQL update code acting funny, and nothing changed! It seems to work if I select a table and just create a form, though. It doesn't even work when it's a splitform and whatnot.
View 1 Replies View RelatedAll. 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 RelatedI 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;
I have a calculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:
Code:
TotalPt: [Att1Pt]+[Att2Pt]+[Att3Pt]+[Att4Pt]+[Att5Pt]+[Att6Pt]+[Att7Pt]+[Att8Pt]+[Att9Pt]+[Att10Pt]+[Att11Pt]+[Att12Pt]+[Att13Pt]+[Att14Pt]+[Att15Pt]+[Att16Pt]+[Att17Pt]+[Att18Pt]+[Att19Pt]+[Att20Pt]+[Att21Pt]+[Quiz1Pt]+[Quiz2Pt]+[Quiz3Pt]+[Quiz4Pt]+[Quiz5Pt]+[Quiz6Pt]+[Quiz7Pt]+[Quiz8Pt]+[Quiz9Pt]+[Quiz10Pt]+[MidtermWritPt]+[FinalWritPt]+[Proc1Pt]+[Proc2Pt]+[Proc3Pt]+[Proc4Pt]+[Proc5Pt]+[Proc6Pt]+[Proc7Pt]+[Proc8Pt]+[Proc9Pt]+[Proc10Pt]+[Proc11Pt]+[Proc12Pt]+[Proc13Pt]+[Proc14Pt]+[ProcPracPt]
I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!
I've attached a screenshot.