Have you checked your references?
--
Duane Hookom
MS Access MVP
LIKE was there, I inadvertently omitted it when I typed it. Here is the
current SQL View:
______________________________________________________________
SELECT [NAME], STREET, PO_BOX, CITY_ZIP, ZIP_CODE, TYPE
FROM Maint_Stations
WHERE TYPE Like "R*" AND InStr("," & [Enter Region Numbers] & "," , "," &
[DIST] & ",")>0
ORDER BY DIST;
_______________________________________________________________
In the SQL View nothing wraps. I can save it and then when I try to run
the
query I get an Access error dialog box that reads as follows:
__________________________________________________________________
Compile error, in query expression '[TYPE] Like "R*" AND InStr(","&[Enter
Region Numbers]&",",","&",")>0'
____________________________________________________________________
When I remove that part of the expression that begins with AND
InStr.......
the query will run just fine. When I put it back in I get the above
compile
error in the Access error dialog box. This leads me to believe that there
is
something missing in the expression you sent me and I have no clue what
that
would be.
Tim
:
You are missing "Like"
[TYPE] Like "R*" AND InStr(","&[Enter Region Numbers]&",",","&",")>0
--
Duane Hookom
MS Access MVP
It wasn't a typo. I cut and pasted exactly what you sent me. I
changed
it
by putting brackets [] around TYPE and I now get this error response:
Compile error, in query expression '[TYPE] "R*" AND InStr(","&[Enter
Region
Numbers]&",",","&",")>0'.
Since the query runs with the TYPE "R*" (without the brackets ([]) I
am
assuming
the error is in the AND InStr(","[Enter Region Numbers]&",",",")>0
Tim
:
Is this just a typo on your part "'TYPE "R*" AND InStr(","&[Enter
Region
Numbers]&",",","&",")>0'."
You may need to put brackets around [Type].
--
Duane Hookom
MS Access MVP
I have tried this latest suggestion and have insured that there
are no
wrap arounds in the SQL View.
I can save the SQL View but when I try to use the query Microsoft
Access
Dialong Box returns the following message: Compile error, in
query
expression 'TYPE "R*" AND InStr(","&[Enter Region
Numbers]&",",","&",")>0'.
Tim
:
I'm surprised that this would not work. Make sure you watch for
word
wrapping.
SELECT [NAME], STREET, PO_BOX,
CITY_ZIP, ZIP_CODE, TYPE
FROM Maint_Stations
WHERE TYPE Like "R*" AND
InStr("," & [Enter Region Numbers] & "," , "," & [DIST] & ",")>0
ORDER BY DIST;
--
Duane Hookom
MS Access MVP
Duane,
I still get the syntax error even with this latest change.
The
region
field
name
is DIST and I changed RegionID" to DIST and it still didn't
work.
It
keeps
saying
the syntax error is in the INStr.
Anyhow I did the following and this will work for me until I
can
get
someone
in our data base area to help me.
SELECT Maint_Stations.NAME, Maint_Stations.STREET,
Maint_Stations.PO_BOX,
Maint_Stations.CITY_ZIP
FROM Maint_Stations
WHERE (((Maint_Stations.DIST)=[1ST DIST NO] Or
(Maint_Stations.DIST)=[2ND
DIST NO] Or (Maint_Stations.DIST)=[3RD DIST NO]) AND
((Maint_Stations.TYPE)
Like "R*"))
ORDER BY Maint_Stations.DIST;
I can enter one District number or 3 numbers that shoould do
me
for
now.
Thanks for your assistance I greatly appreciate it so, let's
put
this
one
to
bed.
OK?
Tim
:
I don't know what your region field name is but I think you
might
have
included two "WHERE"s in your SQL which isn't allowed. Try
something
like:
SELECT Maint_Stations.NAME, Maint_Stations.STREET,
Maint_Stations.PO_BOX,
Maint_Stations.CITY_ZIP, Maint_Stations.ZIP_CODE,
Maint_Stations.TYPE
FROM Maint_Stations
WHERE Maint_Stations.TYPE Like "R*" AND InStr("," & [Enter
Region
Number/s]
& ",","," & [RegionID] & ",")>0
ORDER BY Maint_Stations.DIST;
--
Duane Hookom
MS Access MVP
I have changed the query and eliminated the <"s". The SQL
view is
now:
SELECT Maint_Stations.NAME, Maint_Stations.STREET,
Maint_Stations.PO_BOX,
Maint_Stations.CITY_ZIP, Maint_Stations.ZIP_CODE,
Maint_Stations.TYPE
FROM Maint_Stations
WHERE (((Maint_Stations.TYPE) Like "R*"))
ORDER BY Maint_Stations.DIST;
When I try to enter the string you sent on the criteria
line
in
design
view
I get an
error response that says: The expression you entered
contains
invalid
syntax. You may have entered an operand without an
operator.
If I try to paste the string you recommended directly into
SQL
View
when I
save it, it gives an error message that says: Syntax error
(missing
operator)
in query expression "where InStr(","&[Enter Region
Number/s]&",",","&[RegionID]&",")>0(((Maint_Stations.TYPE)Like"R*"))
Neither action (trying to enter the string) will not
complete,
that
is
why
you don't see anything in the SQL View that resembles what
you
recommended.
By the way, I inherited this database from someone else
and it
was
originally in Access 97 format and I had to convert it to
Access
2003
could
that be causing a problem? Also I am at the beginning end
of
the
learning
curve with Access which has probably already been obvious
to
you.
It's
hard
for this 62 year old dog to learn new tricks but, I'm at
least
asking
questions.
Tim
Please advise
:
I don't see anything like I recommended using Instr().
Also,
your
where
syntax looks messed up. Is TYPE supposed to be <"S"?
--
Duane Hookom
MS Access MVP
Here is the current SQL View:
SELECT Maint_Stations.NAME, Maint_Stations.STREET,
Maint_Stations.PO_BOX,
Maint_Stations.CITY_ZIP
FROM Maint_Stations
WHERE (((Maint_Stations.TYPE) Like "R*") AND
((([Maint_Stations].[TYPE])>"RMH")<"S"))
ORDER BY Maint_Stations.DIST;
Thank You for being patient.
:
Could you paste your full SQL view? How can we find
your
syntax
error
without seeing your sql or your data?
--
Duane Hookom
MS Access MVP
message
Thank You for your response Duane. I am still
having
trouble
I
put
this
in the criteria line in design mode and it just
gives
me
"Syntax
Error"
apparently
I am doing something wrong or it goes somewhere
else?
I
changed
the
"[RegionID]" to the name of the field in my
database
was
that
correct?
:
You can set up a where clause like:
WHERE InStr("," & [Enter Region Number/s] &
",",","
&
[RegionID]
&
",")>0
Users must enter values with a comma between and
no
spaces.
--
Duane Hookom
MS Access MVP
message
I have a parameter query that requests the
user to
enter a
region.
I
have
a
field that contains 12 regions numbered 1
through
12.
I
often
need
the
same
data from
more than one region at a time. How can I set
up
the
parameter
query
to
accept
more than one region? i.e "Enter Region
Number/s"
then
enter
1, 6
or
something
along that nature and have it return the
combined
data.
