Simply query?

P

PayeDoc

Hello All

I'm obviously having a bad day query-wise, as this is the 2nd problem I've
needed help on!

What's wrong with this:
SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = [prac name]") AS Expr2
FROM practices
WHERE (((practices.[20056 EOY])="6"));

It works fine without the 2nd criteria in the DMax expression (i.e. without
[x confirmed]![practice] = [prac name]). With this criteria I get a message
that access can't find the name 'prac name' ... but why on earth not?? It's
there in the query, and in the table. I tried amending the 2nd criteria to
[x confirmed]![practice] = [practices]![prac name], but with the same
result.

I'm sure this should be simple, but just can't see what's wrong. I'll
probably end up kicking myself when someone points it out - but I will also
be very grateful!

Thanks for any help
Leslie Isaacs
 
J

Jeff Boyce

By keeping the [prac name] within the quote, you may be telling Access to
look for the literal string "[prac name]"...

I assume you want Access to look for the contents of the field named [prac
name] ...

(here's a trick I use when I run into troublesome queries ... build the
query in design view, get it working, then use the SQL view to see how
Access translates it into SQL.)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
D

Daryl S

Leslie -

I assume you want the [prac name] to be the value from the practices table,
and not a field name in the [x confirmed] table. Try this:

SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = '" & [prac name] & "'") AS Expr2
FROM practices
WHERE (((practices.[20056 EOY])="6"));
 
J

John Spencer

DMax("entered","[x confirmed]","[month name] =""march 2010"" and practice =
""" & [prac name] & """") AS Expr2

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
L

Leslie Isaacs

Hello Jeff

Thanks for your reply.

You are correct - I do want to look for the contents of the field named
[prac name]: I thought I was doing that!

Les


Jeff Boyce said:
By keeping the [prac name] within the quote, you may be telling Access to
look for the literal string "[prac name]"...

I assume you want Access to look for the contents of the field named [prac
name] ...

(here's a trick I use when I run into troublesome queries ... build the
query in design view, get it working, then use the SQL view to see how
Access translates it into SQL.)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

PayeDoc said:
Hello All

I'm obviously having a bad day query-wise, as this is the 2nd problem
I've
needed help on!

What's wrong with this:
SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name]
=""march
2010"" and [x confirmed]![practice] = [prac name]") AS Expr2
FROM practices
WHERE (((practices.[20056 EOY])="6"));

It works fine without the 2nd criteria in the DMax expression (i.e.
without
[x confirmed]![practice] = [prac name]). With this criteria I get a
message
that access can't find the name 'prac name' ... but why on earth not??
It's
there in the query, and in the table. I tried amending the 2nd criteria
to
[x confirmed]![practice] = [practices]![prac name], but with the same
result.

I'm sure this should be simple, but just can't see what's wrong. I'll
probably end up kicking myself when someone points it out - but I will
also
be very grateful!

Thanks for any help
Leslie Isaacs
Click to expand...
Click to expand...
 
L

Leslie Isaacs

Hello Daryl

Thanks for your reply.

So it's a matter of quotes! I will try your suggestion when I'm back in the
ofice, tomorrow, and will post back with the results.

Thanks again
Les




Daryl S said:
Leslie -

I assume you want the [prac name] to be the value from the practices
table,
and not a field name in the [x confirmed] table. Try this:

SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = '" & [prac name] & "'") AS Expr2
FROM practices
WHERE (((practices.[20056 EOY])="6"));

--
Daryl S


PayeDoc said:
Hello All

I'm obviously having a bad day query-wise, as this is the 2nd problem
I've
needed help on!

What's wrong with this:
SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name]
=""march
2010"" and [x confirmed]![practice] = [prac name]") AS Expr2
FROM practices
WHERE (((practices.[20056 EOY])="6"));

It works fine without the 2nd criteria in the DMax expression (i.e.
without
[x confirmed]![practice] = [prac name]). With this criteria I get a
message
that access can't find the name 'prac name' ... but why on earth not??
It's
there in the query, and in the table. I tried amending the 2nd criteria
to
[x confirmed]![practice] = [practices]![prac name], but with the same
result.

I'm sure this should be simple, but just can't see what's wrong. I'll
probably end up kicking myself when someone points it out - but I will
also
be very grateful!

Thanks for any help
Leslie Isaacs


.
Click to expand...
Click to expand...
 
L

Leslie Isaacs

Hello John

Thanks for your reply.

So it's a matter of quotes - for which yourself and Daryl have offered
slightly different solutions! I will try both suggestions when I'm back in
the ofice, tomorrow, and will post back with the results.

Thanks again
Les


John Spencer said:
DMax("entered","[x confirmed]","[month name] =""march 2010"" and practice
= """ & [prac name] & """") AS Expr2

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello All

I'm obviously having a bad day query-wise, as this is the 2nd problem
I've
needed help on!

What's wrong with this:
SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name]
=""march
2010"" and [x confirmed]![practice] = [prac name]") AS Expr2
FROM practices
WHERE (((practices.[20056 EOY])="6"));

It works fine without the 2nd criteria in the DMax expression (i.e.
without
[x confirmed]![practice] = [prac name]). With this criteria I get a
message
that access can't find the name 'prac name' ... but why on earth not??
It's
there in the query, and in the table. I tried amending the 2nd criteria
to
[x confirmed]![practice] = [practices]![prac name], but with the same
result.

I'm sure this should be simple, but just can't see what's wrong. I'll
probably end up kicking myself when someone points it out - but I will
also
be very grateful!

Thanks for any help
Leslie Isaacs
Click to expand...
Click to expand...
 
P

PayeDoc

Daryl/John

It worked!
Sort of!

Your suggested queries both work fine, then I added an extra criteria to the
field calculated by the the DMax expression, and the query still works fine
.... but then when I try to use this query in an ApplyFilter command I get a
message that the specified field 'prac name' could refer to more than one
table listed in the FROM clause of the SQL statement.

The query I now have is:
SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = '" & [prac name] & "'") AS confirmed
FROM practices
WHERE (((practices.[20056 EOY])="6") AND ((DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = '" & [prac name] &
"'"))<#3/12/2010#));


So the query works, but the ApplyFilter doesn't - although the ApplyFilter
did work when the query didn't have the 2nd criteria for the field
calculated by the the DMax expression - i.e. when the query didn'y have
this: AND ((DMax("[x confirmed]![entered]","[x confirmed]","[x
confirmed]![month name] =""march 2010"" and [x confirmed]![practice] = '" &
[prac name] & "'"))<#3/12/2010#

Sorry to be a pain, but if either of you could point me in the right
direction I'd be really grateful.

Many thanks again
Les
 
J

John Spencer

Try referring to practices.[prac name] in full with both table and field name.

I don't understand by there would be any confusion at all that you are
referring to the same field.

SELECT practices.[prac name]
, practices.[20056 EOY]
, DMax("entered","[x confirmed]","[month name] ='march 2010'
and practice = '" & practices.[prac name] & "'") AS confirmed
FROM practices
WHERE practices.[20056 EOY]="6"
AND DMax("entered","[x confirmed]","[month name] ='march 2010'
and practice = '" & practices.[prac name] & "'")<#3/12/2010#));


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Daryl/John

It worked!
Sort of!

Your suggested queries both work fine, then I added an extra criteria to the
field calculated by the the DMax expression, and the query still works fine
... but then when I try to use this query in an ApplyFilter command I get a
message that the specified field 'prac name' could refer to more than one
table listed in the FROM clause of the SQL statement.

The query I now have is:
SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = '" & [prac name] & "'") AS confirmed
FROM practices
WHERE (((practices.[20056 EOY])="6") AND ((DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = '" & [prac name] &
"'"))<#3/12/2010#));


So the query works, but the ApplyFilter doesn't - although the ApplyFilter
did work when the query didn't have the 2nd criteria for the field
calculated by the the DMax expression - i.e. when the query didn'y have
this: AND ((DMax("[x confirmed]![entered]","[x confirmed]","[x
confirmed]![month name] =""march 2010"" and [x confirmed]![practice] = '" &
[prac name] & "'"))<#3/12/2010#

Sorry to be a pain, but if either of you could point me in the right
direction I'd be really grateful.

Many thanks again
Les


PayeDoc said:
Hello All

I'm obviously having a bad day query-wise, as this is the 2nd problem I've
needed help on!

What's wrong with this:
SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = [prac name]") AS Expr2
FROM practices
WHERE (((practices.[20056 EOY])="6"));

It works fine without the 2nd criteria in the DMax expression (i.e. without
[x confirmed]![practice] = [prac name]). With this criteria I get a message
that access can't find the name 'prac name' ... but why on earth not?? It's
there in the query, and in the table. I tried amending the 2nd criteria to
[x confirmed]![practice] = [practices]![prac name], but with the same
result.

I'm sure this should be simple, but just can't see what's wrong. I'll
probably end up kicking myself when someone points it out - but I will also
be very grateful!

Thanks for any help
Leslie Isaacs
Click to expand...
Click to expand...
 
P

PayeDoc

Hi John

That's it!
Many thanks for your help.

Les

John Spencer said:
Try referring to practices.[prac name] in full with both table and field name.

I don't understand by there would be any confusion at all that you are
referring to the same field.

SELECT practices.[prac name]
, practices.[20056 EOY]
, DMax("entered","[x confirmed]","[month name] ='march 2010'
and practice = '" & practices.[prac name] & "'") AS confirmed
FROM practices
WHERE practices.[20056 EOY]="6"
AND DMax("entered","[x confirmed]","[month name] ='march 2010'
and practice = '" & practices.[prac name] & "'")<#3/12/2010#));


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Daryl/John

It worked!
Sort of!

Your suggested queries both work fine, then I added an extra criteria to the
field calculated by the the DMax expression, and the query still works fine
... but then when I try to use this query in an ApplyFilter command I get a
message that the specified field 'prac name' could refer to more than one
table listed in the FROM clause of the SQL statement.

The query I now have is:
SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = '" & [prac name] & "'") AS confirmed
FROM practices
WHERE (((practices.[20056 EOY])="6") AND ((DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = '" & [prac name] &
"'"))<#3/12/2010#));


So the query works, but the ApplyFilter doesn't - although the ApplyFilter
did work when the query didn't have the 2nd criteria for the field
calculated by the the DMax expression - i.e. when the query didn'y have
this: AND ((DMax("[x confirmed]![entered]","[x confirmed]","[x
confirmed]![month name] =""march 2010"" and [x confirmed]![practice] = '" &
[prac name] & "'"))<#3/12/2010#

Sorry to be a pain, but if either of you could point me in the right
direction I'd be really grateful.

Many thanks again
Les


PayeDoc said:
Hello All

I'm obviously having a bad day query-wise, as this is the 2nd problem I've
needed help on!

What's wrong with this:
SELECT practices.[prac name], practices.[20056 EOY], DMax("[x
confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march
2010"" and [x confirmed]![practice] = [prac name]") AS Expr2
FROM practices
WHERE (((practices.[20056 EOY])="6"));

It works fine without the 2nd criteria in the DMax expression (i.e. without
[x confirmed]![practice] = [prac name]). With this criteria I get a message
that access can't find the name 'prac name' ... but why on earth not?? It's
there in the query, and in the table. I tried amending the 2nd criteria to
[x confirmed]![practice] = [practices]![prac name], but with the same
result.

I'm sure this should be simple, but just can't see what's wrong. I'll
probably end up kicking myself when someone points it out - but I will also
be very grateful!

Thanks for any help
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

What's wrong with this query?! 6
"Too few parameters" error - sometimes 1
ApplyFilter problem 2
Slow query 1
Slow query 13
Slow query 9
Query to find missing data 4
Slow query 1

Top