IIF Statements With Wildcards

Sep 14, 2005

I've got a query that is defeating me, but that might be due to a week's worth of insomnia related sleep problems. My issue exists in the following query, of the four IIF statments in the HAVING portion of the query and the need to select all if the statement is false for each one of the four queries, the way it is now partly works by returning all results where none of the values are null, I need to get null results as well. I've tried not including the false action for them, a wildcard to select all and just can't get around this problem. Any help appreciated.


SELECT DISTINCT vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number,
[AOI Category groups].ProductType AS Category, dbo_Contact.Source_of_Inquiry,
dbo_Company.Rn_Descriptor AS Store_Name, vw_mdb_SalesSummary.Job_Number,
Sum(vw_mdb_SalesSummary.Extended_Price) AS Sales1, vw_mdb_SalesSummary.Designer_Code,
vw_mdb_SalesSummary.Manager_Code, 0 AS Sales2

FROM ((dbo_Company RIGHT JOIN vw_mdb_SalesSummary ON dbo_Company.Store_Number = vw_mdb_SalesSummary.Store_Number)
LEFT JOIN dbo_Contact ON vw_mdb_SalesSummary.Job_Number = dbo_Contact.Job_Number)
LEFT JOIN [AOI Category groups] ON vw_mdb_SalesSummary.Category = [AOI Category groups].SalesCategory

WHERE (((vw_mdb_SalesSummary.Order_Date) Between [Forms]![frmLandS]![currentStart] And [Forms]![frmLandS]![currentEnd]))

GROUP BY vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number, [AOI Category groups].ProductType,
dbo_Contact.Source_of_Inquiry, dbo_Company.Rn_Descriptor, vw_mdb_SalesSummary.Job_Number,
vw_mdb_SalesSummary.Designer_Code, vw_mdb_SalesSummary.Manager_Code, 0

HAVING (((vw_mdb_SalesSummary.Channel)=IIf([Forms]![frmLandS]![Channel]<>"",[Forms]![frmLandS]![Channel],[Channel]))
AND ((vw_mdb_SalesSummary.Store_Number)=IIf([Forms]![frmLandS]![Store]<>"",[Forms]![frmLandS]![Store],[vw_mdb_SalesSummary]![Store_Number]))
AND (([AOI Category groups].ProductType)=IIf([Forms]![frmLandS]![AOI]<>"",[Forms]![frmLandS]![AOI],[ProductType]))
AND ((dbo_Contact.Source_of_Inquiry)=IIf([Forms]![frmLandS]![source]<>"",[Forms]![frmLandS]![source],[Source_of_Inquiry])));

View Replies


ADVERTISEMENT

Help With Wildcards

Oct 24, 2006

hi guys.. i want to include a search form in my database.. here is my current SQL statement:

SELECT tblCompany.CompanyIDPK, tblCompany.CompanyName, tblCompany.CompanyAddress, tblCompany.ContactPerson, tblCompany.ContactPosition, tblCompany.OfficeNo, tblCompany.MobileNo, tblCompany.FaxNo, tblCompany.EmailAdd
FROM tblCompany
WHERE (((tblCompany.CompanyName)=[Forms]![frmSearchClient]![txtSearchFor]));

but this statement only returns the exact name of the company... if the name of the company in the database is ABC company and you searched for "ABC", it will return zero results... so how can i improve on this? that even if they enter only "ABC", it will return a result.

thanks a lot and good day...

View 3 Replies View Related

Wildcards

Mar 22, 2005

Hi all

I'm making a student report card. Teachers enter student data via a bound form. I have a text field that teachers enter their class which brings up student names. They fill oput the form accordingly.

I have 6 classes of grade 7. 7A, 7B, 7C and so on.

If a teacher enters something weird like (7 (space) A) the query bound to the form will return nill results because it's looking for something that doesn't exist. I need an error message to appear which says no records will be returned because you haven't entered the correct parameter value. (Because they have entered 7spaceA rather than just 7A.

I could just use a combo and this would solve all my problems. But it's not always practical. I was thinking I could use a wild card where if the first character was a number then at least some values would be returned. There is method to my madness... I actually want to apply the same code elsewhere when running reports.

Is there a way I can enforce some kind of input mask in an event proceedure that can do this??

I appreciate any advice.

View 4 Replies View Related

Wildcards In Combobox

Nov 8, 2007

Hi,

I have data of type "number" shown in the drop-down combo-box in a form. It has some codes in the event of "After Update".

My problem is that i can't type in something like 13* in the combo-box in order to get anything starts from 13. It says "The value you entered isn't valid".

The reason is that i want user to be able to use the wildcard. I know that i can do this with text. I'm not allowed to change the structure of the table in the database.

This combo-box shows IDs from one of the exisiting tables. That IDs is of type "autonumber" in the table.

Is there a better way of doing this?

Thank you in advance

View 1 Replies View Related

OrderBy With Wildcards

Oct 13, 2006

I am trying to get the OrderBy to recognize wildcards (or to that effect). A scenario would be I would have more than one name in a field, EX: Name2, 4, 6. How do I use wildcards in the OrderBy and they are recognized.

ORDER BY (Table.Field)="Name1", (Table.Field)="Name2", (Table.Field)="Name3", (Table.Field)="Name4", (Table.Field)="Name5", (Table.Field)="Name6", (Table.Field)="Name7", (Table.Field)="Name8";

View 2 Replies View Related

Using Wildcards With A Combo Box

Jul 27, 2007

Hi

Im trying to create a query that uses a combo box as the filter criteria. However the combo box needs to use wildcards, which i would have thought would have been *[forms]![control]![combo1]*. However this doesnt seem to work, anyone know what im missing?


Cheers

Michael

View 1 Replies View Related

Queries :: Like Condition Using Wildcards

Apr 5, 2013

I have a table called Coded_Data with one particular field called Codes. This field list a number and a respective code to identify what type of code it is. For instance 123456789-AD...123456789-ADS. I am wanting to create to two columns. One listing all work orders coded with AD and the other withADS. When I use the Like condition with % it returns both AD and ADS in same column. I understand why because the "%AD" returns anything that starts with AD so this is why I get both codes. I only want to get the the AD codes in one column and ADS in the other. I have duplicated the table twice since I am trying to retrieve data from the same field and table but display in 2 separate columns. The duplicated table has a different alias. The problem is in the where clause.

where (((coded_data.codes) LIKE "%AD")) or ((coded_data_1.codes) LIKE "%ADS");

View 3 Replies View Related

Encapsulate A Form Field With Wildcards

May 19, 2005

I have a query that has a criteria from a field on a form...

[forms]![frmSearchCriteria]![Company_Name]

I would like to put wild cards * on eitherside that it uses will bring up with anything with what is written into the field.

I have tried putting wild cards on the form field and on the criteria of the query. Any Ideas? thanks

View 3 Replies View Related

Filter Query Wildcards Won't Work-pls Hlp

Jun 6, 2005

I'd be grateful if someone could offer some help with this to a frustrated Access novice : )

I have a query that filters records according to the value of an unbound text box in my form (basically search on last name)

I want to introduce wildcards into the filtering process but cannot get it to work.

Here's what I've done:

In the criteria field I had
[Forms]![MAIN]![Text440]
refering to the unbound text box on the form already mentioned.

This worked fine, but the following:
Like "*[Forms]![MAIN]![Text440]*" does not.

Nor does various combinations of brackets such as:
Like "*([Forms]![MAIN]![Text440])*"

In fact it now doesn't return anything, even if given a positive match. I have trawled help and the forums but to no avail. I have also tried the % character instead of *

I am using Access2000...

View 2 Replies View Related

WildCards Referencing Null Entries

Jun 24, 2005

I have a Qry that has a criteria of

Like "*" & [Forms]![FrmSearch![Cmbo1]

However on this field there are null field entries and even if the cmbo box is null it wont display these results.

I tood out the & [Forms]![FrmSearch![Cmbo1] section and just left the wild card and again only displayed the records where this field had an entry.

How can i get it so that when no selcetion has been made in the combo box all the records show up.

Many Thanks

View 2 Replies View Related

Queries :: Search Query With Wildcards?

Jun 15, 2013

I have created a simple Unbound search form based on a 'Bookings' table. There are 4 comboboxes that the user can choose to search on, 'Client_ID', 'BookingType', 'FundingArea' and 'ChargeTo'. I have created a query that uses the following criteria;

Like "*" & [Forms].[frmLBT_db_Tool].[cmbName] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbBookingType] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbFundingArea] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbChargeTo] & "*"

Using this criteria any of the comboboxes can be left blank and the results will display the chosen combobox used, or if 2 comboboxes were used the results would be further filtered.

The problem I have is that if a name was chosen (cmbName - Client_ID) and the Client_ID was 2, then all Client_ID records which have a 2 in them are returned, 2, 12, 20-29,32,42,52 etc...

Is there a way to overcome this, or am I going about it all wrong?

View 8 Replies View Related

Queries :: Updating A Table Using Wildcards?

Jun 21, 2014

Is it possible to update vlues in a table using wildcards. I want to use a query to search for certain records that meet a certain criteria and modify these values like changing particular dates in the date field to new values.

View 1 Replies View Related

Is There A Way To Include Wildcards In MS Access Parameter Queries?

May 4, 2008

I have a table with a string field for a street. It might have data such as:

Bleeker Street
or Bleeker St.

I want to create a query that will enable the user to search for a string by entering a parameter. Is there a way to construct a query using wildcards for a string so that if the user types in "Bleeker" both the above values will be returned?

Thanks.

View 2 Replies View Related

Forms :: Using Wildcards In Fields - Filter By Form

May 13, 2013

Been trying to use wildcards in fields while Filter By Form instanced in Access 2007.

LIKE, BETWEEN, IN or */% not available?

The form is based on a query. I am guessing this is why the drop downs only show IS NULL or IS NOT NULL as choices instead of the entire unique list existing in the underlying table/query.

View 2 Replies View Related

General :: Using Wildcards To Find Number In A Sentence

Mar 4, 2013

I should find the position of a number in a sentence and later extracted. The number is attached always to an "E". ie: E1, E2, E3,E4. The range of the number is 1-4. I have this until now:

X: InStr("E[#]",[Col1_Comment_1])

but it always return 0

View 5 Replies View Related

How To Enable Users To Use Wildcards In Their Searches On A Form

Jul 22, 2013

I have a Excel spreadsheet with an embedded query that pulls from an Access table. The users use a spreadsheet with an embedded query to search on a field called "Circuit ID", to bring up results showing the history of a given circuit id. Currently they have to enter an exact match, for example if the circuit id value is DHECHUIOY, they need to enter this exact value DHECHUIOY. They want instead to be able to enter DHEC and a wildcard character to bring back every value that begins with DHEC. There doesn't seem to be a way to do this in a spreadsheet with an embedded query.

I finally decided that it would be easier to create a form in Access

How I can create a form which allows users to search on a value using wildcard characters ? (the user enters the wildcard character).

I'm on Access 2012, Windows Vista

View 1 Replies View Related

Queries :: Wildcards - Find Any Number Of Numeric Values

Oct 3, 2013

How do you search for any number of numeric characters using wildcards? The # symbol only searches for 1, and * obviously includes letters.

Example:
I have:
D1234
D3
D5336767
D123F

My search should only retrieve the first 3 values.

WHERE FieldName LIKE 'D[0-99999999]' does not seem to work.

View 5 Replies View Related

Queries :: Removing Text From String Using Replace Function And Wildcards

Apr 25, 2014

I have a list of consumables;

Syringe 50ml
Syringe 20ml
Syringe 5ml
Syringe Cap
White Needle

I want to remove only the number and the ml part from the list, so I would end up with;

Syringe
Syringe
Syringe
Syringe Cap
White Needle

If I use

PHP Code:

Replace([DrugNameVial],"50ml","") 

I get the desired result for the 50ml syringe size.

I have tried every possible combination of "**ml", "##ml", "Like [0-9]ml all with no success.

How this can be resolved without having to individual enter each syringe size "5ml", "20ml" etc

I can't even just take the text from the right till the first space as this would lead to problems with other consumables in the list.

View 5 Replies View Related

Forms :: Searching Forms Using Combo Boxes With Wildcards

Jul 9, 2013

So I've got a form set up, and it uses a combo box to find the name of a persons record to populate the form. Simple enough stuff, the wizard takes you through it. It works fine.

However the people that use the database have kinda thrown me a curveball by asking if the search function can search any part of the name. For example, you've got a John Smith. If you enter Smith into the combo box, it won't find the record because it's the second name, you have to type in John.

Is there a way to use wildcards in the combobox so you can type in first or last names and get the same normal combo box effect?

View 6 Replies View Related

I Need Help With Using IIF Statements

May 16, 2007

Can anyone give me any guidance on how to use IFF statements in microsoft access? or Has anyone got a link to a good website that can help me? I need to use the formula below to determine if the quantity of an order from a store is greater than the quantity that is in stock at the warehouse and produce a message message.IIF([quantityordered])(=[quantityinstock]"enoughstock","notenoughstock") I just dont know where to start, i didnt even know you could enter formulas in Microsoft Access so i really need help

View 2 Replies View Related

If Statements

Mar 8, 2007

Hi, I have another problem with my Access database now...

I am trying to disable fields when a certain value is chosen. I can do it for one field with this code:

If ALLOCATION_LP = "Other" Then
OTHER_ALLOCATION_LP.Enabled = True
Else
OTHER_ALLOCATION_LP.Enabled = False
End If

That one works perfectly well, but I have another one that needs to disable two fields instead. If they choose "No Extant PP", I want it to disable two fields; one combo box - PLNG_PERMISSION_STATUS_2 (which I can get to and it works) and also an "Other" text box - OTHER_PLNG_PERMISSION_STATUS_2 (this is where i get stuck). I have tried two If statements, but whether I'm writing them wrong, I'm not sure.

I would really appreciate the help. Thanks.

View 2 Replies View Related

Two Where Statements

Feb 5, 2008

'construct a where clause as required
If Nz(Me.Manufacturer.Text, "") <> "" Then
Where1 = "WHERE MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
End If

Me.list.RowSource = _
"SELECT ID, Description, Par, MaxCoins, PayLines " & _
"FROM MachineTypeQuery " & _
Where1, Where2 & _ <- how do i get something like this to work
"ORDER BY Description;"


Exit Sub
Where2 comes from another control and has to be filled in before they get to this control. also "where1" and "where2" are set as public stings

View 6 Replies View Related

And Or Statements

Feb 25, 2005

I want to check eight Yes/No fields. If none of the boxes are checked I want to display a msgbox. I can link seven of the fields (no problem), when I add the eighth it doesn't work.

Am I limited to seven fields in my statement? Is there a way around this?

Any help would be appreciated.

============

If Me.Printer = 0 And Me.Hardware = 0 And Me.Software = 0 And Me.MSOffice = 0 And Me.Network = 0 And Me.NewComputer = 0 And Me.Profile = 0 And Me.Other = 0 Then

MsgBox "You must enter the type of problem(s) addressed with this customer", vbQuestion, "Type of Problem(s)"

Else
blah blah
End if

View 2 Replies View Related

SQL Statements

Nov 5, 2004

I want to select all records where a specifc field data start with specific data.

I'm having a meltdown because I know if is easy. If have tried Like "*" but that doesn't work. What is the command for Starts with?

View 4 Replies View Related

IF Statements

Feb 28, 2007

My database has a field named "Note". This field will either have a "Y" or "N" in it. At the bottom of a report I'm writing, I want to count the number of "Y's" (not the "N's") at the bottom of my report. Can someone tell me how to do this? Thanks so much in advance!

View 1 Replies View Related

Iif Statements

Apr 2, 2007

hey all

i have a list of values that i'm trying to run through an iif, like so:

IIf(([Warehouse Location].[ToAISLE] Like "1" Or "2" Or "3" Or "4" Or "5" Or "6" Or "7" Or "8" Or "9" Or "10"),([Product Inventory with totals].[Available]+[Warehouse Location].[Qty]))

im doing that in a query, not the vb code. now heres my problem: that's not the correct syntax for the Like blah blah blah part. does anybody know the correct syntax for this?

thanks!

View 2 Replies View Related







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