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 Replies


ADVERTISEMENT

Query Criteria With Blank And Non-blank Text Records

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

General :: Return Based On Criteria - Records With Less Than 5 Characters Or Blank

Aug 28, 2013

I have a table with Zip CODE field. I uploaded my information to this table form an excel file. There were over 120K rows of data so no way i could do this in excel.

So I found that some of the ZIP codes are either blank or have less than 5 digits.

Can I build a a query with the ZIP code field that will return all zip code records with less than 5 characters or blank. I'd like to repair these zip codes and a update query will be my next step.

I repeat excel is not an option because when I try to sort by the Zip Code column my computer explodes (okay not literally) ....

View 3 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 :: Omit Records With Blank Field - Criteria With IFF Statement And Checkbox

Apr 18, 2013

I'm having an issue getting my query to omit records with a blank field - in fact, it omits all records.

What I'm trying to do is:

I have a list of customers, with phone and email addresses. I want to filter via query for only customers with their email address's entered.

Here is what I have:

IIf([Forms]![AdvancedReporting]![Check230]=-1,"*",Null)

View 14 Replies View Related

Queries :: Ignore Wildcard If Criteria Is Blank

Dec 16, 2014

In a database am building, I want to run a query with the criteria dependant on which field the user populates in a form.

The form has a number of fields that the user can select from including our reference number, the client's reference number and the site address.

I would like the user to be able to select the site address using a wildcard so that they can enter a part of the address such as "This Street" instead of "45 This Street" and the user be presented with all of the records matching "This Street".

I tried using the criteria:

Code:
Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"

Which works perfectly as long as this field is populated. If this field is not populated, entering details in any other field bring up every record in the database.

Code:
IIf(IsNull([Forms]![SearchJobs]![SearchAddressLine1]),Null,[Forms]![SearchJobs]![SearchAddressLine1] & "*")

The full sql of my query is:

Code:
SELECT Jobs.JobsSalesEnquiryRecordNumber, Jobs.JobsJobStatus, Jobs.JobsEnquiryDate, Jobs.JobsTakenBy, ClientDetails.ClientDetailsURN, ClientDetails.ClientDetailsName, ClientDetails.ClientDetailsAddressLine1, ClientDetails.ClientDetailsAddressLine2, ClientDetails.ClientDetailsAddressLine3, ClientDetails.ClientClientPostCode, Jobs.JobsClientJobNumber, Jobs.JobsAlternativeClientJobNumber, Jobs.JobsClientContact,

[Code] ....

View 12 Replies View Related

Queries :: Blank Query After Setting Criteria

Feb 4, 2014

I've set up a form with a button to open a report based on the current name on the form. The idea is that as you look through the different pages on the form you can open a report for whatever one you're on and print it.

In my query I have a name field where I put the criteria: Forms!Formname.Textboxname

By clicking the button on my form I'm able to generate a report based on the name that appears in that textbox. It works great when I initially put it in but if I close the form (or query, or report) and open it back up it is blank.

Is this even possible with a text box? It seems like it when I open it, it has nothing to go off and that's why it's blank. I just don't know how to fix that.

View 7 Replies View Related

Queries :: Query Criteria - All Results If Blank

Aug 7, 2013

I use this on most queries where I need to return all results if the form field is left blank. Works like a charm every time...

Like [Forms]![frm_main_menu]![Week] & "*" Or Is Null

except for this time..I need to filter by week number (52 weeks in a year)...problem is if I enter week "1" I also get weeks "10, 11, 12, 13, 14, 15, 16, 17, 18 and 19".

View 4 Replies View Related

Queries :: Count And Sort Criteria - Returning On Blank Values

Nov 18, 2013

I have a list of employees and sort criteria. for example

empID....Criteria
1234......T
1234......F
1234......T
1234......F
1235......F
1236......T
1236......F
1236......F
1236......F
1236......T
1236......T
1237......F

The output I am looking for is a count of the number of times T appears by an employee, BUT is there is no record it would return 0

E.G.
empID......Count
1234...........2
1235...........0
1236...........3
1237...........0

I can get it to return:
empID......Count
1234...........2
1236...........3

using Count and the criteria Where Criteria="T" but not returning zeros.

View 2 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 :: 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 :: 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 :: 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 1 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 :: Pulling Records Based On Date Of Another Record?

Sep 29, 2014

I have a file of transaction history from the accounting system. All of the payroll cash payments are coded as ZG. Payroll accruals are coded as ZC. I need a sum of payroll accruals by department that have the same date or later than the last payroll cash payment. How do I write that query?

View 1 Replies View Related

Queries :: Update Table If A Field Is Blank Without Clicking Through All 5K Records

May 7, 2014

I am putting the finishing touches on a DB and have come up with a a problem which I cannot solve. I have code on my data entry form that concatenated the days and times of up to five days. That is : [Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") & " " & ";" and so forth for five days. It has a complicated (for me) nested IIF statement and it works fine on the form. But I have now transferred the data from an old DOS DB and I do not want to go through almost 5K records just to update three fields.When I tried to do it in an update query, it said that the string was too long for the update. My update is:

IIf([Day5] Is Not Null,[Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") & " " & ";" & " " & [Day2] & " " & "@" & " " & Format([Time2],"h:nn AM/PM") & " " & ";" & " " & [Day3] & " " & "@" & " " & Format([Time3],"h:nn AM/PM") & " " & ";" & " " & [Day4] & " " & "@" & " " & Format([Time4],"h:nn AM/PM") & " " & ";" & " " & [Day5] & " " & "@" & " " & Format([Time5],"h:nn AM/PM"),IIf([Day5] Is Null And [Day4] Is Not Null,[Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") &

[code]...

how to update the table if a field is blank without clicking through all 5K records? I have tried simple concatenation, but I usually get a lot of extra @ in the field.

View 2 Replies View Related

Setting Query Criteria To Be 'blank' Depending On The Criteria Of A Combo Box

Oct 21, 2006

I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).

However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.

Any help would gratefully be appreciated. Thanks

View 5 Replies View Related

Blank New Form Is No Longer Blank?

Apr 11, 2006

Hi guys,

I have been running the same database on numerous systems for 5 years and the user clicks on a button and using this code;

DoCmd.OpenForm "Employee", acNormal

Simple !, this always opened this form blank, without applying filters, ready to accept a new employees details.

But now this no longer works, for no apparent reason?
What happens is that it opens with the form fields filled with the first person in the Databases details instead of being blank.

The only thing that could have changed is that i converted the database to 2003?

Any ideas?

Thank you in advance

View 5 Replies View Related

Criteria In Query Leaving Blank Table

Apr 30, 2007

I am trying set up some criteria for a create table query. I am using multiple fields from different tables, I am trying to delete certain feilds if they meet the criteria. I am using iif([letter c_last]=[letter a_last],null,[letter c_last]) in the criteria for letter c last, but when I run it a box appears to enter criteria for the last name field? I want it to look at the field not for data to be entered manually. I hope this makes sense. Any help would be greatly appreciated.

View 1 Replies View Related

Queries :: When One Sub Query Has No Records Entire Main Query Is Blank

Oct 1, 2014

I have a query with three sub queries, all returning a number from different tables. But when any one of the sub queries returns a blank, the entire main query is blank.

How do I stop this from happening? I have tried NZ() on the sub queries and on the main query, but the blank still happens.

View 1 Replies View Related

Forms :: Way To Continue To Blank Form After Entering Information Into Previous Blank Form?

Mar 25, 2013

When entering information into a blank form, I would like to be able to continue entering information to another additional blank form after my last entry. Is there a way to continue to a blank form after entering information into the previous blank form? I would just like to continue without having to close the entire form and then reopening another form.

View 7 Replies View Related

Blank Date Field Default On Query Criteria

Oct 8, 2007

Hello.

I have a query that is pulling data based on a date in a table. For some records, that date field is empty. For those records, the data should be pulled based on the date of 1/1/06. I tried doing this
=IIf([Signed SLA Received] Is Null,1/1/06,>=[Signed SLA Received])

It doesn't like this at all, it brings back no data. I also tried putting 1/1/06 in quotes, but it didn't make any difference.

This is probably a totally incorrect way to get this done, but I don't know how else to try it.

Thanks!
Stacey

View 3 Replies View Related

Forms :: Pulling Data From A Query Based On Criteria

Apr 13, 2013

I am having a problem pulling some data from a query to populate text boxes in a form

Text27 = DLookup("'SumOfSumOfDocCount'", "SumTotalPerf", "DateReceived=" & Forms.Tracker.Text23.Value & "AND 'BookedInID'=" & Forms.Tracker.BookedInID.Value)

I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.

View 8 Replies View Related

Showing Subform Based On Whether Or Not A Field Is Blank Or Null

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







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