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 Replies


ADVERTISEMENT

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

Modules & VBA :: Search Form Will Not Return Exact With Wildcard SQL

Jul 4, 2014

I did export the table data into a tabbed delimited format and will include that at the end. If you want to reproduce my bug copy that data in a txt file and import that into the table tblMain. Make a search form and a sub form. the sub form is linked to the table and the main form is unbound with two search buttons.

Problem Statement:The code works fine. I did find a bug that seems to arise with the wild cards when the entire field values are entered. You can replicate the bug by testing the search criteria listed below.

This is a brief example of the bug. A detailed description is near the code below.

If my name is "Devtycoon" and I search "Dev" the SQL statement will build "*Dev*" and it will pull up my name, "Devtycoon". On the contrary if I search "Tycoon" the SQL statement will build "*tycoon*" and it will pull up my name, "Devtycoon". If i search "DevTycoon" the sql statement will build "*Devtycoon*" and no results will be returned. That is buggy because the name is in the database but no wildcards would be needed.

Table structure:

tblMain
ClientID
Surname [text]
Organization [text]
ProgramTitle [text]
City [text]
State[text]
Zip4 [text]
Telephone [text]

Form1 contains the controls for search criteria. Three text box controls are used to filter a sub form control called "DS". The sub form is called sFrmMain and is a datasheet that shows results of the search. there is a button that runs the code and another button that clears search criteria and shows all records. Both button's code set the sub form's record source using an SQL string built using a function that returns a segment of the overall search string using the contents of each control that then is concatenated into a SQL statement used to set the record source.

There is a function for the following components of the SQL statement

SELECT / FROM
WHERE
controlA = me.txtSurname
controlB = me.txtOrganization
controlC = me.txtProgramTitle
This is how you can replicate the bug.

I tested two additional surnames organizations and program titles as follows:

Try example (1). you will get both the 4's and the 14's records returned.

If you type in letter for letter of the second record (the one with the 14's) no records populate. It is like the wild card does not like it if you type in the entire field value. Uncle Gizmo's and Allen Brown's method do the same thing were no records populate if the 14's entire surname organization and program title are entered into the text controls. Can you reproduce this error? Other than that I think either method is bulletproof.

Example criteria

1) put the following criteria in each control then hit the search button

4 surname
4 organization
4 program title
[two records returned]

2) put the following criteria in each control then hit the search button

[no records returned]
14 surname 14
14 organization 14
14 program title 14

Code behind search form:

Code:
Option Compare Database
Option Explicit
Private Sub cmdShowallRecords_Click()
Dim strSQL0 As String
Me.txtOrganization = ""
Me.txtProgramTitle = ""
Me.txtSurName = ""
strSQL0 = fSQL_SelectFrom & fWhere & fSurName & fOrganization & fProgramTitle
Me.DS.Form.RecordSource = Left(strSQL0, Len(strSQL0) - 5) & ")"

[code]....

View 12 Replies View Related

Forms :: Filtered Search Form Using Asterisk As Wildcard Default Value In Combo Boxes

Aug 23, 2013

I'm trying to make a filtered search form using "*" as a wildcard default value in combo boxes, this works for all the text fields except for the account number field (Numeric primary key). After quite a bit of reading up and searching, I tried using the following as the row source;

SELECT customers.ACCOUNT_NO, customers.CUSTOMER FROM customers; UNION SELECT "*", "All" From Customers;

But am still getting "#Error" in the combo box.

View 2 Replies View Related

Forms :: Filter A Form Based On A Field With Wildcard - Make Search Case Insensitive

Jan 9, 2014

Access 2003

Trying to filter a form based on a field with wildcard. My form has a txtCustFilter control where a customer's name can be entered in part or whole. The Customer's name is in PCCustomerName

This code works but, I'd like to make it case insensitive

Dim strFilter As String
strFilter = "[PCCUSTOMERNAME] LIKE ""*" & Me.txtCustFilter & "*"""
Me.Filter = strFilter
Me.FilterOn = True

View 9 Replies View Related

Queries :: Search Form That Uses A Query To Show Results Of A Search

Aug 5, 2014

I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.

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

Wildcard Search Funtionality

Jun 13, 2005

I am working on a form that will be used to search records in a table. I have created the query shown below:

SELECT tblSTC.STC, *
FROM tblSTC
WHERE (((tblSTC.STC) Like forms!frmsearch!txtsearch));

I want my user to be able to enter search criteria and allow the use of a wildcard. When i run the query no data is found??

Can anyone help, thanks.

View 3 Replies View Related

Wildcard Search Using Visual Basic Problem

Sep 8, 2005

first i must thank everyone here. this forum has proved invaluable for some of the simple databases i have created. now i have a problem which i cannot find the answer for.
i'm using access 2000
basically i am creating a cemetery database so most of the information i'm dealing with is duplicated. to create a unique record i have six primary keys on the one table. ie people with the same name, age, and birthday can be buried in the same grave.
anyway i need to do a wildcard seach from a form (QBF) based on surname and firstname. i have downloaded the code below and modified it:

Private Sub cmdsearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As querydef
Set dbNm = CurrentDb()

'Constant Select statement for the Query definition
strSQL = "SELECT searchtestdata.Surname; searchtestdata.Firstname; " & " FROM searchtestdata"

strWhere = "WHERE"

strOrder = "ORDER BY searchtestdata.autonumber;"

'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.txtsurname) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & " (searchtestdata.Surname) Like '*" & Me.txtsurname & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtfirstname) Then
strWhere = strWhere & " (Searchtestdata.firstname) Like '*" & Me.txtfirstname & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("quesearchtestdata")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder

'Open the Query
DoCmd.OpenQuery "quesearchtestdata", acViewNormal

End Sub

the code stops at the line:

qryDef.SQL = strSQL & " " & strWhere & " " & strOrder

and tells me either that i have "run-time error 3142 - characters found at end of SQL statement" - this is the error i am recieving at the moment, or more usually " run-time error 3141 - which tells me the SELECT - WHERE statement is wrong.
i am definetly no expert in VB - i am only just starting using it.
so guys any idea?
thanks in advance

View 9 Replies View Related

Modules & VBA :: DLookup - Wildcard Search On Both Sides?

May 27, 2015

When trying to match people's names - sometimes with a middle initial, sometimes without - I'd like to try to wildcard each side of it.

So this

Code:

varProblem = DLookup("EMAIL_ADDRESS", "tblUsers", "EMPLOYEE_NAME Like '" & varString & "*'")

but also something like this.

Code:

varProblem = DLookup("EMAIL_ADDRESS", "tblUsers", "EMPLOYEE_NAME* Like '" & varString & "'")

View 8 Replies View Related

Modules & VBA :: Edit A String Or Use Wildcard In Search Function

Jul 18, 2013

Access 2007

I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.

Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.

Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.

Function Search()
Dim lssql As String
Dim lsSn As Recordset
Dim db As Database
Dim lsMessage As String
Dim sMsg As String
Dim vRetVal As Variant
Set db = CurrentDb()

[Code] .....

View 2 Replies View Related

Queries :: Search Query Based On Form?

Nov 13, 2013

I am unable to get multiple search output using the below code in queries, the search text is based on form :

Search form name : Find
Query name : Search
Table name : Sheet1
[forms]![find]![city]

The above works fine if it is only used in one of the fields, but as soon as I have it in multiple fields the output is null.

I am aware that i can used Like "*" code, but the thing I want to find records which are perfectly matching based on the form.

View 14 Replies View Related

Queries :: Custom Search Form Query?

Jan 28, 2015

I have made a database for work and is fully functional, but theres one thing I want to add but cant get my head around how to do it.

I have created a Form called 'Filtered Search', on the form it has multiple combo boxes for 'Auditors' 'Area' 'Status' and 2 text boxes for date range.

I want to be able to set what filters I want, and for the query to ignore any fields with no information selected/inputted (i.e. I want to see all records raised by "Mr Smith" (Auditor) that are still 'Active' (Status) in all areas at any time).

Names of items;

Table = 'Incidents'
Form = 'Filtered Search'
Report = 'Filtered Report'
Auditor = 'Combo7'
Status = 'Combo156'
Area = 'Combo5'
Date Range From = 'Text161'
Date Range To = 'Text163'

View 5 Replies View Related

Forms :: Applying Parameter Based Wildcard Search On Subform Filter

Jul 31, 2014

I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.

I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.

I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.

View 3 Replies View Related

Modules & VBA :: Wildcard Search Of Combobox - Lookup Values In Table Column

Nov 19, 2014

I have a form which has a combobox called Task_Ref which looks up values in a table column.

I would like to be able to set the tickbox value of tickbox called P1 to True if the combobox contains the word "test", each entry on the combobox selection may vary such as:-

Test number 1
Yesterdays Test

As long as the word "Test" appears I would like the above to happen?

I was thinking of something along the lines of:-

If InStr(Task_Ref.Value, "Test") > 0 Then
P1.Value = True
Else
P1.Value = False
End If
End Sub

But this hasn't worked

View 4 Replies View Related

Queries :: Create Query That Takes Values From Search Form And Provides Records

May 5, 2015

I am trying to create a query that takes values from a search form and provides records. I was having issues with getting results when some boxes on the form were left blank. I found a solution to that and it worked with a small number of fields. However when I make the full form query (about 8-10 fields) and run it says the query is too complex. I wrote the sql as I could not get designer to do what I wanted. Attached is the sql that works and that which is "too complex".

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

Update Query Using IIF And Wildcard

Feb 1, 2006

Hello,
I am not sure if this is possible, but I am running an update query to add text in a field if a certain criteria is met. However, if the field I am adding to already contains what I am trying to add, i don't want it to add it again. Everything works fine except the IIF statement in my update field. Here is what I have:


IIf(Temp_Vendor_Trips.[Reco Classification]="*" & "V3" & "*",Temp_Vendor_Trips.[Reco Classification],Temp_Vendor_Trips.[Reco Classification] & " " & "V3")


If the field contains the text V3, I want it to be left alone and continue to show what is already in the field, if not, i want it to add V3 to the end.

Thanks in advance for the help!

View 1 Replies View Related

Include Wildcard Option Within Form

Feb 23, 2006

This may well be a simple question.

I have set up a Form to report Client acitivity by department Code for a given month. Users have the option within the form of selecting a Department Code.

These department codes are derived from a department Code Table and set accordingly within my Row Source properties.

If I wanted to select multiple departments for a particular report. how can i allow for wildcard entries i.e if several department code begin with letters OD*?

Thanks

Paul

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

Query- Assuming Wildcard If Control Is Null

Jul 12, 2006

Hi,
I'm making a query which is used to generate a report. Anyways, I've ran into a problem. The query is based on some controls in a form, but what I would like to do is that if a control was null then a field in the query should be assumed to be a wildcard for the criteria, otherwise the data in the control should be used in the criteria of the query. Does anyone have any ideas if this could be done?

Cheers,
Ben

View 14 Replies View Related

Query That Finds Parts That Start With * Wildcard

Feb 26, 2007

I have a sales invoice table that contains parts that start with *, for example *SPR362.

I need to select only those parts that start with the *, as this is the wildcard in queries how do i select those products that start with it without it being recognised as the wildcard and selecting all records?

The field name is simply: Part

:o

Many Thanks

View 4 Replies View Related







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