Going nuts...

[Fred Bloggs]

I've posted this in the 'queries' newsgroup, but didn't know whether it
should actually be here, so apologies in advance for cross-posting. I'm
pulling what's left of my hair out...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 command 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 Vinson]

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

You could search the postcode field without creating the Left()
expression at all:

LIKE [Forms]![Therapistlocator]![ByPCode] & "*"

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!

Please post the actual SQL of the query, messy as it is. I *suspect*
that it's parenthesis nesting - you're getting AND where you want OR
for example.

 

[Fred Bloggs]

I tried that Postcode option you suggested and it doesn't work for some
reason. If I key in CB, for example, I get all records. I suspect it is
because the Postcode is a string with a gap...e.g. TG1 4F7, so the
expression needs to understand this.

How do I post the SQL to the group? If I use the 'Send to mail recipient as
an attachment' what option should I use out of rtf, excel, html, etc?

I am happy to e-mail you directly with the database if it would help.

Many thanks
James

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

You could search the postcode field without creating the Left()
expression at all:

LIKE [Forms]![Therapistlocator]![ByPCode] & "*"

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!

Please post the actual SQL of the query, messy as it is. I *suspect*
that it's parenthesis nesting - you're getting AND where you want OR
for example.

 

[Douglas J. Steele]

What John's looking for is the text of the SQL statement you're running.
There's no need for an attachment.

If it's a QueryDef object in your database, open the query in Design view
and select "Design View" from the View menu. That should display the SQL
associated with the query. You can select it all, copy it to the clipboard,
then paste it into your post.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I tried that Postcode option you suggested and it doesn't work for some
reason. If I key in CB, for example, I get all records. I suspect it is
because the Postcode is a string with a gap...e.g. TG1 4F7, so the
expression needs to understand this.

How do I post the SQL to the group? If I use the 'Send to mail recipient as
an attachment' what option should I use out of rtf, excel, html, etc?

I am happy to e-mail you directly with the database if it would help.

Many thanks
James


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

You could search the postcode field without creating the Left()
expression at all:

LIKE [Forms]![Therapistlocator]![ByPCode] & "*"

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!

Please post the actual SQL of the query, messy as it is. I *suspect*
that it's parenthesis nesting - you're getting AND where you want OR
for example.

 

[John Vinson]

I tried that Postcode option you suggested and it doesn't work for some
reason. If I key in CB, for example, I get all records. I suspect it is
because the Postcode is a string with a gap...e.g. TG1 4F7, so the
expression needs to understand this.

A criterion on Postcode of

LIKE "TG*"

should return only those postcodes starting with the letters TG.
Similarly, a criterion of

LIKE [Forms]![MyForm]![txtPostcode] & "*"

will return only those postcodes starting with the value entered into
that control by the user - if they enter nothing you'll see *all*
postcodes. The gap is simply another ASCII character, a blank; it's
treated exactly like any other character.

How do I post the SQL to the group? If I use the 'Send to mail recipient as
an attachment' what option should I use out of rtf, excel, html, etc?

No attachment is necessary. Open the Query in design view; on the
leftmost tool in the query design toolbar select the dropdown arrow
and choose SQL, or (equivalently) use the View menu option to view the
SQL. Copy and paste the SQL text to a message - it's just text.

I am happy to e-mail you directly with the database if it would help.

Sorry... private EMail support is for paying customers only.

 

[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

I tried that Postcode option you suggested and it doesn't work for some
reason. If I key in CB, for example, I get all records. I suspect it is
because the Postcode is a string with a gap...e.g. TG1 4F7, so the
expression needs to understand this.

A criterion on Postcode of

LIKE "TG*"

should return only those postcodes starting with the letters TG.
Similarly, a criterion of

LIKE [Forms]![MyForm]![txtPostcode] & "*"

will return only those postcodes starting with the value entered into
that control by the user - if they enter nothing you'll see *all*
postcodes. The gap is simply another ASCII character, a blank; it's
treated exactly like any other character.

How do I post the SQL to the group? If I use the 'Send to mail recipient as
an attachment' what option should I use out of rtf, excel, html, etc?

No attachment is necessary. Open the Query in design view; on the
leftmost tool in the query design toolbar select the dropdown arrow
and choose SQL, or (equivalently) use the View menu option to view the
SQL. Copy and paste the SQL text to a message - it's just text.

I am happy to e-mail you directly with the database if it would help.

Sorry... private EMail support is for paying customers only.