SQL statement format for readability and making adjustments purposes

[Ronald Dodge]

Is there a way to prevent the SQL statement from putting everything in the
following manner:

SELECT tblHrsHist.fldstrEmpID, tblEmp.fldstrFirstName,
tblEmp.fldstrMiddleInit, tblEmp.fldstrLastName, tblEmp.fldstrSuffix,
tblEmp.fldstrPayCode, tblPayCode.fldbolPdLunch, tblHrsHist.fldstrWrkCode,
tblHrsHist.flddblLogStartTime, tblHrsHist.flddblActStartTime,
tblHrsHist.fldbolStartTimeAdj, tblHrsHist.fldstrUsrNamStartTimeAdj,
tblHrsHist.flddblLogEndTime, DateValue([flddblActEndTime]) AS [Actual End
Date], tblHrsHist.fldbolEndTimeAdj, tblHrsHist.fldstrUsrNamEndTimeAdj
FROM tblPayCode INNER JOIN (tblEmp INNER JOIN tblHrsHist ON
tblEmp.fldstrEmpID = tblHrsHist.fldstrEmpID) ON tblPayCode.fldstrPayCode =
tblEmp.fldstrPayCode WHERE DateValue([flddblActEndTime]) Between [Start
Date] And [End Date] OR [Start Date] Is Null OR [End Date] Is Null ORDER BY
tblHrsHist.fldstrEmpID, tblHrsHist.flddblLogStartTime;


When I prefer to be in the manner of :


SELECT
tblHrsHist.fldstrEmpID,
tblEmp.fldstrFirstName,
tblEmp.fldstrMiddleInit,
tblEmp.fldstrLastName,
tblEmp.fldstrSuffix,
tblEmp.fldstrPayCode,
tblPayCode.fldbolPdLunch,
tblHrsHist.fldstrWrkCode,
tblHrsHist.flddblLogStartTime,
tblHrsHist.flddblActStartTime,
tblHrsHist.fldbolStartTimeAdj,
tblHrsHist.fldstrUsrNamStartTimeAdj,
tblHrsHist.flddblLogEndTime,
DateValue([flddblActEndTime]) AS [Actual End Date],
tblHrsHist.fldbolEndTimeAdj,
tblHrsHist.fldstrUsrNamEndTimeAdj

FROM
tblPayCode INNER JOIN
(tblEmp INNER JOIN
tblHrsHist
ON tblEmp.fldstrEmpID = tblHrsHist.fldstrEmpID)
ON tblPayCode.fldstrPayCode = tblEmp.fldstrPayCode

WHERE
DateValue([flddblActEndTime]) Between [Start Date] And [End Date]
OR [Start Date] Is Null
OR [End Date] Is Null

ORDER BY
tblHrsHist.fldstrEmpID,
tblHrsHist.flddblLogStartTime;


Not only can I read this much easier, but I can also make adjustments much
quicker with this format than I can in the other format. As for the query
running, it runs the same under either format. The only purpose the other
format serves, it doesn't use up as much white space, thus reducing the
overall size of the query object. If you look at this latter format, it's
in many ways self documenting (allow the statements do the documenting for
you rather than having to type in your extra comments like you would in a
module, though SQL statements as far as I know doesn't allow for such type
comments).

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Allen Browne]

No. There is not really a way to get the queries in Access to format the
text in the way you describe.

One workaround is to create a table with a memo field to hold your SQL
statements. You can then format them however you like and cut'n'paste into
the query window.

Another workaround is to programmatically manipulate the text (e.g.
replacing a comma-and-space with a comma-and-space-and-CRLF) at the time you
need it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Is there a way to prevent the SQL statement from putting everything in the
following manner:

SELECT tblHrsHist.fldstrEmpID, tblEmp.fldstrFirstName,
tblEmp.fldstrMiddleInit, tblEmp.fldstrLastName, tblEmp.fldstrSuffix,
tblEmp.fldstrPayCode, tblPayCode.fldbolPdLunch, tblHrsHist.fldstrWrkCode,
tblHrsHist.flddblLogStartTime, tblHrsHist.flddblActStartTime,
tblHrsHist.fldbolStartTimeAdj, tblHrsHist.fldstrUsrNamStartTimeAdj,
tblHrsHist.flddblLogEndTime, DateValue([flddblActEndTime]) AS [Actual End
Date], tblHrsHist.fldbolEndTimeAdj, tblHrsHist.fldstrUsrNamEndTimeAdj
FROM tblPayCode INNER JOIN (tblEmp INNER JOIN tblHrsHist ON
tblEmp.fldstrEmpID = tblHrsHist.fldstrEmpID) ON tblPayCode.fldstrPayCode =
tblEmp.fldstrPayCode WHERE DateValue([flddblActEndTime]) Between [Start
Date] And [End Date] OR [Start Date] Is Null OR [End Date] Is Null ORDER
BY tblHrsHist.fldstrEmpID, tblHrsHist.flddblLogStartTime;


When I prefer to be in the manner of :


SELECT
tblHrsHist.fldstrEmpID,
tblEmp.fldstrFirstName,
tblEmp.fldstrMiddleInit,
tblEmp.fldstrLastName,
tblEmp.fldstrSuffix,
tblEmp.fldstrPayCode,
tblPayCode.fldbolPdLunch,
tblHrsHist.fldstrWrkCode,
tblHrsHist.flddblLogStartTime,
tblHrsHist.flddblActStartTime,
tblHrsHist.fldbolStartTimeAdj,
tblHrsHist.fldstrUsrNamStartTimeAdj,
tblHrsHist.flddblLogEndTime,
DateValue([flddblActEndTime]) AS [Actual End Date],
tblHrsHist.fldbolEndTimeAdj,
tblHrsHist.fldstrUsrNamEndTimeAdj

FROM
tblPayCode INNER JOIN
(tblEmp INNER JOIN
tblHrsHist
ON tblEmp.fldstrEmpID = tblHrsHist.fldstrEmpID)
ON tblPayCode.fldstrPayCode = tblEmp.fldstrPayCode

WHERE
DateValue([flddblActEndTime]) Between [Start Date] And [End Date]
OR [Start Date] Is Null
OR [End Date] Is Null

ORDER BY
tblHrsHist.fldstrEmpID,
tblHrsHist.flddblLogStartTime;


Not only can I read this much easier, but I can also make adjustments much
quicker with this format than I can in the other format. As for the query
running, it runs the same under either format. The only purpose the other
format serves, it doesn't use up as much white space, thus reducing the
overall size of the query object. If you look at this latter format, it's
in many ways self documenting (allow the statements do the documenting for
you rather than having to type in your extra comments like you would in a
module, though SQL statements as far as I know doesn't allow for such type
comments).

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Ronald Dodge]

Yeah, I thought about just using Notepad to store my SQL statements.

However, the bad thing about that, if there comes a time when someone else
would do something with the SQL statement, that would make my text file
obsolete, unless they were to make the adjustment in the text file and then
copy and paste it into the query object.

The other option would be to more or less combine your 2 work arounds into
1. First, create a table in the BE file with the query name and the memo
field for the SQL statement. Next, create a form for that specific table.
I personally have had in general, better luck using *UNBOUND* forms than
bound forms due to how bound forms works, even though it means spending more
time on the form coding aspect, but the end result is much closer to user
friendliness and not near as much of the side effects that I have had with
bound forms.

Once this is done, then I would like to have a SQL syntax checker type deal
in place prior to the SQL statement actually being updated into the query
object.

If you use a vbCrLf programmatically, you don't need a space after the comma
as the vbCrLf also serves as a white space.

Maybe I'm one such person that challenges things in the programming
environment as I have been told by others both inside and outside of the
company that I push the edges of the envelope out cause I'm constantly
pushing programs to do more than what they were intended to do. I do that
cause I want to get rid of as much of the manual work as one can possibly
get rid of, but yet, I don't want to lose the user friendliness of such
programs either, and I most certainly don't want such programs taking up
excessive amount of time processing either. I hate data entry after my 2
seasonal employment time period working at the IRS doing nothing but data
entry for a period of 60 to 70 hours per week not counting the 0.5 hours
lunch break working 6 to 7 nights per week on those 10 hour shifts with just
2 - 15 minute paid break time and the 1 half hour unpaid lunch break time.
Carpal Tunnel Syndrome was taking hold half way into the season the second
year, and I was like, get me out of the field, so programming was my ticket
out.

One such example:

When we were getting rid of our manufacturing database, I was like, don't
count on me doing all of that data entry by hand. I had 3 weeks tops to
have something in place, fully operational, and operators trained. At that
time, I did know Access fairly well from the user side, but knew very little
of it from the programming side. On the other hand, I knew Excel inside out
including the various limitations and barriers, thus was the only thing that
allowed me to get a production reporting system in place, fully debugged,
and having all operators and assistants trained all done within a 3 week
time period using userforms in Excel VBA.

Operators used the client side of the program, another computer was
dedicated to taking care of the server side of it, which to this day is
still using this program, and my reporting system pulling data from the
reporting system in addition to data from the main database (JDE) via
ShowCase Query. I also had to do a few work arounds in Excel VBA to be able
to run multiple reports all in the same go cause of how the ShowCase Query
Add-In for Excel works, which the tech reps of the ShowCase vendor program
didn't expect me to figure out how to get around the issue. I have since
that time typed up a 4 part series article on how to get Excel and ShowCase
Query talking to each other and how to automate a large part of the process.
The first 2 parts, pretty much any user can understand it, but then the last
2 parts, only well advanced users will want to read it due to the
technicalities involved.

Believe it or not, the Excel Production Reporting System has been in place
for the last 6+ years and it's still in full operation, even though when it
was built, it was only intended as an intermediate type solution. However,
I have had to spend only up to 10% of my total time monitoring and adjusting
the data on an as needed basis, as opposed to when I first took over the
position, I was spending 100% of my total time on it.

Some of the adjustments is a result of user errors, thus you will more or
less always have that, even when minimized by the various checks in place.
Some of the other adjustments has also been as a result of the limitations
of Excel, which is why I still consider the program as intermediate.
However, as one big plus, I have not lost one bit of data in the reporting
system as there's a total of 7 different backup programs all taking place,
though each of them are in place for different reasons. Believe it or not,
every one of these different backup programs has been used at least at some
point of time over the years, either for restoring data and/or bypassing
down systems.

IT department backs up the file server.

MS has the file saved to the local drive when it can't save to the server

My server side of the program has a record of all data retrieved and
reported into JDE for the last 7 calendar days.

My server side of the program has a backup program in place for when the
file server goes down and the reporting system needs to keep going with the
network system still fully in tact.

When my reports are ran, it has a backup mechanism built into it in case any
of the reporting files ends up crashing. This particular backup program was
built in the summer of 1999 within Excel 97 as a result of me hating Excel
97 due to the unstableness of the version and at that time, it took the IT
department 3 full weeks just to get around to restoring a whole department's
folder that was accidently deleted. I was like, what would happen if one of
my files crashed and got corrupted, as Excel 97 was notorious for that.
Excel 97 was so bad, I would have rather work with Lotus 1-2-3, v 2.3 than
to work with Excel 97, even after applying SR-2 onto Excel. Excel 2000 on
the other hand was much more stable and resolved a pretty good majority of
the issues that I had with Excel 97, SR2 in the various areas of the
program. While the files were primarily saved to the file server, the
backup copies has been saved to the local hard drive for the respective
year.

The client side of the program maintains the data for a period of 45
calendar days. Each work station has it's own client side file.

All data are reported into JDE, which then it's maintained in JDE for an
entirnity.

Within my machine center files that has the charts and summarized data, it
has one worksheet specifically for holding the data that those files pulls
in from the client side of my Production Reporting System, which then the
reporting system goes on and tabulates the data prior to saving and backing
up. However, I was not able to use the MS Query program within Excel due to
the fact that the query rely's on ADO, and within Excel, ADO is a known
memory leaker, thus caused my reporting system to crash too quick and too
easily, thus has to use VBA in a completely different manner to get the data
into the summarized file from the client side file. You may not believe all
of the different limitations that I have ran into including undocumented
limitations within Excel, such as the Defined names that says in the specs
that it's only limited to system resources, but in actuallity, it's limited
to a maximum of 65,536 defined names, and not only that, but it has a higher
chance of becoming unstable after exceeding 32,768 defined names. To test
this limitation, have 65537 range names put in via VBA (it may or may not
error out as it approaches this limit. If it does, it's the unstableness
factor coming into play. However, even if it doesn't, it may work until
after you have saved and closed out the workbook. The next time when you
attempt to open the workbook with more than 65,536 defined names in it, it
goes into repair mode and the workbook more or less at that point of time
becomes worthless).

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000