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 Replies


ADVERTISEMENT

Queries :: If Check Box Is Ticked Ignore 1 Criteria

Oct 15, 2014

I have 3 queries populating 3 subreports on a report, i have a checkbox on my main form i launch the report from - i reference it in a filter on the form to ignore certain error types - id like it do to the same in the queries that populate the reports

So the reports atm give - an over all count of error types, a count per person and then a breakdown on type per person

Thats all fine but id like the option to ignore one type of error (as we sometimes look at it and sometimes dont).

View 3 Replies View Related

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

Ignore Blank Parametres In Query

Jan 22, 2008

Hey

I have a form where the user can choose from a drop down menu, values like Name= James.90
Date=1/10/07.


Then it would return the record with James.90 And That date in it

But my problem is you have to choose a value for both boxes. Is there a way to ignore it if the field is left blank.

My current code is


SELECT timesheet.WeekEnding, timesheet.FName, timesheet.Company, timesheet.Project, timesheet.CTR, timesheet.Comments, timesheet.Total, timesheet.Project_Title
FROM timesheet
WHERE (((timesheet.WeekEnding)=[Forms]![FormBeta]![Week]) AND ((timesheet.Company)=[Forms]![FormBeta]![Company]) AND ((timesheet.Project)=[Forms]![FormBeta]![Project]));

View 14 Replies View Related

Ignore Blank On Query Expression

Jun 11, 2012

I have some expressions built to calculate totals in a query. Here is the first

Engineer Total: [Engineer Hrs]*[Engineer rate].

This works when a user adds hrs to a table and the rate is a default number.Then a total from 4 totals similar to the Engineer total are added together here:

Total Rate: [Designer Total]+[Quality Total]+[Coordinator Total]+[Engineer Total].

Works perfect if all 4 Hrs fields have data. The problem is not every entry will have hrs in each field..when this happens the Total rate does not calculate. So long story short, how do I make the Total rate bypass the blanks or do I need to make the Engineer total force a zero on blanks?

View 3 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 :: 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 :: 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 :: 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

Help With Wildcard Criteria On SubForm

Feb 21, 2005

I have a form with subform which use six fields on the form to determine
what data is returned in the subform. As long as every field has a valid entry the data is returned
correctly. What I would like to add to this is if a particular criteria field is left blank it will return all
values in that field. The current SQL Statement looks like this:

SELECT QryItemQtyProductLineLinked.FINISH, QryItemQtyProductLineLinked.SIZE, QryItemQtyProductLineLinked.COLOR, QryItemQtyProductLineLinked.BASISWGT, QryItemQtyProductLineLinked.PRODUCT_LINE, QryItemQtyProductLineLinked.MWIEGHT, QryItemQtyProductLineLinked.DFLT_VENDOR_ID, *
FROM QryItemQtyProductLineLinked
WHERE (((QryItemQtyProductLineLinked.FINISH)=[Forms]![FrmProductLineLookup]![MatchProduct]) AND ((QryItemQtyProductLineLinked.SIZE)=[Forms]![FrmProductLineLookup]![MatchSize]) AND ((QryItemQtyProductLineLinked.COLOR)=[Forms]![FrmProductLineLookup]![MatchColor]) AND ((QryItemQtyProductLineLinked.BASISWGT)=[Forms]![FrmProductLineLookup]![MatchBWeight]) AND ((QryItemQtyProductLineLinked.PRODUCT_LINE)=[forms]![frmproductlinelookup]![MatchProduct]) AND ((QryItemQtyProductLineLinked.MWIEGHT)=[Forms]![FrmProductLineLookup]![MatchMWeight]) AND ((QryItemQtyProductLineLinked.DFLT_VENDOR_ID)=[Forms]![FrmProductLineLookup]![MatchVendor]));

Thank you in advance for any help!

View 2 Replies View Related

Wildcard Criteria With Input Box

Feb 28, 2008

hi,

new to the forum so hello everybody,
what i have is a table of contacts. this table has a field called locations. i have created a queriy that askes me the location which works ok. i could also have the criteria that has like "lo*" in it

but what i really need it the query to run ask me the location and i only put in LO* OR BI* etc
and the results i get are either london or birmingham etc

thanks for you help
steve

View 2 Replies View Related

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

Adding Wildcard Character To Query Criteria

Aug 11, 2005

I've got a query that does exactly what this (http://www.access-programmers.co.uk/forums/showthread.php?t=89564&highlight=null+records) chap got his to do.

However I want to add a "*" character to the criteria to allow for searches with partial matches. Here's the criteria that works:

[title]=[Forms].[Search]![Title] Or [Forms].[Search]![Title] Is Null

However when I try the logical extension:

[title]=[Forms].[Search]![Title] & "*" Or [Forms].[Search]![Title] Is Null

this doesn't work, and nor does:

[title]=([Forms].[Search]![Title] & "*") Or [Forms].[Search]![Title] Is Null

WHAT DO I DO? I'M TIRED AND I CANT THINK STRAIGHT! :eek: thanks and sorry for being so dumb!

View 6 Replies View Related

Queries :: Left Function - NOT Ignore Leading Zeros

Aug 3, 2015

In my table I have a numerical field for case numbers. Our case numbers are in the format of YYMM000000 (YY = 2 digit year, MM = 2 digit month, remaining 0's are consecutive case numbers). In order to properly list the case numbers in descending order, I have

caseyr: Left([CaseNo],2) (which pulls the first 2 digits being the year)
officeno: Right([CaseNo],3) (which pulls the last 3 for our part of the case number)

This was working fantastic until I had to enter cases from 2009, ex: 0911000587

I have set the format for the CaseNo field to 000000000 everywhere it is. It displays the 09 cases perfectly, retaining the zero, however, my left function ignores it. In the case number of 0911000587, it pulls 91 as the first 2 digits. So, in the form that I list my cases in order, the 2009 cases are showing up first (because they're pulling as 91 instead of 09).

Is there anything I can do to force it to NOT ignore the leading zero?

View 3 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

Wildcard Queries

Aug 17, 2005

I am running a query that links to a "wildcard" form so that the user can basically run a query filtered on any field they want.

For some reason when I try to use wildcards along with Criteria in my query the query will not return any results. I know the link to the textbox is right because if I take out the wildcard and put an exact word from the table I get a result.

I have tried using many different combinations inclusive of the following:

Like [Forms]![Refurb-WildcardReport]![RefurbWildcard-Name]
Like "[Forms]![Refurb-WildcardReport]![RefurbWildcard-Name]"
"[Forms]![Refurb-WildcardReport]![RefurbWildcard-Name]"
[Forms]![Refurb-WildcardReport]![RefurbWildcard-Name]

Any help would be appreciated, as always thank you ahead of time.

View 14 Replies View Related

Date Queries And Wildcard

Jan 24, 2007

Hi
I am looking for ways to sort date columns.
Something like find all in one particular month, or year.

I know i have done this before to sort data into monthly queries,
but now i have forgotten
>> something like ="#*/7/*#" to sort all date on the 7th month.



By the way I cannot seem to get access to format dates in the
dd/mm/yy format, does anyone know if there is an option for this.

cheers

View 2 Replies View Related

Queries :: Wildcard In Query Fed From A Search Form

Jan 9, 2014

I have built a search form to feed information to a query. The form uses combo boxes tied to table values, and all have wild cards built into them so if the user leaves the combo box they get all the records. I also have to text boxes representing start date and end date. I would like to allow the user to leave these blank and get all there as well. I have been looking through my one Access book, as well as searched all over the internet, but I cant seem to find the way to do this. My filter criteria for the text based combo boxes are like this:

Code:
Like "*" & [Forms]![ReportDesignF]![Company] & "*"

My filter criteria for the Start and End Dates looks like this:

Code:
Between [Forms]![ReportDesignF]![StartDate] And [Forms]![ReportDesignF]![EndDate]+#11:59:59 PM#

In this case of the user leaves the date values blank, the query returns nothing. I would like to return all dates if that is the case. I am assuming it is my lack of knowledge of wild cards and how they work with date values.

View 14 Replies View Related

Queries :: Replacing Wildcard Characters In A Query

Jun 5, 2013

I am trying to replace a # character and all characters after in a text field.

U set up a Select query and used the following expression:
Expr1: Replace([Part Number],"[#]*","")

It doesn't change any of the records. Is this possible to do in Access 2007?

View 11 Replies View Related

Queries :: Wildcard Like Not Returning Desired Output

Jul 8, 2014

I'm building a query using wildcard "like". See the attached file. My question is that why doesn't the query "result" return "aaaaa" for "aaa" is contained within "aaaaa"?I'm basically limiting my records to those found in tbl_site based on a wildcard "like".

View 12 Replies View Related

Queries :: Wildcard Search Does Not Work With A Form Control

May 24, 2015

I am trying to get a wildcard search to work with a form.

I have a query, in which the criteria is:

Like "*" & [Enter a word] & "*"

That works fine. I enter a word, and I get the few records in which the word appears.But if I try to replace [Enter a word] with a word entered on a control on a form, it doesn't work - I get all the records. This is my code:

Like "*" & [Forms]![Myform]![Mycontrol]&"*"

What am I doing wrong?

View 8 Replies View Related

Queries :: Query To Return A Wildcard Search On A Field

May 21, 2015

How do I design a query to return a result in a wildcard format? So that I could enter a part of a name, and it returns all the names that include that part of name?

View 1 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

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

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







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