Dissapearing Query

[michelleumich]

Hi all,
I am working with a multiple parameter search query. The form I am
using to conduct the search displays the results in a subform. I
have
gone through and really combed through the SQL to make sure there are
no blatant spelling errors or incorrect references within the code.
Anyways when I open the form that it is attached to, I keep getting
the boxes described in this website http://allenbrowne.com/bug-13.html
.. The form is open, and I even changed names of fields in the table
in case SQL was choosing to be picky about what I named my fields.
Is
there anything else that would cause this error that anyone is aware
of?
So, after I open my form and those boxes pop up, I will try to play
around with the form a little to see if it's just a 'startup error'.
If I hit the command button I set up to run the Query I will go back
to the SQL after closing the form and Access will have deleted it.
Does anybody know why this is happening? Is there anything I can do
to fix this? Here is my code just in case it will help:
SELECT [2007-08ScholarshipMaster2].*
FROM [2007-08ScholarshipMaster2]
WHERE ([2007-08ScholarshipMaster2].Name Like "*" & Forms!
QueryMultiValueSearchForm!txtFilterName & "*"
Or Forms!QueryMultiValueSearchForm!txtFilterName Is Null)
And (Forms!QueryMultiValueSearchForm!txtFilterUMID Is Null
Or [2007-08ScholarshipMasterLink].UMID=Forms!
QueryMultiValueSearchForm!
txtFilterUMID)
And (Forms!QueryMultiValueSearchForm!txtProjectGrant Is Null
Or [2007-08ScholarshipMasterLink].PG=Forms!QueryMultiValueSearchForm!
txtProjectGrant)
And (Forms!QueryMultiValueSearchForm!txtStartDate Is Null
Or [2007-08ScholarshipMaster2].ScholarshipStart=Forms!
QueryMultiValueSearchForm!txtStartDate)
And (Forms!QueryMultiValueSearchForm!txtEndDate Is Null
Or [2007-08ScholarshipMaster2].ScholarshipEnd=Forms!
QueryMultiValueSearchForm!txtEndDate);

 

[Guest]

Hi (again) Michelle,

First, in the WHERE clause, you have "[2007-08ScholarshipMaster2].Name
Like...."

I would rename this field because "Name" is a reserved word in Access. It
looks like you are comparing the Name Property of the table with the data in
a control on a form. I'm not sure if you will get an error, "#NAME", the name
of the table or the wrong results.


I don't think that Access is actually deleting the query. I think it is not
being saved because of an error. In the conditions of the WHERE clause, there
is a reference to two fields in a table that is not in the query.

The only table in the FROM clause is "[2007-08ScholarshipMaster2]". But
there are also two conditions that compare two fields (UMID and PG) that are
in a different table ([2007-08ScholarshipMasterLink]).

Try taking out the second and third "AND" conditions to see if the query
still disappears.


(I looked in the mdb you sent me last month, but this query wasn't in it.)


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi all,
I am working with a multiple parameter search query. The form I am
using to conduct the search displays the results in a subform. I
have
gone through and really combed through the SQL to make sure there are
no blatant spelling errors or incorrect references within the code.
Anyways when I open the form that it is attached to, I keep getting
the boxes described in this website http://allenbrowne.com/bug-13.html
.. The form is open, and I even changed names of fields in the table
in case SQL was choosing to be picky about what I named my fields.
Is
there anything else that would cause this error that anyone is aware
of?
So, after I open my form and those boxes pop up, I will try to play
around with the form a little to see if it's just a 'startup error'.
If I hit the command button I set up to run the Query I will go back
to the SQL after closing the form and Access will have deleted it.
Does anybody know why this is happening? Is there anything I can do
to fix this? Here is my code just in case it will help:
SELECT [2007-08ScholarshipMaster2].*
FROM [2007-08ScholarshipMaster2]
WHERE ([2007-08ScholarshipMaster2].Name Like "*" & Forms!
QueryMultiValueSearchForm!txtFilterName & "*"
Or Forms!QueryMultiValueSearchForm!txtFilterName Is Null)
And (Forms!QueryMultiValueSearchForm!txtFilterUMID Is Null
Or [2007-08ScholarshipMasterLink].UMID=Forms!
QueryMultiValueSearchForm!
txtFilterUMID)
And (Forms!QueryMultiValueSearchForm!txtProjectGrant Is Null
Or [2007-08ScholarshipMasterLink].PG=Forms!QueryMultiValueSearchForm!
txtProjectGrant)
And (Forms!QueryMultiValueSearchForm!txtStartDate Is Null
Or [2007-08ScholarshipMaster2].ScholarshipStart=Forms!
QueryMultiValueSearchForm!txtStartDate)
And (Forms!QueryMultiValueSearchForm!txtEndDate Is Null
Or [2007-08ScholarshipMaster2].ScholarshipEnd=Forms!
QueryMultiValueSearchForm!txtEndDate);

 

[michelleumich]

Steve,
Thank you so much for your response and help. You've been by far the
most helpful person to me on this message board and I am truly
appreciative.

Firstly: I changed Name to "FullName". Thanks for the advice. I
remember you saying this before, but I was so excited to finally get
the table working it slipped my mind...

I think this is a good theory. (Access simply not saving the Query.)
The reason I have multiple tables now in my Query is because I split
up the [2007-08ScholarshipMaster] due to repetitive data. I wanted to
give my data a better design with the tables. Now none of my data is
repetitive, but unfortunately my data has a many-many relationship so
it makes doing Queries etc more complicated.

That is where this Query comes in (and why it wasn't a month ago). I
need to re-do my form to work with my new table setup and I can't do
it with the filter i'm using (the filter only works with one table..I
think)
I can send you a new blank mdb if you want. I basically have three
tables now, one with student information, one with scholarship
information, and one that links the two.

To answer your troubleshooting:
I entered this in:
SELECT [2007-08ScholarshipMaster2].*
FROM [2007-08ScholarshipMaster2]
WHERE ([2007-08ScholarshipMaster2].Name Like "*" & Forms!
QueryMultiValueSearchForm!txtFilterName & "*" Or Forms!
QueryMultiValueSearchForm!txtFilterName Is Null) And (Forms!
QueryMultiValueSearchForm!txtStartDate Is Null Or
[2007-08ScholarshipMaster2].ScholarshipStart=Forms!
QueryMultiValueSearchForm!txtStartDate) And (Forms!
QueryMultiValueSearchForm!txtEndDate Is Null Or
[2007-08ScholarshipMaster2].ScholarshipEnd=Forms!
QueryMultiValueSearchForm!txtEndDate);

And it worked.

Is the solution to add all of the tables in the "From" clause?

 

[Guest]

Hi Michelle,

It would help if you would send the new mdb. That way we are looking at the
same thing. Maybe put in a few records (Mickey Mouse, Minny Mouse, etc.) so I
can see how things relate...


I might jump around a bit answering your questions, but here goes....

Is the solution to add all of the tables in the "From" clause?

Only if the tables are related to each other and you need a field from that
table or the table is linked to another table where you need a field.

That is where this Query comes in (and why it wasn't a month ago). I
need to re-do my form to work with my new table setup and I can't do
it with the filter i'm using (the filter only works with one table..I
think)

I don't know of any reason you can't use a filter.

The reason I have multiple tables now in my Query is because I split
up the [2007-08ScholarshipMaster] due to repetitive data. I wanted to

I was looking at the old [2007-08ScholarshipMaster] table and I see at least
6 tables and maybe 8. (I still struggle with table design.) But I don't know
what the data looks like or how it is related.

When you see fields in a table like this:

ScholarshipName
ScholarshipName 2

it is an indication that the tables are not normalized. Another indication
is when tables are named with dates (years). What do you do for the 2008-09
school year? Create new tables, queries, reports and code??

Looking some of the queries, you have:

2003-2007 Student Demographic Query
2004-2008 Student Demographic Query
2005-2009 Student Demographic Query
etc.

and

2003-2007 Student Scholarship Query
2004-2008 Student Scholarship Query
2005-2009 Student Scholarship Query
etc.


AFAICT, the queries for Demographics are the same except for the Scholarship
start year and the "....Student Scholarship Query" queries are the same
except for the Scholarship start year. You use one query for each and have
the Scholarship start year as a parameter. Then you don't have to create a
new query each year.


Nothing above is meant as a criticism, just things I have learned
(mostly from this NG)


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,
Thank you so much for your response and help. You've been by far the
most helpful person to me on this message board and I am truly
appreciative.

Firstly: I changed Name to "FullName". Thanks for the advice. I
remember you saying this before, but I was so excited to finally get
the table working it slipped my mind...

I think this is a good theory. (Access simply not saving the Query.)
The reason I have multiple tables now in my Query is because I split
up the [2007-08ScholarshipMaster] due to repetitive data. I wanted to
give my data a better design with the tables. Now none of my data is
repetitive, but unfortunately my data has a many-many relationship so
it makes doing Queries etc more complicated.

That is where this Query comes in (and why it wasn't a month ago). I
need to re-do my form to work with my new table setup and I can't do
it with the filter i'm using (the filter only works with one table..I
think)
I can send you a new blank mdb if you want. I basically have three
tables now, one with student information, one with scholarship
information, and one that links the two.

To answer your troubleshooting:
I entered this in:
SELECT [2007-08ScholarshipMaster2].*
FROM [2007-08ScholarshipMaster2]
WHERE ([2007-08ScholarshipMaster2].Name Like "*" & Forms!
QueryMultiValueSearchForm!txtFilterName & "*" Or Forms!
QueryMultiValueSearchForm!txtFilterName Is Null) And (Forms!
QueryMultiValueSearchForm!txtStartDate Is Null Or
[2007-08ScholarshipMaster2].ScholarshipStart=Forms!
QueryMultiValueSearchForm!txtStartDate) And (Forms!
QueryMultiValueSearchForm!txtEndDate Is Null Or
[2007-08ScholarshipMaster2].ScholarshipEnd=Forms!
QueryMultiValueSearchForm!txtEndDate);

And it worked.

Is the solution to add all of the tables in the "From" clause?