Re: SQL Syntax Error
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Fri, 6 Jul 2007 12:16:24 -0400
PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Long
, [Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Long
, [Forms]![QueryMultiValueSearchForm]![txtProjectGrant] Long;
SELECT [T].*
FROM [2007-08ScholarshipMaster] AS T
WHERE ([T].Name Like "*" &
[Forms]![QueryMultiValueSearchForm]![txtFilterName] & "*"
OR [Forms]![QueryMultiValueSearchForm]![txtFilterName] Is
Null)
AND ([Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Is Null or
OR [T].UMID =
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID])
AND ([Forms]![QueryMultiValueSearchForm]![txtProjectGrant] Is Null
OR [T].ProjectGrant =
[Forms]![QueryMultiValueSearchForm]![txtProjectGrant])
AND ([Forms]![QueryMultiValueSearchForm]![txtStartDate] Is Null
OR [T].ScholarshipStart =
[Forms]![QueryMultiValueSearchForm]![txtStartDate])
AND ([Forms]![QueryMultiValueSearchForm]![txtEndDate] Is Null
OR [T].ScholarshipEnd =
[Forms]![QueryMultiValueSearchForm]![txtEndDate])
That may be what you are attempting to do. It will return records that
match all the criteria you input. If you leave an item blank it will
"ignore" the criteria for that item
If you want a date range on the start and end dates you might want to change
the equals to >= and <= respectively. Be warned that the above query will
be changed when Access saves it. It may not change if you leave it in the
SQL view and never open it in the query grid view.
Your parameters are all declared as numbers of the type long, but you are
using LIKE with the Name field which tends to indicate it is a string field.
You may get an Error 13 Type Mismatch error.
Is Name a Number or is it text?
Is UMID a number or is it text?
Is ProjectGrant a number or is it text?
If you don't have ANY null values in a field there are ways to simplify the
above. For instance, if name is never null you can drop the criteria
[Forms]![QueryMultiValueSearchForm]![txtFilterName] Is Null
since if [Forms]![QueryMultiValueSearchForm]![txtFilterName] is blank
[T].Name Like "*" &
[Forms]![QueryMultiValueSearchForm]![txtFilterName] & "*"
will evaluate to return any record where the Name field is not null.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
"michelleumich" <mahampto@xxxxxxxxx> wrote in message
news:1183735044.214359.126910@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
John,
Thank you for your help. I made a few errors earlier when working
with the code. I am still very new to SQL and code in general,
especially within Access. I fixed the Brackets and a few other
things. here is the newly adapted code:
PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Long,
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Long, [Forms]!
[QueryMultiValueSearchForm]![txtProjectGrant] Long;
SELECT [2007-08ScholarshipMaster].*
FROM [2007-08ScholarshipMaster]
WHERE IIf (([Forms]![QueryMultiValueSearchForm]![txtFilterName] Is
Null) ,
IIf([2007-08ScholarshipMaster].Name Like "*" & [Forms]!
[QueryMultiValueSearchForm]![txtFilterName] & "*"))
AND (([Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Is Null)
OR ([2007-08ScholarshipMaster].UMID = [Forms]!
[QueryMultiValueSearchForm]![txtFilterUMID]))
AND (([Forms]![QueryMultiValueSearchForm]![txtProjectGrant] Is
Null)
OR ([2007-08ScholarshipMaster].ProjectGrant = [Forms]!
[QueryMultiValueSearchForm]![txtProjectGrant]))
AND (([Forms]![QueryMultiValueSearchForm]![txtStartDate] Is Null)
OR ([2007-08ScholarshipMaster].ScholarshipStart Like "*" & [Forms]!
[QueryMultiValueSearchForm]![txtStartDate] & "*"))
AND (([Forms]![QueryMultiValueSearchForm]![txtEndDate] Is Null)
OR ([2007-08ScholarshipMaster].ScholarshipEnd Like "*" & [Forms]!
[QueryMultiValueSearchForm]![txtEndDate] & "*"))
I'm going to try to answer your questions about what I think the
statements are doing to the best of my knowledge but I have altered
this code pretty significantly from the original that I found so most
likely I have created some type of syntax error. Basically, I am
trying to create a Query search (So I can use a Print macro) in which
I can enter partial entries and leave some boxes null without it
affecting the search. I think I messed up the "where" statement,
becuase There were other lines of code that had a similar format so I
deleted them and moved other lines up. I realize now that most likely
The syntax for the "where" statement is incorrect becuase of this. If
you would like to see the original code I would be more than happy to
post. To Marshall: thanks for your input, I just checked the spacing
in the parameter clause and it is just fine. To Kingston: If string
comparisons use "or" as a text qualifier does that mean I should
change all my AND statements to OR? This SQL statement is being used
as a Query itself.
Thanks,
M
.
- Follow-Ups:
- Re: SQL Syntax Error
- From: michelleumich
- Re: SQL Syntax Error
- References:
- SQL Syntax Error
- From: michelleumich
- Re: SQL Syntax Error
- From: John Spencer
- Re: SQL Syntax Error
- From: michelleumich
- SQL Syntax Error
- Prev by Date: Re: Remove first character
- Next by Date: Move Table Info And Delete
- Previous by thread: Re: SQL Syntax Error
- Next by thread: Re: SQL Syntax Error
- Index(es):
Relevant Pages
|
