Re: Set up Parameter Query to accept more than one entry

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Duane Hookom (duanehookom_at_NO_SPAMhotmail.com)
Date: 10/13/04 

Date: Tue, 12 Oct 2004 21:09:32 -0500

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
"Tim" <Tim@discussions.microsoft.com> wrote in message
news:F8884719-21FC-42E7-8184-8C11B068D334@microsoft.com...
> 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
>
> "Duane Hookom" wrote:
>
> > 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
> >
> >
> > "Tim" <Tim@discussions.microsoft.com> wrote in message
> > news:EF4AB300-5E93-4758-A448-1C274AB07605@microsoft.com...
> > > 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.
> > >
> > >
> > > "Duane Hookom" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "Tim" <Tim@discussions.microsoft.com> wrote in message
> > > > news:50F73A58-B5AE-4EC9-A568-11E4B6DD039B@microsoft.com...
> > > > > 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?
> > > > >
> > > > > "Duane Hookom" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > "Tim" <Tim@discussions.microsoft.com> wrote in message
> > > > > > news:F114AD7C-7E77-4B37-8B40-FC95B85AF66C@microsoft.com...
> > > > > > > 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.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Relevant Pages

  • Re: Using SQL in Sub to fill box on mouseclick
    ... In addition to Doug's point on the SQL syntax, ... This is because the first " character in your SQL string will be seen by VBA ...
    (microsoft.public.access.formscoding)
  • Re: Mailmerge, querystring and (un)logical SQL (Word 2003)
    ... Dim vsl, qs, airport_query, st_d, en_d As String ... As you can see st_d and en_d are strings formatted as dates in SQL ... the syntax of the part should be: ...
    (microsoft.public.word.mailmerge.fields)
  • Using a variable in SQL
    ... I'm trying to replace an integer number in a test WHERE string of my SQL with a variable. ... See below code snippet. ... Any syntax suggestions appreciated. ...
    (microsoft.public.vb.database)
  • SQL Syntax for a single quote in a string.
    ... I don't know how to write a syntax in SQL if there is a quote mark in a string. ...
    (borland.public.delphi.database.ado)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)