OpenForm with Where Clause

[Mike Charney]

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?

Please help...

Thanks in advance...

Mike
m charney at dunlap hospital dot org

[Allen Browne]

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.

--
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" <no*****@everything.net> wrote in message
news:fX*****************@newssvr25.news.prodigy.ne t...

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?

[Mike Charney]

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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

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.

--
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" <no*****@everything.net> wrote in message
news:fX*****************@newssvr25.news.prodigy.ne t...

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?

[Allen Browne]

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.

--
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" <no*****@everything.net> wrote in message
news:ol*****************@newssvr13.news.prodigy.co m...

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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

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" <no*****@everything.net> wrote in message
news:fX*****************@newssvr25.news.prodigy.ne t...

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?

[Mike Charney]

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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

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.

--
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" <no*****@everything.net> wrote in message
news:ol*****************@newssvr13.news.prodigy.co m...

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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

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" <no*****@everything.net> wrote in message
news:fX*****************@newssvr25.news.prodigy.ne t...
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?

[Allen Browne]

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" <no*****@everything.net> wrote in message
news:FZ*******************@newssvr29.news.prodigy. net...

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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

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" <no*****@everything.net> wrote in message
news:ol*****************@newssvr13.news.prodigy.co m...

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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...
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" <no*****@everything.net> wrote in message
news:fX*****************@newssvr25.news.prodigy.ne t...
>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?

[Mike Charney]

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

Mike

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

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" <no*****@everything.net> wrote in message
news:FZ*******************@newssvr29.news.prodigy. net...

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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

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" <no*****@everything.net> wrote in message
news:ol*****************@newssvr13.news.prodigy.co m...
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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...
> 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" <no*****@everything.net> wrote in message
> news:fX*****************@newssvr25.news.prodigy.ne t...
>>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?

[Allen Browne]

If the field contains characters, it is not a Number type field.

If you are asking how to pattern match digits in specific places in a Text
field, use the # character.

--
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" <no*****@everything.net> wrote in message
news:Gr****************@newssvr11.news.prodigy.com ...

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

Mike

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

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.

"Mike Charney" <no*****@everything.net> wrote in message
news:FZ*******************@newssvr29.news.prodigy. net...

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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...
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" <no*****@everything.net> wrote in message
news:ol*****************@newssvr13.news.prodigy.co m...
> 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" <Al*********@SeeSig.Invalid> wrote in message
> news:44**********************@per-qv1-newsreader-01.iinet.net.au...
>> 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" <no*****@everything.net> wrote in message
>> news:fX*****************@newssvr25.news.prodigy.ne t...
>>>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?

[Mike Charney]

Thanks for everyones help.

I have it working.

Mike
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

If the field contains characters, it is not a Number type field.

If you are asking how to pattern match digits in specific places in a Text
field, use the # character.

--
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" <no*****@everything.net> wrote in message
news:Gr****************@newssvr11.news.prodigy.com ...

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

Mike

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

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.

"Mike Charney" <no*****@everything.net> wrote in message
news:FZ*******************@newssvr29.news.prodigy. net...
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" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...
> 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" <no*****@everything.net> wrote in message
> news:ol*****************@newssvr13.news.prodigy.co m...
>> 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" <Al*********@SeeSig.Invalid> wrote in message
>> news:44**********************@per-qv1-newsreader-01.iinet.net.au...
>>> 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" <no*****@everything.net> wrote in message
>>> news:fX*****************@newssvr25.news.prodigy.ne t...
>>>>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?