SQL Syntax Error

M

michelleumich

Hi, I am creating a search Query using SQL adapted from an online
resource. While 'translating' the code to adapt to my Database I
received a syntax error notification while trying to run. I have
searched through the parenthesis and cannot find the Error! Does
Microsoft access have false error notifications sometimes? Is this a
bug I should be aware of? Is it a problem with my code?
Here is the code:

PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Long,
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Long, [Forms]!
[QueryMultiValueSearchForm]![txtProjectGrant] Long;
SELECT tblClient.*
FROM tblClient
WHERE IIf (([Forms]![QueryMultiValueSearchForm]![txtFilterName] Is
Null) ,
IIf(2007-08ScholarshipMaster.Name Like "*" & [Forms]!
[frmClientSearch]![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]![txtFilterName] Is Null)
OR (2007-08ScholarshipMaster.ScholarshipStart Like "*" & [Forms]!
[QueryMultiValueSearchForm]![txtStartDate] & "*"))



If anyone spots any obvious errors let me know.
Thanks,
M
 
J

John Spencer

What is 2007-08ScholarshipMaster? Is it a table or a query? If so, you
must reference it in the FROM clause and it probably should be joined to
tblClient.

Seond can you describe what you are attempting to do in that where clause?

IS Forms]![frmClientSearch]![txtFilterName] supposed to be
[Forms]![QueryMultiValueSearchForm]![txtFilterName]?
Is Name a text field?


PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Long,
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Long,
[Forms]![QueryMultiValueSearchForm]![txtProjectGrant] Long;
SELECT tblClient.*
FROM tblClient
WHERE (2007-08ScholarshipMaster.Name Like "*" &
[Forms]![???]![txtFilterName] & "*" OR
[Forms]![???]![txtFilterName] is Null)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
K

kingston via AccessMonster.com

I'm guessing this should be in brackets: [2007-08ScholarshipMaster]
My preference is to look for Nulls with: IsNull(...)
Comparisons for strings need the text qualifier " or ' unlike numeric
comparisons.
Where are you using this SQL statement - in VBA or a visual query?
Hi, I am creating a search Query using SQL adapted from an online
resource. While 'translating' the code to adapt to my Database I
received a syntax error notification while trying to run. I have
searched through the parenthesis and cannot find the Error! Does
Microsoft access have false error notifications sometimes? Is this a
bug I should be aware of? Is it a problem with my code?
Here is the code:

PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Long,
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Long, [Forms]!
[QueryMultiValueSearchForm]![txtProjectGrant] Long;
SELECT tblClient.*
FROM tblClient
WHERE IIf (([Forms]![QueryMultiValueSearchForm]![txtFilterName] Is
Null) ,
IIf(2007-08ScholarshipMaster.Name Like "*" & [Forms]!
[frmClientSearch]![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]![txtFilterName] Is Null)
OR (2007-08ScholarshipMaster.ScholarshipStart Like "*" & [Forms]!
[QueryMultiValueSearchForm]![txtStartDate] & "*"))

If anyone spots any obvious errors let me know.
Thanks,
M
Click to expand...
 
M

Marshall Barton

michelleumich said:
Hi, I am creating a search Query using SQL adapted from an online
resource. While 'translating' the code to adapt to my Database I
received a syntax error notification while trying to run. I have
searched through the parenthesis and cannot find the Error! Does
Microsoft access have false error notifications sometimes? Is this a
bug I should be aware of? Is it a problem with my code?
Here is the code:

PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Long,
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Long, [Forms]!
[QueryMultiValueSearchForm]![txtProjectGrant] Long;
SELECT tblClient.*
FROM tblClient
WHERE IIf (([Forms]![QueryMultiValueSearchForm]![txtFilterName] Is
Null) ,
IIf(2007-08ScholarshipMaster.Name Like "*" & [Forms]!
[frmClientSearch]![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]![txtFilterName] Is Null)
OR (2007-08ScholarshipMaster.ScholarshipStart Like "*" & [Forms]!
[QueryMultiValueSearchForm]![txtStartDate] & "*"))
Click to expand...


You are missing [ ] arount the name:
2007-08ScholarshipMaster

I can't tell because of mail reader line wrapping, but you
might have some extra spaces in the middle of a parameter
reference.
 
M

michelleumich

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
 
M

michelleumich

Also: After making the posted changes, I am now receiving a "Wrong
Number of arguments used with Expression" error. I am a newbie with
all of this coding and I have learned a lot in the last week or so but
I am sure you developers understand it is not an overnight process. I
genuinely appreciate everybody's input.
M
 
J

John Spencer

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 said:
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
Click to expand...
 
M

michelleumich

John,

Thank you so much for your help. All three are Text Boxes. Start/End
date are by year only. Unfortunately, I tried your code and with a
few adjustments it works but will not save properly. I will save it
in SQL mode as the code you gave me but will not work properly. To
get back into SQL mode I have to open the Query in Design view. Once I
get back into SQL view the only thing written in the Query is
"SELECT;" Somehow Access decides to delete it. (I believe you
mentioned it not saving correctly for whatever reason.) If this is
happening is there any way to prevent it? This error occured even
before I opened the Query back up in SQL. If searching within a Query
is now not an option do you know of how to apply a print macro to a
search form without using one?
Thanks,
M
 
J

John Spencer

It should save properly, it just saves it as a more complex query.

You didn't answer my earlier questions. Are the FIELDS text or numbers?
The controls are textboxes, but what are the FIELD types?

Also Do the FIELDS (not the controls on the form) always have a value.

Fields are an attribute of tables and are where the data is stored.
Controls are an attribute of forms and reports and may show the data that is
stored in fields or may show other information that is not in a field of a
table.

Terminology can be important in understanding what is going on here.

Can you copy and paste the SQL with the adjustments you made?



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
M

michelleumich

John,
Apologies. The fields are text fields. all of them. Aside from the
primary key, all of the fields do not necessarily have a value.
Thanks for your patience...I get a lot of the terminology mixed up
sometimes.
Here's the updated SQL:
PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Long,
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Long, [Forms]!
[QueryMultiValueSearchForm]![txtProjectGrant] Long;
SELECT [2007-08ScholarshipMaster].*
FROM [2007-08ScholarshipMaster]
WHERE ([2007-08ScholarshipMaster].Name Like "*" & [Forms]!
[QueryMultiValueSearchForm]![txtFilterName] & "*"
OR [Forms]![QueryMultiValueSearchForm]![txtFilterName] Is Null)
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 =[Forms]!
[QueryMultiValueSearchForm]![txtStartDate])
AND ([Forms]![QueryMultiValueSearchForm]![txtEndDate] Is Null
OR [2007-08ScholarshipMaster].ScholarshipEnd =[Forms]!
[QueryMultiValueSearchForm]![txtEndDate])


However, this does not seem to be working with my form. I made sure
the text boxes in the SQL matched with the ones on the form. Sorry
for not pasting the SQL earlier, an absentminded mistake. Thanks
again.
M
 
J

John Spencer

Step one. Declare your parameters as text parameters instead of numeric
(Long) parameters.

Step two. Lets's try trouble shooting this thing by adding in one set of
criteria at a time.

PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Text (255)
, [Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Text (255)
, [Forms]![QueryMultiValueSearchForm]![txtProjectGrant] Text (255);
SELECT [2007-08ScholarshipMaster].*
FROM [2007-08ScholarshipMaster]
WHERE ([2007-08ScholarshipMaster].Name Like
"*" & [Forms]![QueryMultiValueSearchForm]![txtFilterName] & "*"
OR [Forms]![QueryMultiValueSearchForm]![txtFilterName] Is Null)

Does that work and can you save it?

IF so, add the next bit
AND ([Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Is Null
OR [2007-08ScholarshipMaster].UMID =
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID])

Does that work and can you save it? Repeat until you get an error or bad
results. Then please tell us what the error was or why the results were
bad. When you say it "doesn't work for my form" I have no clue how it is
failing to meet your expectations. Bad data, no data, crashes, syntax
errors, computer bursting into flames, operator tearing out hair are all
options.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

michelleumich said:
John,
Apologies. The fields are text fields. all of them. Aside from the
primary key, all of the fields do not necessarily have a value.
Thanks for your patience...I get a lot of the terminology mixed up
sometimes.
Here's the updated SQL:
PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Long,
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Long, [Forms]!
[QueryMultiValueSearchForm]![txtProjectGrant] Long;
SELECT [2007-08ScholarshipMaster].*
FROM [2007-08ScholarshipMaster]
WHERE ([2007-08ScholarshipMaster].Name Like "*" & [Forms]!
[QueryMultiValueSearchForm]![txtFilterName] & "*"
OR [Forms]![QueryMultiValueSearchForm]![txtFilterName] Is Null)
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 =[Forms]!
[QueryMultiValueSearchForm]![txtStartDate])
AND ([Forms]![QueryMultiValueSearchForm]![txtEndDate] Is Null
OR [2007-08ScholarshipMaster].ScholarshipEnd =[Forms]!
[QueryMultiValueSearchForm]![txtEndDate])


However, this does not seem to be working with my form. I made sure
the text boxes in the SQL matched with the ones on the form. Sorry
for not pasting the SQL earlier, an absentminded mistake. Thanks
again.
M
Click to expand...
 
M

michelleumich

John,
I declared my parameters as text and that went well. As for the
troubleshooting, I received an error when I entered in the third "step
of data". When I entered: (After the two statements you 'started me
out with' above)

AND ([Forms]![QueryMultiValueSearchForm]![txtProjectGrant] Is Null
OR [2007-08ScholarshipMaster].ProjectGrant =[Forms]!
[QueryMultiValueSearchForm]![txtProjectGrant])

I received this error: "Invalid use of ., !, or () in Query Expression
' ([2007-08ScholarshipMaster].Name Like "*" & Forms!
QueryMultiValueSearchForm!txtFilterName & "*" Or Forms!
QueryMultiValueSearchForm!txtFilterName Is Null)
AND ([Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Is Null
OR [2007-08ScholarshipMaster].UMID"

I looked over the syntax and I see no difference between the two
statements I have so far. Access added a semicolon at the end of the
clause when saving and to add new material I had to eliminate that.
Other than that I have not touched it.

Regarding the "query not working with my form" comment, I'll wait to
talk about that until after the Query works properly. That question
may fix itself.
Anyways, thanks again :)
M


Step one. Declare your parameters as text parameters instead of numeric
(Long) parameters.

Step two. Lets's try trouble shooting this thing by adding in one set of
criteria at a time.

PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Text (255)
, [Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Text (255)
, [Forms]![QueryMultiValueSearchForm]![txtProjectGrant] Text (255);
SELECT [2007-08ScholarshipMaster].*
FROM [2007-08ScholarshipMaster]
WHERE ([2007-08ScholarshipMaster].Name Like
"*" & [Forms]![QueryMultiValueSearchForm]![txtFilterName] & "*"
OR [Forms]![QueryMultiValueSearchForm]![txtFilterName] Is Null)

Does that work and can you save it?

IF so, add the next bit
AND ([Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Is Null
OR [2007-08ScholarshipMaster].UMID =
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID])

Does that work and can you save it? Repeat until you get an error or bad
results. Then please tell us what the error was or why the results were
bad. When you say it "doesn't work for my form" I have no clue how it is
failing to meet your expectations. Bad data, no data, crashes, syntax
errors, computer bursting into flames, operator tearing out hair are all
options.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




John,
Apologies. The fields are text fields. all of them. Aside from the
primary key, all of the fields do not necessarily have a value.
Thanks for your patience...I get a lot of the terminology mixed up
sometimes.
Here's the updated SQL:
PARAMETERS [Forms]![QueryMultiValueSearchForm]![txtFilterName] Long,
[Forms]![QueryMultiValueSearchForm]![txtFilterUMID] Long, [Forms]!
[QueryMultiValueSearchForm]![txtProjectGrant] Long;
SELECT [2007-08ScholarshipMaster].*
FROM [2007-08ScholarshipMaster]
WHERE ([2007-08ScholarshipMaster].Name Like "*" & [Forms]!
[QueryMultiValueSearchForm]![txtFilterName] & "*"
OR [Forms]![QueryMultiValueSearchForm]![txtFilterName] Is Null)
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 =[Forms]!
[QueryMultiValueSearchForm]![txtStartDate])
AND ([Forms]![QueryMultiValueSearchForm]![txtEndDate] Is Null
OR [2007-08ScholarshipMaster].ScholarshipEnd =[Forms]!
[QueryMultiValueSearchForm]![txtEndDate])
Click to expand...
However, this does not seem to be working with my form. I made sure
the text boxes in the SQL matched with the ones on the form. Sorry
for not pasting the SQL earlier, an absentminded mistake. Thanks
again.
M- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
M

michelleumich

Also: I realized now that Access only deletes my query leaving only
"select;" if I try to run my query with a macro and and a command
button. I am not sure if this is of significance but I figured I
should clarify.
 

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

Access Deleting a Query SQL 0
Dissapearing Query 3
Multi Value Many-Many Relationship Query 3
Characters found at end of sql error-Repost 2
Is field between date range on form 2
Union Query Edit 1
IIF in Query 2
yes/no or both Error 3071 2

Top