Between...And query Parentheses removed

[Chris Watson]

I have written a query that selects values that fall between a range.
Although when I update the table "tbl_0100" (which is being used in
the query) the parentheses are taken out of the Between...And
statement and I get an Error 3075 in Access. I can no longer access
the query, so I must delete it and re-write the SQL. I was wondering
if there was a way to keep the parentheses in the query, because I
will have to update this table with new data periodically. It will be
unacceptable if I have to re-write the query every time I update the
data in the table. I have pasted the query below. I have seen a few
other posts with people having the same problem. Although there were
not any solutions posted. Thanks for all your help.

Query:

SELECT [qry_2400/2500].[WBS element]
FROM [qry_2400/2500] LEFT JOIN tbl_0100 ON ([qry_2400/2500].[WBS
element] Between [tbl_0100].[Field4] And [tbl_0100].[Field5])
WHERE ((([qry_2400/2500].[WBS element]) Not Like "76-090*" And
([qry_2400/2500].[WBS element]) Not Like "76-037*" And
([qry_2400/2500].[WBS element]) Not Like "76-038*" And
([qry_2400/2500].[WBS element]) Not Like "76R*" And
([qry_2400/2500].[WBS element]) Not Like "FY*") AND
((tbl_0100.Field10) Is Null))
ORDER BY [qry_2400/2500].[WBS element];

 

[Van T. Dinh]

Try NOT to open the Query in DesignView and use the
SQLView instead. Generally, if you use theSQL View,
Access will leave the SQL String alone. As soon as you
open the Query in DesignView, Access will try to re-
arrange your SQL String.

Do you mean the parentheses just after the ON keyword?

HTH
Van T. Dinh
MVP (Access)

 

[david epsom dot com dot au]

use a stored query instead of [tbl_0100]
then when you want to change tables, just update
the stored query. Don't touch the sensitive query.

stored query: Select * from [tbl_0100];

(david)

 

[Chris Watson]

use a stored query instead of [tbl_0100]
then when you want to change tables, just update
the stored query. Don't touch the sensitive query.

stored query: Select * from [tbl_0100];

(david)

I have written a query that selects values that fall between a range.
Although when I update the table "tbl_0100" (which is being used in
the query) the parentheses are taken out of the Between...And
statement and I get an Error 3075 in Access. I can no longer access
the query, so I must delete it and re-write the SQL. I was wondering
if there was a way to keep the parentheses in the query, because I
will have to update this table with new data periodically. It will be
unacceptable if I have to re-write the query every time I update the
data in the table. I have pasted the query below. I have seen a few
other posts with people having the same problem. Although there were
not any solutions posted. Thanks for all your help.

Query:

SELECT [qry_2400/2500].[WBS element]
FROM [qry_2400/2500] LEFT JOIN tbl_0100 ON ([qry_2400/2500].[WBS
element] Between [tbl_0100].[Field4] And [tbl_0100].[Field5])
WHERE ((([qry_2400/2500].[WBS element]) Not Like "76-090*" And
([qry_2400/2500].[WBS element]) Not Like "76-037*" And
([qry_2400/2500].[WBS element]) Not Like "76-038*" And
([qry_2400/2500].[WBS element]) Not Like "76R*" And
([qry_2400/2500].[WBS element]) Not Like "FY*") AND
((tbl_0100.Field10) Is Null))
ORDER BY [qry_2400/2500].[WBS element];

Thanks David. I did have a follow up question. How exactly do you
create a stored query in Access? Is it just a regular select query?
I am not familiar with that term, but that could be because I am an
Access newbie. Thanks very much.

 

[david epsom dot com dot au]

Stored queries (querydefs or views) are what you see
in the Access database window. You can store Select
queries, or Action queries or Pass Through queries
or... So yes, a stored query is just a regular query.

You can either store queries in your database, or
construct SQL at runtime as needed for your recordsets
and Execute actions.

In this case, because you can refer to a stored query
by name, it means that you don't have to change
your complex query in order to change the table that
it is based on.

(david)

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message

use a stored query instead of [tbl_0100]
then when you want to change tables, just update
the stored query. Don't touch the sensitive query.

stored query: Select * from [tbl_0100];

(david)

I have written a query that selects values that fall between a range.
Although when I update the table "tbl_0100" (which is being used in
the query) the parentheses are taken out of the Between...And
statement and I get an Error 3075 in Access. I can no longer access
the query, so I must delete it and re-write the SQL. I was wondering
if there was a way to keep the parentheses in the query, because I
will have to update this table with new data periodically. It will be
unacceptable if I have to re-write the query every time I update the
data in the table. I have pasted the query below. I have seen a few
other posts with people having the same problem. Although there were
not any solutions posted. Thanks for all your help.

Query:

SELECT [qry_2400/2500].[WBS element]
FROM [qry_2400/2500] LEFT JOIN tbl_0100 ON ([qry_2400/2500].[WBS
element] Between [tbl_0100].[Field4] And [tbl_0100].[Field5])
WHERE ((([qry_2400/2500].[WBS element]) Not Like "76-090*" And
([qry_2400/2500].[WBS element]) Not Like "76-037*" And
([qry_2400/2500].[WBS element]) Not Like "76-038*" And
([qry_2400/2500].[WBS element]) Not Like "76R*" And
([qry_2400/2500].[WBS element]) Not Like "FY*") AND
((tbl_0100.Field10) Is Null))
ORDER BY [qry_2400/2500].[WBS element];

Thanks David. I did have a follow up question. How exactly do you
create a stored query in Access? Is it just a regular select query?
I am not familiar with that term, but that could be because I am an
Access newbie. Thanks very much.