Query name with space on the end.

S

sjones

I just spent several hours building a rather complex query. When I use Docmd.OpenQuery "LookupGeneralDiscount",acViewDesign in the
Debug window it opens the Query just fine. When I use print CurrentDb.QueryDefs("LookupGeneralDiscount").Name I receive the message
"Item not found in collection."

Then I noticed in the Database window for queries that "LookupGeneralDiscount" had a space appended to the name, i.e.
"LookupGeneralDiscount ".

I opened the query with docmd using the name with a space and without. It is the same query. So I decided to remove the space and
received the message that the query already exists and "do I want to replace the existing query". I went ahead and renamed the query
and Access promptly deleted it for me and now is lost to the world.

Is anyone familiar with this behavior?

S Jones
 
U

UpRider

I'm not sure what you want with the print. What you appear to want is
something like "Who's buried in Grant's tomb?" The .NAME of the query
LookupGeneralDiscount is LookupGeneralDiscount. If you want to print the
SQL for the query, try something like:
MsgBox CurrentDb.QueryDefs("LookupGeneralDiscount").SQL, vbOKOnly, " S Q L
D I S P L A Y "

As for losing your query design, I haven't done that or heard of that
before.

UpRider
 
D

david

The Access database engine does not truncate trailing
white space: the Access user interface does. (This is
the opposite to SQL Server, where text fields normally
truncate trailing white space, and user interfaces normally
do not).

So the question is how you managed to get a query with
a trailing space in the name. I can do that, but not by building
the query the normal way, using Access.

Once you got the query with the trailing space in the name,
it was always going to be a problem, because the Access
user interface truncates trailing spaces, it can't really tell the
difference reliably.

Still, loosing the query was unfortunate. In the similar situation,
where you are trying to change the capitalisation of a name,
Access will often refuse/ignore your request. I guess in this
case, Access was unprepared.

(david)
 
Top