By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

Wildcard help

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.