Re: OpenForm with Where Clause

So how would I do a pattern match on a field that had data like DMH10257 or
DMH10096?

Mike

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:4432812e$0$2141$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Matching numbers based on the string value of the digits is not generally
useful. For example:
Like "1*"
matches 1, 10, 11, ... 19, 100, 101, ...
which is not really a useful sequence of numbers.

Access will do it, but it is not normally a good design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Charney" <nothing@xxxxxxxxxxxxxx> wrote in message
news:FZuYf.55326$F_3.45096@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
So what happens if I have a text field with numbers in it? Can I use the
str( ) function to change the number to a string, and will it still
search on the filed?

Mike

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:4431c200$0$2149$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Use the Like operator with Text type fields (not dates or numbers.)

Text fields need quotes as delimiters.
Use * as the wildcard, or % if the back end is Sql Server.

Example:
Dim strWhere As String
If Not IsNull(Me.txtFindCity) Then
strWhere = "(City Like ""*" & Me.txtFindCity & "*"")"
End If

If you want an example of how to filter a form based on any combination
of unbound search controls on different types of fields, try:
http://allenbrowne.com/unlinked/Search2000.zip
The example requires Access 2000 or later. The code is documented, but
there is no explanatory article.

"Mike Charney" <nothing@xxxxxxxxxxxxxx> wrote in message
news:olcYf.61747$H71.973@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thanks Allen,

That took care of the between condition I needed. Do you know anything
about the Like condition in the where clause?

Mike

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:44313f26$0$2151$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Check out the example code for Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

The code explains how to build the WhereCondition based on whichever
boxes have an entry. You do exactly the same thing for OpenForm as it
explains for OpenReport.

"Mike Charney" <nothing@xxxxxxxxxxxxxx> wrote in message
news:fXaYf.63820$Jd.3267@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am trying to write an Open Form command in VBA with a where clause
in it.

I need to be able to do two different where clauses:

1) With a between in it: i.e. Between Date1 and Date2 or Between
Data1 and Data2

2) With a Like in it: Like *data1* or Like *date1*

3) Or a statement with both Between and Like in it.

I do not know how to make these statements?




.

Relevant Pages

  • Re: OpenForm with Where Clause
    ... which is not really a useful sequence of numbers. ... Dim strWhere As String ... combination of unbound search controls on different types of fields, ... anything about the Like condition in the where clause? ...
    (comp.databases.ms-access)
  • Re: OpenForm with Where Clause
    ... Dim strWhere As String ... unbound search controls on different types of fields, ... Tips for Access users - http://allenbrowne.com/tips.html ... about the Like condition in the where clause? ...
    (comp.databases.ms-access)
  • Re: OpenForm with Where Clause
    ... Matching numbers based on the string value of the digits is not generally ... which is not really a useful sequence of numbers. ... of unbound search controls on different types of fields, ... about the Like condition in the where clause? ...
    (comp.databases.ms-access)
  • Re: Help! Outer Join problem
    ... dateTime type. ... you could end up with a problem of string comparisons. ... query and then bring that into another query with the other two tables. ... Try the following as the FROM clause (and you should be able to drop the ...
    (microsoft.public.access.queries)
  • Re: How to open a report based on a multi-select listBox
    ... "build" as the WHERE clause from your multiselect listbox might look ... DoCmd.OpenReport "ReportName",,, strWhere ... Dim strWhere_Field1 As String, strWhere_Field2 As String ... >> method to pass the filtering string. ...
    (microsoft.public.access.formscoding)