UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Querydef, Access 2013    
 
   
waitingroomz
post Aug 22 2019, 12:14 PM
Post#1



Posts: 87
Joined: 29-October 18



For whatever reason, when I return the QueryDef, whether I'm adding WHERE criteria or not, it is splitting the string causing it to be unusable.

I debug.printed and copied the result into a Query Design to see what was going on. See the attached photo.


Attached File(s)
Attached File  Query.jpg ( 353.47K )Number of downloads: 12
 
Go to the top of the page
 
GroverParkGeorge
post Aug 22 2019, 12:21 PM
Post#2


UA Admin
Posts: 35,680
Joined: 20-June 02
From: Newcastle, WA


I assume you are generating a SQL statement from the querydef's SQL using VBA?

I've seen that problem. Perhaps you can handle it by making sure the spaces in the SQL string are where they need to be to honor word boundaries. Access is splitting arbitrarily because it has no indication of those word boundaries.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
waitingroomz
post Aug 22 2019, 12:25 PM
Post#3



Posts: 87
Joined: 29-October 18



Yes, I am generating the SQL via VBA.

The spacing happens immediately after I pull the QDF.SQL into any string (strSQL, strTempSQL, strTemp) whether I add any type of variable information or not.

Can you elaborate on the word boundaries a little? Might lead me down the right path.
Go to the top of the page
 
GroverParkGeorge
post Aug 23 2019, 08:28 AM
Post#4


UA Admin
Posts: 35,680
Joined: 20-June 02
From: Newcastle, WA


Sorry about the pace of responding. Family matters.

At it's root, the issue is ambiguity, or rather the absence of it.

Humans can look at a string and figure out what was "meant" as opposed to what was "written" because we are familiar with the conventions involved. You've probably seen posts on social media with puzzles where they say "If you can read this you are a genius" and then something like "T41$ 1$ my (@$$w0rd" (I hope you can figure that out.) Computers don't have that level of insight--at least not yet.

That means, when you write a string in VBA, you have to be very sure that the string produced can be parsed by the SQL processor. Again, an example, "SELECT tblOne.*FROM tblOne" We easily parse that out as humans because we already know that tbleOne.* and FROM are actually two DIFFERENT words which were accidentally smooshed up together. Access can't be sure if it's two or one, and won't get it right.

That's what I meant by word boundaries, in my little example about the space missing between tblOne.* and FROM the missing word boundary is that space.

I think that's probably similar to what's going on in your much longer and more complex SQL, somewhere along the way, a space got dropped so that the SQL is sent to the SQL processor with SQL that can't be parsed.

Make sure that string is valid by using DeBug.Print to examine it and figure out how to alter your VBA to eliminate the syntax problem.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
waitingroomz
post Aug 23 2019, 08:57 AM
Post#5



Posts: 87
Joined: 29-October 18



Good morning sir,

Thanks for the response, hope all is well with the family.

I have debugged it and it is happenning at the exact same location, x amount of characters in.

I am now attempting to find a decent where condition builder on here to mitigate the issue.
Go to the top of the page
 
waitingroomz
post Aug 23 2019, 08:57 AM
Post#6



Posts: 87
Joined: 29-October 18



Good morning sir,

Thanks for the response, hope all is well with the family.

I have debugged it and it is happenning at the exact same location, x amount of characters in.

I am now attempting to find a decent where condition builder on here to mitigate the issue.
Go to the top of the page
 
GroverParkGeorge
post Aug 23 2019, 09:18 AM
Post#7


UA Admin
Posts: 35,680
Joined: 20-June 02
From: Newcastle, WA


Ah, so it's happening at the same number of characters each time. That's an important point I apparently overlooked earlier.

Let's revisit the VBA that generates the string. Can you share that?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 11:54 AM