Queries :: Records Not Showing With Blank Numeric Fields

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 Replies


ADVERTISEMENT

Queries :: Pulling Records Based On Blank / Non-blank Criteria

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

Queries :: How To Convert Text Fields To Numeric

Aug 16, 2014

I have an ms access Database(2013 version). There are about 10000 records. There are some columns with field property of "short text" but contains the values like that 0.4,7, 9.0 etc I would like to convert the "short text" into "double" without loosing information.

View 3 Replies View Related

Queries :: Concatenate - Difference Between Numeric And Text Fields

Aug 6, 2015

I have a Query contains field that is :

Code : AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE SerName='" & [SerName] & "'" & " ORDER BY NormID")

but [SerName] is a text type field that is not Primary Key.

I have in the FamilyTbl , [ServiceID] is numeric type field, and a primary key. I try :

Code ; AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE ServiceID='" & [ServiceID] & "'" & " ORDER BY NormID")

but this returns all [NAll] records. I use concatenate :

Code:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant

[Code] .....

View 14 Replies View Related

Queries :: Create Numeric Query Fields In MAKE Table

May 25, 2014

Have a Make table query that needs to create (add) several new fields where each field must be numeric design.

Have tried:

Score1: Not Null - does not seem to work (results in a Binary field)
Score1: 0 - which does give me the numeric field designation but every field in table contains a 0.

Would like to show Blank field (makes data input easier at a later time) but still have the Numeric designation.

View 6 Replies View Related

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 4 Replies View Related

Tables :: Updating Blank Fields For Existing Records

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

Queries :: Quotations Database - Queries Showing Too Many Records

Jun 25, 2015

I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.

View 1 Replies View Related

Modules & VBA :: Search Form Not Returning Records With Blank Fields

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

Queries :: Select Distinct Field And Showing Other Fields?

Jun 4, 2013

How to query Select Distinct field + showing other fields? E.g.:

Code:
SELECT DISTINCTROW assetMovementTable.assetNo, assetMovementTable.moveCode, assetMovementTable.compCode, assetMovementTable.compCodeDesc, assetMovementTable.assetDesc, assetMovementTable.equipType, assetMovementTable.equipManufacturer, assetMovementTable.equipModel, assetMovementTable.constYear, assetMovementTable.plateNo
FROM assetMovementTable

[Code] ......

View 7 Replies View Related

Forms :: Subforms Randomly Adding Blank Records Not Clearing Fields

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

Tables :: Creating Table Via Into Query With Blank Numeric Field

Jun 13, 2014

I am creating a table, using an INTO statement.

I require a blank numeric field, which users will be updating via a form.

How do I make the field numeric, as my current script makes it a text field

'' as Ticket_No

And you can't cast/convert text to numeric ....

View 2 Replies View Related

Tables :: Creating Table Via INTO Query With Blank Numeric Field

Jun 13, 2014

I am creating a table, using an INTO statement.

I require a blank numeric field, which users will be updating via a form.

How do I make the field numeric, as my current script makes it a text field

'' as Ticket_No

And you can't cast/convert text to numeric ...

View 1 Replies View Related

Queries :: Show All Blank Records?

Nov 19, 2014

how to have our query show ONLY the records with NO DATA in the company field?

View 12 Replies View Related

Queries :: Access Brings Back No Data If Fields Are Blank

Jul 5, 2013

I have a query that I made for about five fields, where the criteria is

Like "*" & [Forms]![DATA SEARCH]![Box] & "*"

Where box is the name of the field that I am searching for.

However, some of the fields in my form are left blank, which makes this refuse to give any results when I try to query it with a form. The other problem that I have is that the fields are bland in different parts of the 1,000 some-odd row table, which was imported from MS Excel.

View 1 Replies View Related

Queries :: Query To Pull ALL Records Whether Blank / Zero Or Value

Jun 24, 2013

I 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?

View 1 Replies View Related

Queries :: Records With Blank Field Not Shown

Aug 28, 2013

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?

View 9 Replies View Related

Queries :: Crosstab Query - Showing Rows Without Records / Data

May 7, 2013

I have attached part of my database.

I need to show the complete rows for all the status in each year with or without any record/data.

View 5 Replies View Related

Queries :: Cannot Edit Group By Query - Fields Reverting Back To Blank

Jun 8, 2013

I have a query where I group by EmpID, so duplicates do not show up. In this query I have two fields with no data. These fields are to be filled in after the query is run in a form. However, these fields are not editable due to the group by feature. I tried two unbound fields, which does allow me to type, but doesn't save the changes once I click out of the field. The field then reverts back to blank.

View 10 Replies View Related

Queries :: Convert Boolean Fields To Integers / Blank For Null Values

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

Queries :: Creating Blank Records From New Part Of A Composite Key

May 10, 2013

I 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.

View 4 Replies View Related

Queries :: Field Criteria - Cannot Get Blank (Not Null) Records

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

Queries :: Calculated Field Is Blank For Records With Even Primary Key

Jun 17, 2013

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.

View 6 Replies View Related

Queries :: Filling Blank Field Into All Records Of Database

Nov 22, 2013

I am working with an inherited database. When this database was created, a large amount of data was imported. Over the course of time, I have added additional fields for tracking information. One such field is "Date Started."

Unfortunately, there are almost 500 records without this information and that is skewing some report results.

I would like to do is insert the date of 9/9/1999 into all records that have no data in this field. (This date is well before the creation of the database and would serve to indicate old records, whether or not they are still active.) Copy and pasting isn't working, and I can't do a find and replace, since there's nothing to find.

View 1 Replies View Related

Queries :: Recordset Is Showing As Empty But Query Wizard Returns Records

May 12, 2014

I'm trying to write record set contents to excel. My query runs perfect in access query wizard, but recordset showing as null. My VBA code

Code:

Dim cnn As ADODB.Connection
Dim recordst As ADODB.Recordset
Dim strSQL As String
Dim strPath As String
Dim appXL As Excel.Application
Dim wb As Excel.Workbook

[Code] ....

View 5 Replies View Related

Any Way Of Not Showing The Blank Record?

Oct 20, 2004

I've got a text box, populated by a query.
(it looks like a list because it's tabular view)

you can always see the empty record... any way you can stop it showing?

or failing that, I have a dblclick event on all the textboxes. But It doesn't work with the blank record..

this is quite frustrating.. any help please?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved