It has come to my attention that sometimes, when I open a Query in SQL
View, the SQL that I see is not exactly the same as the SQL in the
Query's Querydef. The difference I see (only occasionally) has to do
with square brackets and dot on some tblName.FieldNa me portions of the
query.
The following routine demonstrates how I see the QueryDef's SQL:
Sub DbgQuery(sQryNa me As String)
'' Prints the SQL for the given query to the Debug Window
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.Query Defs(sQryName)
Debug.Print qdf.SQL
Set qdf = Nothing
End Sub
I have some VBA code that compares queries of the same name accross
different mdb's and have come across some instances where the SQL is
identical when I view it in the SQL View of the Query, but my Compare
code is picking up differences. In each of these cases, the VBA is
showing extra brackets, but I cannot see it in the SQL View and thus
they look the same.
Has anyone ever observed this before? Is there any way I can get
'identical Queries' to look the same via VBA? I am so surprised that
the SQL View does not match the QueryDef.SQL. I've been comparing
queries across mdbs like this for months and had not seen this before,
and now suddenly, four or five of them have developed differences as a
result of the brackets! I am quite dependent on my compare technique
and this new quirk is posing me a real challenge.
Any comments or insights greatly appreciated.
TIA,
Christine
9  5576 
Hi Christine.
I've never seen this. If you have not already done so, uncheck the boxes
under:
Tools | Options | General | Name AutoCorrect
Access will add square brackets around a field name that requires it (e.g.
begins with a number, or contains a space), so I am wondering whether Name
AutoCorrect thinks it's safer to add the square brackets. After turning this
off, compact the database, then open the query in SQL View, change the text
and save.
If that does not fix the problem, please post back with more info. The only
issue I've seen with comparing the SQL of the QueryDef is that it tends to
have a trailing CrLf.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Christine" <ct******@stny. rr.com> wrote in message
news:24******** *************** **@posting.goog le.com... It has come to my attention that sometimes, when I open a Query in SQL
View, the SQL that I see is not exactly the same as the SQL in the
Query's Querydef. The difference I see (only occasionally) has to do
with square brackets and dot on some tblName.FieldNa me portions of the
query.
The following routine demonstrates how I see the QueryDef's SQL:
Sub DbgQuery(sQryNa me As String)
'' Prints the SQL for the given query to the Debug Window
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.Query Defs(sQryName)
Debug.Print qdf.SQL
Set qdf = Nothing
End Sub
I have some VBA code that compares queries of the same name accross
different mdb's and have come across some instances where the SQL is
identical when I view it in the SQL View of the Query, but my Compare
code is picking up differences. In each of these cases, the VBA is
showing extra brackets, but I cannot see it in the SQL View and thus
they look the same.
Has anyone ever observed this before? Is there any way I can get
'identical Queries' to look the same via VBA? I am so surprised that
the SQL View does not match the QueryDef.SQL. I've been comparing
queries across mdbs like this for months and had not seen this before,
and now suddenly, four or five of them have developed differences as a
result of the brackets! I am quite dependent on my compare technique
and this new quirk is posing me a real challenge.
Any comments or insights greatly appreciated.
TIA,
Christine
PMFJI...
Perhaps this is the redefinition by ACCESS of the subquery to the
[subquery]. syntax?
Access translates FROM-clause subqueries in the standard parenthetical form
into the "[subquery]. " form, and any square bracket inside the subquery's
enclosing brackets will interfere with the correct interpretation of it.
Even a bracketed [table].[field] reference will cause the problem. I know
of no way around this but to rewrite the query to avoid the use of a FROM
subquery.
--
Ken Snell
<MS ACCESS MVP>
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:41******** **************@ per-qv1-newsreader-01.iinet.net.au ... Hi Christine.
I've never seen this. If you have not already done so, uncheck the boxes
under:
Tools | Options | General | Name AutoCorrect
Access will add square brackets around a field name that requires it (e.g.
begins with a number, or contains a space), so I am wondering whether Name
AutoCorrect thinks it's safer to add the square brackets. After turning
this off, compact the database, then open the query in SQL View, change the
text and save.
If that does not fix the problem, please post back with more info. The
only issue I've seen with comparing the SQL of the QueryDef is that it tends to
have a trailing CrLf.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Christine" <ct******@stny. rr.com> wrote in message
news:24******** *************** **@posting.goog le.com... It has come to my attention that sometimes, when I open a Query in SQL
View, the SQL that I see is not exactly the same as the SQL in the
Query's Querydef. The difference I see (only occasionally) has to do
with square brackets and dot on some tblName.FieldNa me portions of the
query.
The following routine demonstrates how I see the QueryDef's SQL:
Sub DbgQuery(sQryNa me As String)
'' Prints the SQL for the given query to the Debug Window
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.Query Defs(sQryName)
Debug.Print qdf.SQL
Set qdf = Nothing
End Sub
I have some VBA code that compares queries of the same name accross
different mdb's and have come across some instances where the SQL is
identical when I view it in the SQL View of the Query, but my Compare
code is picking up differences. In each of these cases, the VBA is
showing extra brackets, but I cannot see it in the SQL View and thus
they look the same.
Has anyone ever observed this before? Is there any way I can get
'identical Queries' to look the same via VBA? I am so surprised that
the SQL View does not match the QueryDef.SQL. I've been comparing
queries across mdbs like this for months and had not seen this before,
and now suddenly, four or five of them have developed differences as a
result of the brackets! I am quite dependent on my compare technique
and this new quirk is posing me a real challenge.
Any comments or insights greatly appreciated.
TIA,
Christine
Ken, thanks for that.
Can you give an example of where JET might do this?
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Ken Snell [MVP]" <kt***********@ ncoomcastt.rena etl> wrote in message
news:e9******** ******@TK2MSFTN GP14.phx.gbl... PMFJI...
Perhaps this is the redefinition by ACCESS of the subquery to the
[subquery]. syntax?
Access translates FROM-clause subqueries in the standard parenthetical
form
into the "[subquery]. " form, and any square bracket inside the subquery's
enclosing brackets will interfere with the correct interpretation of it.
Even a bracketed [table].[field] reference will cause the problem. I know
of no way around this but to rewrite the query to avoid the use of a FROM
subquery.
--
Ken Snell
<MS ACCESS MVP>
Ken's right; Access does this all the time. I've experienced the behaviour
many times, because I build a lot of nested subqueries.
To resolve the issue, replace the square brackets with round ones, and
remove the dot.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
"Ken Snell [MVP]" <kt***********@ ncoomcastt.rena etl> wrote in message
news:e9******** ******@TK2MSFTN GP14.phx.gbl... PMFJI...
Perhaps this is the redefinition by ACCESS of the subquery to the
[subquery]. syntax?
Access translates FROM-clause subqueries in the standard parenthetical
form into the "[subquery]. " form, and any square bracket inside the subquery's
enclosing brackets will interfere with the correct interpretation of it.
Even a bracketed [table].[field] reference will cause the problem. I know
of no way around this but to rewrite the query to avoid the use of a FROM
subquery.
--
Ken Snell
<MS ACCESS MVP>
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:41******** **************@ per-qv1-newsreader-01.iinet.net.au ... Hi Christine.
I've never seen this. If you have not already done so, uncheck the boxes
under:
Tools | Options | General | Name AutoCorrect
Access will add square brackets around a field name that requires it
(e.g. begins with a number, or contains a space), so I am wondering whether
Name AutoCorrect thinks it's safer to add the square brackets. After turning
this off, compact the database, then open the query in SQL View, change the
text and save.
If that does not fix the problem, please post back with more info. The
only issue I've seen with comparing the SQL of the QueryDef is that it tends
to have a trailing CrLf.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Christine" <ct******@stny. rr.com> wrote in message
news:24******** *************** **@posting.goog le.com... It has come to my attention that sometimes, when I open a Query in SQL
View, the SQL that I see is not exactly the same as the SQL in the
Query's Querydef. The difference I see (only occasionally) has to do
with square brackets and dot on some tblName.FieldNa me portions of the
query.
The following routine demonstrates how I see the QueryDef's SQL:
Sub DbgQuery(sQryNa me As String)
'' Prints the SQL for the given query to the Debug Window
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.Query Defs(sQryName)
Debug.Print qdf.SQL
Set qdf = Nothing
End Sub
I have some VBA code that compares queries of the same name accross
different mdb's and have come across some instances where the SQL is
identical when I view it in the SQL View of the Query, but my Compare
code is picking up differences. In each of these cases, the VBA is
showing extra brackets, but I cannot see it in the SQL View and thus
they look the same.
Has anyone ever observed this before? Is there any way I can get
'identical Queries' to look the same via VBA? I am so surprised that
the SQL View does not match the QueryDef.SQL. I've been comparing
queries across mdbs like this for months and had not seen this before,
and now suddenly, four or five of them have developed differences as a
result of the brackets! I am quite dependent on my compare technique
and this new quirk is posing me a real challenge.
Any comments or insights greatly appreciated.
TIA,
Christine
Whenever I try to use a subquery as a FROM table, Jet does this after saving
and closing the query. I get around it by rewriting the query in some way to
use a subquery as the list of values in an
WHERE field IN (subquery)
syntax or something else.
--
Ken Snell
<MS ACCESS MVP>
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:41******** **************@ per-qv1-newsreader-01.iinet.net.au ... Ken, thanks for that.
Can you give an example of where JET might do this?
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Ken Snell [MVP]" <kt***********@ ncoomcastt.rena etl> wrote in message
news:e9******** ******@TK2MSFTN GP14.phx.gbl... PMFJI...
Perhaps this is the redefinition by ACCESS of the subquery to the
[subquery]. syntax?
Access translates FROM-clause subqueries in the standard parenthetical
form
into the "[subquery]. " form, and any square bracket inside the
subquery's enclosing brackets will interfere with the correct interpretation of it.
Even a bracketed [table].[field] reference will cause the problem. I
know of no way around this but to rewrite the query to avoid the use of a
FROM subquery.
--
Ken Snell
<MS ACCESS MVP>
Many thanks to Allen, Ken & Graham for your interest and input. I
think Allen's idea of my problem being related to AutoCorrect issues
is the most likely. I was not exactly able to fix the problem by
following Allen's recommendation but it got me looking further into
the DAO.queryDef properties. I was hoping for some other SQL like
prperty that would match what I see in the interactive SQL View, but
no such luck.
However, I did discover the DOL property and see significant
differences there that presumably could have been applied (by
Access/Jet?) to the interactive SQL View SQL to produce the
Querdef.SQL that I get via VBA/DAO. In looking through the newsgroups,
I did discover snippets tying the DOL prepoerty to the AutoCorrect
option. Allen, I believe you were involved in those threads, in fact I
tried following a link to a web page of yours that was supposed to
give more info on (I think) some of the hazards of Autocorrect) but it
was a broken link.
The queries in question did not have any subqueries but they did
involve tables whose structures had been changing lately, so that is
probably why their DOL's had been touched. As a result of each of your
inputs, I was able to follow up with more investigation today, and at
least now feel more comfortale and educated about AutoCorrect and
other changes that Access will make to my queries and not as anxious
that suddenly all my queries are going to just start changing
themselves out of the blue, and totally destroy my Compare technique.
I'm not yet sure that I want to turn off Name AutoCorrect alltogether
for ever. Allen, if you should happen to refresh that link to your web
site that sheds more light on this, I'd appreciation knowing about it.
So a big thank-you to you all - how great to get so many educated and
well-informed reponses overnight. It really turns our time difference
into a huge plus. And turned my despair & disbelief as of late
yesterday to a manageable and explorable issue today.
Many Thanks & Regards,
Christine
Hi Christine
My web host was off-line for a couple of hours. The page should be availabe
again now. It's: http://members.iinet.net.au/~allenbrowne/bug-03.html
You may be surprised how many problems it causes.
I have not experienced Access stuffing up the QueryDef SQL based on Name
AutoCorrect, but Ken and Graham have clearly experienced the issue and
identified a cause. So, if you have a subquery in the FROM clause, follow
their advice to work around this situation. If you do not have a subquery in
the FROM clause, the issue may relate to some of the other things you
raised.
For anyone who is not sure what a subquery is, see: http://support.microsoft.com/?id=209066
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Christine" <ct******@stny. rr.com> wrote in message
news:24******** *************** **@posting.goog le.com... Many thanks to Allen, Ken & Graham for your interest and input. I
think Allen's idea of my problem being related to AutoCorrect issues
is the most likely. I was not exactly able to fix the problem by
following Allen's recommendation but it got me looking further into
the DAO.queryDef properties. I was hoping for some other SQL like
prperty that would match what I see in the interactive SQL View, but
no such luck.
However, I did discover the DOL property and see significant
differences there that presumably could have been applied (by
Access/Jet?) to the interactive SQL View SQL to produce the
Querdef.SQL that I get via VBA/DAO. In looking through the newsgroups,
I did discover snippets tying the DOL prepoerty to the AutoCorrect
option. Allen, I believe you were involved in those threads, in fact I
tried following a link to a web page of yours that was supposed to
give more info on (I think) some of the hazards of Autocorrect) but it
was a broken link.
The queries in question did not have any subqueries but they did
involve tables whose structures had been changing lately, so that is
probably why their DOL's had been touched. As a result of each of your
inputs, I was able to follow up with more investigation today, and at
least now feel more comfortale and educated about AutoCorrect and
other changes that Access will make to my queries and not as anxious
that suddenly all my queries are going to just start changing
themselves out of the blue, and totally destroy my Compare technique.
I'm not yet sure that I want to turn off Name AutoCorrect alltogether
for ever. Allen, if you should happen to refresh that link to your web
site that sheds more light on this, I'd appreciation knowing about it.
So a big thank-you to you all - how great to get so many educated and
well-informed reponses overnight. It really turns our time difference
into a huge plus. And turned my despair & disbelief as of late
yesterday to a manageable and explorable issue today.
Many Thanks & Regards,
Christine
Thanks Allen,
Your web page on the hazards of Name AutoCorrect was very
informative. I am now convinced and will go through the process of
turning it off in all my mdbs.
I have experienced the very unpleasant situation in the past of Access
shutting down when I was in the process of changing field names in
tables and trying to open queries. At the time, I did not know what on
earth was going on and it was a huge battle to get Access to stay open
long enough for me to be able to correct the situation. One particular
situation was just dreadful - an absolute nightmare. So 1) I am very
pleased to understand what was really going on and 2) avoiding that
from ever happening again is more than enough of an argument to turn
it off.
Thanks so much for your gracious help.
Best Regards,
Christine
Allen,
I've been fighting with the same problem that Ken and Graham have for years as I
too use a great many nested subqueries. MS Access insists on rewriting my
subqueries by replacing my parentheses with the [...]. syntax, often causing the
query to fail.
I tried your suggestion and turned of the autocomplete, but it didn't seem to cure
the problem. If any of you can a way to stop MS Access from rewriting nested
subqueries, I would be very grateful.
Bill Ehrreich
Hollywood, FL
Sent via www.sfbsa.com
The South Florida Board Sailing Association This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: 'bonehead |
last post by:
Greetings,
I'd like to figure out some syntax for retrieving the data from a table
when I don't know all the of field names. What I do know are, the name
of the table, the names of the primary key fields, and the names of the
fields I want to use in the "where" clause of the SELECT statement. All
of the examples I've seen in my books only show how to do this by
hard-coding the names of each of the fields, i.e.,
$userid=$query_data;
|
by: Veverita |
last post by:
Hi there
I'm hoping that someone can help me with a question I have about
javascript syntax.
I got an html page that uploads an image and some text field to a
database.
What I'd like to do is modify the content of one of the textfields
prior to it being submitted to the database. Specifically, I need to
|
by: Nalpo |
last post by:
Hi,
i want to update several Textboxes by reading the value of another
Box. For that i use the following small script:
<form name="form1" method="POST">
<input type="text" name="xx" value ="1"
onBlur="document.form1.tex1.value=document.form1.xx.value"><br>
<input name="tex1" type="text" size=16 maxlength=80 value="1">
</form>
|
by: mollyf |
last post by:
I'm creating a query, which I want to use in code in my VB.NET app.
This query produces the correct results when executed in Access:
SELECT tblEncounters.EncounterBeginDT, Query11.RID, Query11.LName,
Query11.FName, Query11.MI FROM tblEncounters INNER JOIN Query11 ON
tblEncounters.RID = Query11.RID;
Query11 looks like this:
SELECT tblCases.RID, tblCases.LName, tblCases.FName, tblCases.MI,
|
by: Daniel Klein |
last post by:
In the MSDN documentation I see examples like:
Dim val As () = {"a", "b", "c"}
If I remove the brackets, ie, Dim val As String(), it seems to work
the same way.
What are the brackets for? And what is the difference when they are
used/omitted? I can find no mention of this anywhere in the MSDN docs.
| | |
by: spam |
last post by:
If I run the following query in Access 2002 then I get the expected
result:
SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;
I get the contents of both tables with no duplicates. If I add INSERT
INTO, then it doesn't work:
|
by: planetthoughtful |
last post by:
Hi All,
I'd like to place a subreport onto a report that draws its records from
a UNION query.
When I design the query and view the records all looks fine. However,
when I put the query as the subreport's data source and open it, Access
complains that Jet can't find the table, and then when I open up the
query itself to look at the SQL, it appears all mangled.
|
by: balakrishnan.dinesh |
last post by:
Hi frnds,
Im having a problem in searching out a particular substring in a
string.
Here the code:
---------------------
<html>
<script>
var tmpString=" ";
|
by: prodziedzic |
last post by:
I want use scanf to read from input everything till right square
bracket ']'.
It seems to be something like that:
scanf("%]) but that's not working.
Any ideas?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
