Going nuts...

[Fred Bloggs]

Can any of you experts help a relative newbie with a problem that's driving me nuts?

I've created a query by form using the Microsoft article Q304428, just as it says, in order to locate therapists in the UK. I want to be able to search on Surname or County or Town or Post Code and bring back the relevant records that match.

I've created a form with four textboxes on it: BySname, ByCounty, ByTown, ByPCode and a command button.
I've called the form Therapistlocator
I've created a macro called QBFQuery1 which is run from the button on the form.

I've created a query called 'test' (okay, but after several hundred corrupt queries, it's all I could think of).

In the query, I've got four table fields: Surname, County, Town and PostCode.

I've put the following criteria in each one:
In Surname:
[Forms]![Therapistlocator]![BySname] Or [Forms]![Therapistlocator]![BySname] Is Null
In County:
[Forms]![Therapistlocator]![ByCounty] Or [Forms]![Therapistlocator]![ByCounty] Is Null
In Town:
[Forms]![Therapistlocator]![ByTown] Or [Forms]![Therapistlocator]![ByTown] Is Null

In the PostCode field in the table, the postcodes are xxx xxx, so because I want search only on the first two letters, I created another field like so:
Expr1: Left([PostCode],2)

In the criteria for this, I've put:
[Forms]![Therapistlocator]![ByPCode] Or [Forms]![Therapistlocator]![ByPCode] Is Null

Nothing complicated, eh? But, when I run the form, I get zero records. When I then go back to the query, Access has added about a dozen lines of extra bits of the expressions all over the place, has truncated most of the expressions, and has created about three new columns, all headed up with bits of code!

If I only put criteria in one field, it seems to work but the minute I add criteria to another field, it does it all over again. Is this a bug? Did MS get it wrong in their article? I've tried it on another machine and still get the same problem. Help!!!!

I'm using Microsoft Access 2002 SP-2

I know there's someone out there who can help...I'd be very grateful.
James.

 

[Ken Snell]

First, to clear up why ACCESS changed the query's look on you. ACCESS takes
all the criteria that you entered and determines all possible combinations
and then rewrites the query design with those combinations. No need to worry
about this change; it's ok (so long as what you started with works).

Second, the criteria that you've posted appear fine. So, let's look for
other reasons why you're getting zero records.

(1) I assume that the form Therapistlocator is open when the query runs. It
has to be for the query to read the values.

(2) What is the code on the command button's Click event?


--
Ken Snell
<MS ACCESS MVP>

Can any of you experts help a relative newbie with a problem that's driving
me nuts?

I've created a query by form using the Microsoft article Q304428, just as it
says, in order to locate therapists in the UK. I want to be able to search
on Surname or County or Town or Post Code and bring back the relevant
records that match.

I've created a form with four textboxes on it: BySname, ByCounty, ByTown,
ByPCode and a command button.
I've called the form Therapistlocator
I've created a macro called QBFQuery1 which is run from the button on the
form.

I've created a query called 'test' (okay, but after several hundred corrupt
queries, it's all I could think of).

In the query, I've got four table fields: Surname, County, Town and
PostCode.

I've put the following criteria in each one:
In Surname:
[Forms]![Therapistlocator]![BySname] Or [Forms]![Therapistlocator]![BySname]
Is Null
In County:
[Forms]![Therapistlocator]![ByCounty] Or
[Forms]![Therapistlocator]![ByCounty] Is Null
In Town:
[Forms]![Therapistlocator]![ByTown] Or [Forms]![Therapistlocator]![ByTown]
Is Null

In the PostCode field in the table, the postcodes are xxx xxx, so because I
want search only on the first two letters, I created another field like so:
Expr1: Left([PostCode],2)

In the criteria for this, I've put:
[Forms]![Therapistlocator]![ByPCode] Or [Forms]![Therapistlocator]![ByPCode]
Is Null

Nothing complicated, eh? But, when I run the form, I get zero records. When
I then go back to the query, Access has added about a dozen lines of extra
bits of the expressions all over the place, has truncated most of the
expressions, and has created about three new columns, all headed up with
bits of code!

If I only put criteria in one field, it seems to work but the minute I add
criteria to another field, it does it all over again. Is this a bug? Did MS
get it wrong in their article? I've tried it on another machine and still
get the same problem. Help!!!!

I'm using Microsoft Access 2002 SP-2

I know there's someone out there who can help...I'd be very grateful.
James.

 

[Fred Bloggs]

Hi there
Yes, Therapistlocator is open when the query runs, because it is the command
button on this form that fires the macro that runs the query.

The Command button's click event merely says QBFQuery1, the name of the
macro.

As to the rewriting of the query design, it doesn't just add some
paramenters, it strips away the latter half of each criteria, adds numerous
rows containing duplicate half-criteria, adds about six columns, all headed
up with the half-criteria, which is what made me think that this was
corruption rather than modification.

First, to clear up why ACCESS changed the query's look on you. ACCESS takes
all the criteria that you entered and determines all possible combinations
and then rewrites the query design with those combinations. No need to worry
about this change; it's ok (so long as what you started with works).

Second, the criteria that you've posted appear fine. So, let's look for
other reasons why you're getting zero records.

(1) I assume that the form Therapistlocator is open when the query runs. It
has to be for the query to read the values.

(2) What is the code on the command button's Click event?


--
Ken Snell
<MS ACCESS MVP>

Can any of you experts help a relative newbie with a problem that's driving
me nuts?

I've created a query by form using the Microsoft article Q304428, just as it
says, in order to locate therapists in the UK. I want to be able to search
on Surname or County or Town or Post Code and bring back the relevant
records that match.

I've created a form with four textboxes on it: BySname, ByCounty, ByTown,
ByPCode and a command button.
I've called the form Therapistlocator
I've created a macro called QBFQuery1 which is run from the button on the
form.

I've created a query called 'test' (okay, but after several hundred corrupt
queries, it's all I could think of).

In the query, I've got four table fields: Surname, County, Town and
PostCode.

I've put the following criteria in each one:
In Surname:
[Forms]![Therapistlocator]![BySname] Or [Forms]![Therapistlocator]![BySname]
Is Null
In County:
[Forms]![Therapistlocator]![ByCounty] Or
[Forms]![Therapistlocator]![ByCounty] Is Null
In Town:
[Forms]![Therapistlocator]![ByTown] Or [Forms]![Therapistlocator]![ByTown]
Is Null

In the PostCode field in the table, the postcodes are xxx xxx, so because I
want search only on the first two letters, I created another field like so:
Expr1: Left([PostCode],2)

In the criteria for this, I've put:
[Forms]![Therapistlocator]![ByPCode] Or [Forms]![Therapistlocator]![ByPCode]
Is Null

Nothing complicated, eh? But, when I run the form, I get zero records. When
I then go back to the query, Access has added about a dozen lines of extra
bits of the expressions all over the place, has truncated most of the
expressions, and has created about three new columns, all headed up with
bits of code!

If I only put criteria in one field, it seems to work but the minute I add
criteria to another field, it does it all over again. Is this a bug? Did MS
get it wrong in their article? I've tried it on another machine and still
get the same problem. Help!!!!

I'm using Microsoft Access 2002 SP-2

I know there's someone out there who can help...I'd be very grateful.
James.

 

[Ken Snell]

And what does the macro do? What are its actions?

I assume that you've checked the spelling of the form and controls in the
query against the actual names?

Try running the query all by itself (with the form closed). The query will
ask you to enter the parameters (because it won't find the form) -- does the
query return the expected records?

--
Ken Snell
<MS ACCESS MVP>

Hi there
Yes, Therapistlocator is open when the query runs, because it is the command
button on this form that fires the macro that runs the query.

The Command button's click event merely says QBFQuery1, the name of the
macro.

As to the rewriting of the query design, it doesn't just add some
paramenters, it strips away the latter half of each criteria, adds numerous
rows containing duplicate half-criteria, adds about six columns, all headed
up with the half-criteria, which is what made me think that this was
corruption rather than modification.

First, to clear up why ACCESS changed the query's look on you. ACCESS takes
all the criteria that you entered and determines all possible combinations
and then rewrites the query design with those combinations. No need to worry
about this change; it's ok (so long as what you started with works).

Second, the criteria that you've posted appear fine. So, let's look for
other reasons why you're getting zero records.

(1) I assume that the form Therapistlocator is open when the query runs. It
has to be for the query to read the values.

(2) What is the code on the command button's Click event?


--
Ken Snell
<MS ACCESS MVP>

Can any of you experts help a relative newbie with a problem that's driving
me nuts?

I've created a query by form using the Microsoft article Q304428, just

as

it
says, in order to locate therapists in the UK. I want to be able to search
on Surname or County or Town or Post Code and bring back the relevant
records that match.

I've created a form with four textboxes on it: BySname, ByCounty, ByTown,
ByPCode and a command button.
I've called the form Therapistlocator
I've created a macro called QBFQuery1 which is run from the button on the
form.

I've created a query called 'test' (okay, but after several hundred corrupt
queries, it's all I could think of).

In the query, I've got four table fields: Surname, County, Town and
PostCode.

I've put the following criteria in each one:
In Surname:
[Forms]![Therapistlocator]![BySname] Or [Forms]![Therapistlocator]![BySname]
Is Null
In County:
[Forms]![Therapistlocator]![ByCounty] Or
[Forms]![Therapistlocator]![ByCounty] Is Null
In Town:
[Forms]![Therapistlocator]![ByTown] Or [Forms]![Therapistlocator]![ByTown]
Is Null

In the PostCode field in the table, the postcodes are xxx xxx, so

because

I
want search only on the first two letters, I created another field like so:
Expr1: Left([PostCode],2)

In the criteria for this, I've put:
[Forms]![Therapistlocator]![ByPCode] Or [Forms]![Therapistlocator]![ByPCode]
Is Null

Nothing complicated, eh? But, when I run the form, I get zero records. When
I then go back to the query, Access has added about a dozen lines of extra
bits of the expressions all over the place, has truncated most of the
expressions, and has created about three new columns, all headed up with
bits of code!

If I only put criteria in one field, it seems to work but the minute I add
criteria to another field, it does it all over again. Is this a bug? Did MS
get it wrong in their article? I've tried it on another machine and still
get the same problem. Help!!!!

I'm using Microsoft Access 2002 SP-2

I know there's someone out there who can help...I'd be very grateful.
James.

 

[John Spencer (MVP)]

Fred,

First, this scheme works BUT if you are using more than a couple fields, the
query can get TOO complex and fail. Access restructures the query to make the
query "more efficient", but in my experience this causes problems when you use
more than two or three of the compound criteria.

IF each of your fields (surname, County, Town, and PostCode, ALWAYS contains
data then you can simplify your query by using criteria as follows.

In Surname:
LIKE Nz([Forms]![Therapistlocator]![BySname],"*")
In County:
LIKE Nz([Forms]![Therapistlocator]![ByCounty],"*")
In Town:
LIKE([Forms]![Therapistlocator]![ByTown],"*")
In PostCode:
LIKE([Forms]![Therapistlocator]![ByPCode] & "*")


You can still use this scheme if some fields are null, but it will be slower.
To do this, you need to concatenate an empty string onto your field values.
Something like:

Field: xSurname: SurName & ""
Table: <Blank>
Criteria: LIKE Nz([Forms]![Therapistlocator]![BySname],"*")

Hope this helps.

 

[Fred Bloggs]

Sorry about the delay in replying. My dial-up was down for a day, so I
couldn't respond. In the meantime I contacted Microsoft. It would appear
that I was actually chasing a wild goose. My concentration was on the way
the criteria was being added to, split, etc., in the query itself, but I
understand from MS that this is just the way the Jet engine optimises
everything -- even though some of its changes in the query don't make any
sense to me whatsoever! From my viewpoint, it was just corrupting. Anyway,
after putting in all the criteria expressions and testing it, it actually
works fine.

Sorry for wasting your valuable time. I guess I've learned a big lesson
here...like, if it ain't broke, don't fix it. It just 'appeared' broke -- I
think because I was testing one field at a time, returning to the criteria
and seeing it messed up, rewriting it, doing it again, etc., etc.

Just shows how a newbie really relies on experts to see them through the
forest, when their focus is on the trees.

Cheers
James

And what does the macro do? What are its actions?

I assume that you've checked the spelling of the form and controls in the
query against the actual names?

Try running the query all by itself (with the form closed). The query will
ask you to enter the parameters (because it won't find the form) -- does the
query return the expected records?

--
Ken Snell
<MS ACCESS MVP>

Hi there
Yes, Therapistlocator is open when the query runs, because it is the command
button on this form that fires the macro that runs the query.

The Command button's click event merely says QBFQuery1, the name of the
macro.

As to the rewriting of the query design, it doesn't just add some
paramenters, it strips away the latter half of each criteria, adds numerous
rows containing duplicate half-criteria, adds about six columns, all headed
up with the half-criteria, which is what made me think that this was
corruption rather than modification.

First, to clear up why ACCESS changed the query's look on you. ACCESS takes
all the criteria that you entered and determines all possible combinations
and then rewrites the query design with those combinations. No need to worry
about this change; it's ok (so long as what you started with works).

Second, the criteria that you've posted appear fine. So, let's look for
other reasons why you're getting zero records.

(1) I assume that the form Therapistlocator is open when the query

runs.
It

has to be for the query to read the values.

(2) What is the code on the command button's Click event?


--
Ken Snell
<MS ACCESS MVP>

Can any of you experts help a relative newbie with a problem that's driving
me nuts?

I've created a query by form using the Microsoft article Q304428, just

as

it
says, in order to locate therapists in the UK. I want to be able to search
on Surname or County or Town or Post Code and bring back the relevant
records that match.

I've created a form with four textboxes on it: BySname, ByCounty, ByTown,
ByPCode and a command button.
I've called the form Therapistlocator
I've created a macro called QBFQuery1 which is run from the button on the
form.

I've created a query called 'test' (okay, but after several hundred corrupt
queries, it's all I could think of).

In the query, I've got four table fields: Surname, County, Town and
PostCode.

I've put the following criteria in each one:
In Surname:
[Forms]![Therapistlocator]![BySname] Or [Forms]![Therapistlocator]![BySname]
Is Null
In County:
[Forms]![Therapistlocator]![ByCounty] Or
[Forms]![Therapistlocator]![ByCounty] Is Null
In Town:
[Forms]![Therapistlocator]![ByTown] Or [Forms]![Therapistlocator]![ByTown]
Is Null

In the PostCode field in the table, the postcodes are xxx xxx, so

because

I
want search only on the first two letters, I created another field

like
so:

Expr1: Left([PostCode],2)

In the criteria for this, I've put:
[Forms]![Therapistlocator]![ByPCode] Or [Forms]![Therapistlocator]![ByPCode]
Is Null

Nothing complicated, eh? But, when I run the form, I get zero records. When
I then go back to the query, Access has added about a dozen lines of extra
bits of the expressions all over the place, has truncated most of the
expressions, and has created about three new columns, all headed up with
bits of code!

If I only put criteria in one field, it seems to work but the minute I add
criteria to another field, it does it all over again. Is this a bug?

Did
MS

get it wrong in their article? I've tried it on another machine and still
get the same problem. Help!!!!

I'm using Microsoft Access 2002 SP-2

I know there's someone out there who can help...I'd be very grateful.
James.

 

[Ken Snell]

That concept of using the combination of a reference to the value of a
control or if the control's value is Null is great until you've saved the
query and then need to go back and edit it! As you have found (and as I have
found in my own situations).

Good luck!

--
Ken Snell
<MS ACCESS MVP>

Sorry about the delay in replying. My dial-up was down for a day, so I
couldn't respond. In the meantime I contacted Microsoft. It would appear
that I was actually chasing a wild goose. My concentration was on the way
the criteria was being added to, split, etc., in the query itself, but I
understand from MS that this is just the way the Jet engine optimises
everything -- even though some of its changes in the query don't make any
sense to me whatsoever! From my viewpoint, it was just corrupting. Anyway,
after putting in all the criteria expressions and testing it, it actually
works fine.

Sorry for wasting your valuable time. I guess I've learned a big lesson
here...like, if it ain't broke, don't fix it. It just 'appeared' broke -- I
think because I was testing one field at a time, returning to the criteria
and seeing it messed up, rewriting it, doing it again, etc., etc.

Just shows how a newbie really relies on experts to see them through the
forest, when their focus is on the trees.

Cheers
James

And what does the macro do? What are its actions?

I assume that you've checked the spelling of the form and controls in the
query against the actual names?

Try running the query all by itself (with the form closed). The query will
ask you to enter the parameters (because it won't find the form) -- does the
query return the expected records?

--
Ken Snell
<MS ACCESS MVP>

Hi there
Yes, Therapistlocator is open when the query runs, because it is the command
button on this form that fires the macro that runs the query.

The Command button's click event merely says QBFQuery1, the name of the
macro.

As to the rewriting of the query design, it doesn't just add some
paramenters, it strips away the latter half of each criteria, adds numerous
rows containing duplicate half-criteria, adds about six columns, all headed
up with the half-criteria, which is what made me think that this was
corruption rather than modification.

First, to clear up why ACCESS changed the query's look on you. ACCESS
takes
all the criteria that you entered and determines all possible combinations
and then rewrites the query design with those combinations. No need to
worry
about this change; it's ok (so long as what you started with works).

Second, the criteria that you've posted appear fine. So, let's look for
other reasons why you're getting zero records.

(1) I assume that the form Therapistlocator is open when the query runs.
It
has to be for the query to read the values.

(2) What is the code on the command button's Click event?


--
Ken Snell
<MS ACCESS MVP>

Can any of you experts help a relative newbie with a problem that's
driving
me nuts?

I've created a query by form using the Microsoft article Q304428,

just
as

it
says, in order to locate therapists in the UK. I want to be able to search
on Surname or County or Town or Post Code and bring back the relevant
records that match.

I've created a form with four textboxes on it: BySname, ByCounty, ByTown,
ByPCode and a command button.
I've called the form Therapistlocator
I've created a macro called QBFQuery1 which is run from the button

on
the

form.

I've created a query called 'test' (okay, but after several hundred
corrupt
queries, it's all I could think of).

In the query, I've got four table fields: Surname, County, Town and
PostCode.

I've put the following criteria in each one:
In Surname:
[Forms]![Therapistlocator]![BySname] Or
[Forms]![Therapistlocator]![BySname]
Is Null
In County:
[Forms]![Therapistlocator]![ByCounty] Or
[Forms]![Therapistlocator]![ByCounty] Is Null
In Town:
[Forms]![Therapistlocator]![ByTown] Or [Forms]![Therapistlocator]![ByTown]
Is Null

In the PostCode field in the table, the postcodes are xxx xxx, so because
I
want search only on the first two letters, I created another field like
so:
Expr1: Left([PostCode],2)

In the criteria for this, I've put:
[Forms]![Therapistlocator]![ByPCode] Or
[Forms]![Therapistlocator]![ByPCode]
Is Null

Nothing complicated, eh? But, when I run the form, I get zero records.
When
I then go back to the query, Access has added about a dozen lines of extra
bits of the expressions all over the place, has truncated most of the
expressions, and has created about three new columns, all headed up with
bits of code!

If I only put criteria in one field, it seems to work but the minute

I
add

criteria to another field, it does it all over again. Is this a bug? Did
MS
get it wrong in their article? I've tried it on another machine and still
get the same problem. Help!!!!

I'm using Microsoft Access 2002 SP-2

I know there's someone out there who can help...I'd be very grateful.
James.