REQUERY METHOD

#11

On Wed, 21 Dec 2011 16:33:25 -0600, "Ron Paii" <None (AT) none (DOT) com> wrote:

Quote:

"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote in message
news:g6m4f7p9cnsu7e7hjqc5taksnhnd7fc2bh (AT) 4ax (DOT) com...
On Wed, 21 Dec 2011 14:05:21 -0600, "Ron Paii" <None (AT) none (DOT) com> wrote:

"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote in message
news:8g74f7lgf2dme6glajei3f67o2ptvtmecq (AT) 4ax (DOT) com...
On Wed, 21 Dec 2011 05:44:43 -0800 (PST), FireyColin
colin.mardell (AT) btopenworld (DOT) com> wrote:

Thankyou Patrick

I've been trying that using the following code:

Dim StrValue1 As String

StrValue1 = [Forms]![frmPolicy]![txtPolicyNo]

DoCmd.OpenForm "frmPolicy", acNormal, , , "policy_no = StrValue1"

but just comes back with 'Type mismatch'

I stink at string syntax, but maybe this will work:

"[policy_no] = ' " & strValue1 &" ' "

-pw

PW's syntax should work;

Hey - it's right out of Getz's Access 97 Handbook! :-) I wish he went
into more explanation though.

I think the explanation would be in how SQL and VBA handles strings more
then Access. In this case StrValue1 is a local variable so when you attempt
use it in a form filter "policy_no = StrValue1", SQL has no access to the
variable. But with "[policy_no] = ' " & strValue1 &" ' ", you are
concatenating the string stored in strValue1 into the SQL. The extra quotes
you are adding are required by SQL so it doesn't try to interpret the
strValue1 string as a SQL statement.

Good to know! Thanks.

#12

On Wed, 21 Dec 2011 14:05:21 -0600, "Ron Paii" <None (AT) none (DOT) com> wrote:

Quote:

I find recordsetclone much more reliable.

That would be my recommendation too.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

#13

On 22/12/2011 14:06:43, "Bob Barrows" wrote:

Quote:

Patrick Finucane wrote:
My rule of thumb is
Strings get surrounded in qutes
Dates get surrounded in #
Numbers don't get surrounded

I used to have a canned response covering this somewhere. Let's see if I
can find it ...
Ah! Here it is:
To decide whether or not to delimit the data, look at the datatype of the

4.Lastly, if you are using LIKE, you need to be aware that you must use %
and _ as the wildcards, not * and ?. This is true no matter what database
you are using

Interesting, but are you sure you're correct about the "LIKE" clause?

For years (Access 2 to Access 2010) I have had a form with a field called
"Filter" - I know, probably a reserved word but it has [] round it, so no
problem. This can be set to "A*", "B*" etc or just plain "*"
The query that is the RowSource for a list box has the line "WHERE
(MemSurName Like [Screen].[ActiveForm]![Filter]) ". Works perfectly OK
Phil

#14

Whether you use % and _ or * and ? as the wildcard characters depends on
whether you're using ADO or DAO as your data access mechanism.

ADO uses % and _, DAO uses * and ?

"Phil" wrote in message news:jdc995$1an$1 (AT) speranza (DOT) aioe.org...

On 22/12/2011 14:06:43, "Bob Barrows" wrote:

Quote:

Patrick Finucane wrote:

4.Lastly, if you are using LIKE, you need to be aware that you must use %
and _ as the wildcards, not * and ?. This is true no matter what database
you are using

Interesting, but are you sure you're correct about the "LIKE" clause?

For years (Access 2 to Access 2010) I have had a form with a field called
"Filter" - I know, probably a reserved word but it has [] round it, so no
problem. This can be set to "A*", "B*" etc or just plain "*"
The query that is the RowSource for a list box has the line "WHERE
(MemSurName Like [Screen].[ActiveForm]![Filter]) ". Works perfectly OK
Phil

#15

Phil wrote:

Quote:

On 22/12/2011 14:06:43, "Bob Barrows" wrote:
Patrick Finucane wrote:
My rule of thumb is
Strings get surrounded in qutes
Dates get surrounded in #
Numbers don't get surrounded

I used to have a canned response covering this somewhere. Let's see
if I can find it ...
Ah! Here it is:
To decide whether or not to delimit the data, look at the datatype
of the

4.Lastly, if you are using LIKE, you need to be aware that you must
use % and _ as the wildcards, not * and ?. This is true no matter
what database you are using

Interesting, but are you sure you're correct about the "LIKE" clause?
For years (Access 2 to Access 2010) I have had a form with a field
called "Filter" - I know, probably a reserved word but it has []
round it, so no problem. This can be set to "A*", "B*" etc or just
plain "*"
The query that is the RowSource for a list box has the line "WHERE
(MemSurName Like [Screen].[ActiveForm]![Filter]) ". Works perfectly OK
Phil

In a query run in Access, Jet wildcards are used. I believe I mentioned that
somewhere else in that message. If not, my apologies.

As for code that connects to Jet from external sources, as Douglas says,
whether DAO or ADO is being used is the key. The canned message was intended
for an asp group where ADO is exclusively used instead of DAO (due to the
single-threaded structure of the DAO dll), so either the ODBC Jet driver or
the native Jet OLE DB provider is used, both of which require ODBC
wildcards. They translate the wildcards to Jet wildcards before passing a
query along to Jet to be run.

#16

On 27/12/2011 12:22:36, "Bob Barrows" wrote:

Quote:

Phil wrote:
On 22/12/2011 14:06:43, "Bob Barrows" wrote:
Patrick Finucane wrote:
My rule of thumb is
Strings get surrounded in qutes
Dates get surrounded in #
Numbers don't get surrounded

I used to have a canned response covering this somewhere. Let's see
if I can find it ...
Ah! Here it is:
To decide whether or not to delimit the data, look at the datatype
of the

4.Lastly, if you are using LIKE, you need to be aware that you must
use % and _ as the wildcards, not * and ?. This is true no matter
what database you are using

Interesting, but are you sure you're correct about the "LIKE" clause?
For years (Access 2 to Access 2010) I have had a form with a field
called "Filter" - I know, probably a reserved word but it has []
round it, so no problem. This can be set to "A*", "B*" etc or just
plain "*"
The query that is the RowSource for a list box has the line "WHERE
(MemSurName Like [Screen].[ActiveForm]![Filter]) ". Works perfectly OK
Phil

In a query run in Access, Jet wildcards are used. I believe I mentioned
that somewhere else in that message. If not, my apologies.

As for code that connects to Jet from external sources, as Douglas says,
whether DAO or ADO is being used is the key. The canned message was
intended for an asp group where ADO is exclusively used instead of DAO
(due to the single-threaded structure of the DAO dll), so either the ODBC
Jet driver or the native Jet OLE DB provider is used, both of which
require ODBC wildcards. They translate the wildcards to Jet wildcards
before passing a query along to Jet to be run.

Ah! that explains it. I always use DAO

Phil

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

REQUERY METHOD

comp.databases.ms-access comp.databases.ms-access

Re: REQUERY METHOD - 12-22-2011 , 01:18 PM

Re: REQUERY METHOD - 12-26-2011 , 09:04 PM

Re: REQUERY METHOD - 12-27-2011 , 05:13 AM

Re: REQUERY METHOD - 12-27-2011 , 05:53 AM

Re: REQUERY METHOD - 12-27-2011 , 06:22 AM

Re: REQUERY METHOD - 12-27-2011 , 01:23 PM