Blank Space after short lines - Convert Query SQL to VBA in Microsoft Access


	Back to Convert Query SQL to VBA Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Blank Space after short lines

John Michael Fowler

5 years ago

First, amazing videos! Thank you for all you do! I am using the VB to SQL conversion utility that you put together and its awesome. I am having an issue that I cannot seem to resolve, and it may very well be me. For short lines that do not meet the MAXLEN = 50 there is an issue with the conversion not adding a blank space at the end of the line. For example:
Original Statement:

SELECT tblActiveResearchProjects.*
FROM tblActiveResearchProjects
WHERE ((((tblActiveResearchProjects.ProjectStatus) Like "*" & forms!frmActiveResearchProjects!cmboxProjectStatusFilter & "*" Or (tblActiveResearchProjects.ProjectStatus) Is Null) And (tblActiveResearchProjects.ProjectStatus)<>{guid {6FA6B21E-BFE1-441C-B8C5-D95F145190DF}}) And ((tblActiveResearchProjects.Site) Like "*" & forms!frmActiveResearchProjects!cmboxSiteFilter & "*" Or (tblActiveResearchProjects.Site) Is Null));

Converted Statement:
"SELECT tblActiveResearchProjects.*" & _
"FROM tblActiveResearchProjects" & _
"WHERE ((((tblActiveResearchProjects.ProjectStatus) Like ""*"" & forms!frmActiveResearchProjects!cmboxProjectStatusFilter " & _
"& ""*"" Or (tblActiveResearchProjects.ProjectStatus) Is Null) And (tblActiveResearchProjects.ProjectStatus)<>{guid " & _
"{6FA6B21E-BFE1-441C-B8C5-D95F145190DF}}) And ((tblActiveResearchProjects.Site) Like ""*"" & forms!frmActiveResearchProjects!cmboxSiteFilter " & _
"& ""*"" Or (tblActiveResearchProjects.Site) Is Null));"

The error I am getting, when I try and set this record source, is a Syntax Error in FROM Clause. VBA wants a space to be entered in the FROM clause, so "FROM tblActiveResearchProjects" needs to be "FROM tblActiveResearchProjects "

The lines above the MAXLEN value are handling this perfectly, just not the shorter lines. Any thoughts on what I am doing wrong?

	Kevin Robertson @Reply 5 years ago
Try entering a space after the table name in the code editor.

	Kevin Robertson @Reply 5 years ago

	John Michael Fowler OP @Reply 5 years ago
Kevin, thank you for your reply. I know how to fix it manually, I was just wondering if there was a way to fix the conversion code so that it takes that into account.

	Scott Axton @Reply 5 years ago
John are you copying and pasting into the box? If so is the space there when before you hit convert? OR Are you using the Load Query List button?

John Michael Fowler OP @Reply

5 years ago

Scott,
Thanks for reaching out. I am using the QueryList to select each query, basically using the built in conversion method that Richard built. The code loads up the SQL statement from the queries in the database perfectly and also converts them without issue. The problem is that when access builds the SQL statement behind each query, the lines it breaks up automatically end with a blank space and then continue on the next line, the shorter lines do not have a blank space and I need them to. So, I was thinking that the most efficient way to do this is to add some code to the QueryList_Click sub that will add a blank space behind each line that does not already have one; however, I could be totally wrong.

	Scott Axton @Reply 5 years ago
John I can reproduce this using the program from the download from the video. Is this just this query or does it happen from others as well where the length is less than 50? If you open up your query directly in SQL view and copy it, then paste it in the converter, do you get the same results? I'm stumped. RICHARD CAN YOU LOOK AT THIS?

	Scott Axton @Reply 5 years ago
Sorry - I can't reproduce it.

	Richard Rost @Reply 5 years ago
I see the problem. I fixed it. It's not elegant, but it works. Find this in the code for MainMenuF: `S = Replace(S, vbNewLine, """ & _ " & vbNewLine & vbTab & """")` Replace it with this: `S = Replace(S, vbNewLine, " "" & _ " & vbNewLine & vbTab & """")` That's it. I'll upload a corrected database.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Convert Query SQL to VBA.

Next Unseen

New Feature: Comment Live View