473,320 Members | 1,910 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Wildcard help

EJC
Hi Folks,

I've been trying to build a query from a form of dialogue boxes I
have created that holds search criteria for my main database. I have
been able to get the query to retrieve the data selected in the
relevant dialogue boxes and perform the query on these boxes. However
my problem lies in the fact that if no choice is selected in the
dialogue box I want the query to default (or get passed) a wildcard
that will mean it will return results of any value in that field.

I have tried passing it a '*' and a '%' by adding these values to the
choices available in the dialogue boxes and also adding these to the
default values however when I then select to run the query on these it
returns no results.

Any help much appreciated,

EJC
Nov 12 '05 #1
6 2091
EJC wrote:
Hi Folks,

I've been trying to build a query from a form of dialogue boxes I
have created that holds search criteria for my main database.


Could you define "Build A Query"?

I usually do something like this when building a query
Sub BuildIt
Dim strWhere As STring
Dim strSQL As STring
strSQL = "Select EmpID, EmpName From Employees "
If Not IsNull(me.text1)
strWhere = "Code = " & Me.Text1 & " And "
Endif
If Not IsNull(me.text2)
strWhere = "DateEmp = #" & Me.Text2 & "# And "
Endif
If Not IsNull(me.text3)
strWhere = "Status = " & Me.Text3 & " And "
Endif
'remove the word "And" and add the word "Where"
If strWhere > "" THen strWhere = "Where " & _
Left(strWhere,Len(StrWhere)-5)
strSQL = strSQL & strWhere & " Order By EmpName"
Endif
Nov 12 '05 #2
How are you running your query? Are you building the SQL in VBA?

ee******@hotmail.com (EJC) wrote in message news:<76**************************@posting.google. com>...
Hi Folks,

I've been trying to build a query from a form of dialogue boxes I
have created that holds search criteria for my main database. I have
been able to get the query to retrieve the data selected in the
relevant dialogue boxes and perform the query on these boxes. However
my problem lies in the fact that if no choice is selected in the
dialogue box I want the query to default (or get passed) a wildcard
that will mean it will return results of any value in that field.

I have tried passing it a '*' and a '%' by adding these values to the
choices available in the dialogue boxes and also adding these to the
default values however when I then select to run the query on these it
returns no results.

Any help much appreciated,

EJC

Nov 12 '05 #3
GJT
EJC

You can choose to return all records if no selection is made. There
are a couple of examples shown with this at:

How to Return All Records When Parameter Is Blank:
http://www.microsoft-accesssolutions..._parameter.htm

-OR-

Plan for Null Responses in Your Parameter Queries:
http://www.microsoft-accesssolutions..._parameter.htm

I hope this helps

******************************************
GJT
http://www.microsoft-accesssolutions.co.uk
******************************************

ee******@hotmail.com (EJC) wrote in message news:<76**************************@posting.google. com>...
Hi Folks,

I've been trying to build a query from a form of dialogue boxes I
have created that holds search criteria for my main database. I have
been able to get the query to retrieve the data selected in the
relevant dialogue boxes and perform the query on these boxes. However
my problem lies in the fact that if no choice is selected in the
dialogue box I want the query to default (or get passed) a wildcard
that will mean it will return results of any value in that field.

I have tried passing it a '*' and a '%' by adding these values to the
choices available in the dialogue boxes and also adding these to the
default values however when I then select to run the query on these it
returns no results.

Any help much appreciated,

EJC

Nov 12 '05 #4
Salad wrote:
EJC wrote:
Hi Folks,

I've been trying to build a query from a form of dialogue boxes I
have created that holds search criteria for my main database.


Could you define "Build A Query"?

I usually do something like this when building a query
Sub BuildIt
Dim strWhere As STring
Dim strSQL As STring
strSQL = "Select EmpID, EmpName From Employees "
If Not IsNull(me.text1)
strWhere = "Code = " & Me.Text1 & " And "
Endif
If Not IsNull(me.text2)
strWhere = "DateEmp = #" & Me.Text2 & "# And "
Endif
If Not IsNull(me.text3)
strWhere = "Status = " & Me.Text3 & " And "
Endif
'remove the word "And" and add the word "Where"
If strWhere > "" THen strWhere = "Where " & _
Left(strWhere,Len(StrWhere)-5)
strSQL = strSQL & strWhere & " Order By EmpName"
Endif


I'd do it slightly different.. but same result :)

If Not IsNull(me.text1)
strWhere = "([code] = " & Me.Text1 & ")"
Endif
If Not IsNull(me.text2)
if len(strWhere) > 0 the strWhere = StrWhere & " AND "
strWhere = "([DateEmp] = #" & Me.Text2 & "#) "
Endif
If Not IsNull(me.text3)
if len(strWhere) > 0 the strWhere = StrWhere & " AND "
strWhere = "([Status] = '" & Me.Text3 & "')"
' Not forgetting the single quote when doing string comparison:)
Endif

--
regards,

Bradley
Nov 12 '05 #5
EJC
Hi Again,

first off thanks for all the help, I have now achieved a further level
of the functionality I was after. I have followed this method
(http://www.microsoft-accesssolutions..._parameter.htm)
and adapted the parameter example to work with my combo box to achieve
this. This works fine for one combo box however as soon as I try to
add the same functionality to the other combo boxes on my form it
breaks down and I end up getting the empty record set returned. Any
ideas as to why this is?

Additional background info: Currently I have a form that holds combo
boxes with values I have entered myself. I have a button on this form
that runs a query thats criteria is based on the values that have been
selected from the combo boxes. If I select a choice from each combo
box it works fine. If I have one or more combo boxes left on their
default (which is now Null) it doesnt work.

Thanks for the code examples but I'm hoping that I wont need to resort
to this just yet but if I still cant achieve the functionality I am
after through menus I guess I will have no choice.

Sorry if the term 'build' sent you good people down the coding route I
did not intend for that at this stage, a forum lesson has been learnt
:)

EJC

ac*************@fizzmail.co.uk (GJT) wrote in message news:<f5*************************@posting.google.c om>...
You can choose to return all records if no selection is made. There
are a couple of examples shown with this at:

How to Return All Records When Parameter Is Blank:
http://www.microsoft-accesssolutions..._parameter.htm

-OR-

Plan for Null Responses in Your Parameter Queries:
http://www.microsoft-accesssolutions..._parameter.htm

I hope this helps

******************************************
GJT
http://www.microsoft-accesssolutions.co.uk
******************************************

ee******@hotmail.com (EJC) wrote in message news:<76**************************@posting.google. com>...

Nov 12 '05 #6
EJC wrote:
Hi Again,

first off thanks for all the help, I have now achieved a further level
of the functionality I was after. I have followed this method
(http://www.microsoft-accesssolutions..._parameter.htm)
and adapted the parameter example to work with my combo box to achieve
this. This works fine for one combo box however as soon as I try to
add the same functionality to the other combo boxes on my form it
breaks down and I end up getting the empty record set returned. Any
ideas as to why this is?


Yes. You need to adjust your query for each and every possible instance
of true and false. You only satisfied 1 criteria, you need to satisfy
all criterias.

That is the problem with your query....you need to have lots of ORs in
it to make it work. Maybe I should say lots or Ors and Ands to meet all
of the truth table results. In fact, If you have more than 2 or 3 text
boxes I would hate to be the one to modify your query later on. It will
become extremely complex and a pita if you ever need to update or modify it.

If your list in the combo is basically small, you might want to consider
creating a function and use it in the combo's query. Lets say the form
is called MF and the table fields to compare are Field1...Field3 and the
form fields are Text1...Text3. Create a column like this
PassFail : DoesItPass([Field1],[Field2],[Field3])

This will pass the 3 fields to compare to the 3 text fields on your form

In the criteria row for PassFail enter
True

Public Function DoesItPass(var1, var2, var3) As Boolean
Dim bln1 As Boolean
Dim bln2 As Boolean
Dim bln3 As Boolean
bln1 = True
bln2 = True
bln3 = True

If Forms!MF!Text1 > "" then
If NZ(var1,"") <> Forms!MF!Text1 then bln1 = False
endif

If Forms!MF!Text2 > "" then
If NZ(var2,"") <> Forms!MF!Text2 then bln2 = False
endif

If Forms!MF!Text3 > "" then
If NZ(var3,"") <> Forms!MF!Text3 then bln3 = False
endif

DoesItPass = (bln1 And bln2 And bln3)
end function

Now only those records in the combo query where DoesItPass is true will
be displayed. A function like this will be MUCH easier to modify and
update later on. BTW, this function would be created under the Modules tab.

Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Robert Brown | last post by:
I have researched newsgroups and the web very thoroughly and unsuccessfully for a solution to what I believe is a very common problem. I know it's easy to do wildcard match against data in DB...
1
by: Generic Usenet Account | last post by:
Here's the requirement that I am trying to satisfy: Handle wildcards in STL such that the wildcard entry is encountered before non-wildcard entries while traversing containers like sets and...
1
by: deko | last post by:
I have a form where users can enter a string with asterisks to perform a wildcard search. Currently, the string entered by the user looks like this: *somestring* The purpose is to match any...
1
by: Earl Teigrob | last post by:
Does someone have or know of an algorythm (method) that will delete all files under a give directory and its subdirectories based on a wildcard mask? I can use this for one directory for each...
2
by: guoqi zheng | last post by:
Dear Sir, I would like all files on my application to pass asp.net, so I added aspnet_isapi.dll to the Wildcard application maps. After that, many things changed. I used to be able to visit my...
6
by: Jan Kucera | last post by:
Hi, does anybody know about wildcard mapping ASP.NET 2 in IIS6? Any tutorial? Thanks, Jan
2
by: googlegroups.dsbl | last post by:
I'm really confused here, and am wondering if someone knows what could be the issue with my TableAdapter query. A few months ago, I created a really neat program that has th ability to search by...
0
by: Gordon.E.Anderson | last post by:
short description: i've got a .net web site set up using a tableadapter attached to a sql server table - returning results only, no update of data. i've got a query (qry code below) set up to...
7
by: BlackJack17 | last post by:
I have another newbie question for you guys... I apologize in advance for how simple it is... I've got a string of data, included in that string is 11* as it appears, the * is not a wildcard. ...
0
by: savage678 | last post by:
Hi Everyone, I am new to this forum and am i dire need of some help. I am trying to use wildcard searches in infopath. I have it connected to an access database using data connection. I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.