Pilfered from several web pages:
=================================
A well known rich businessman's wife broke her hip. The businessman got the
best bone surgeon in town to do the operation. The operation consisted of
lining up the broken hip and putting in a screw to secure it. The operation
went fine, and the doctor sent the businessman a fee for his services of
$5000. The businessman was outraged at the cost, and sent the doctor a
letter demanding an itemized list of the costs. The doctor sent back a list
with two things:
1 screw $ 1
Knowing how to put it in $4999
$5000 total
The businessman never argued.
=================================
Dentist: I have to pull the aching tooth, but don't worry it will take just
five minutes.
Patient: And how much will it cost?
Dentist: It's $90.00.
Patient: $90.00 for just a few minutes work???
Dentist: I can extract it very slowly if you like.
=================================
I could have wrote a lot more code to make it seem more complex...
--
Duane Hookom
MS Access MVP
Dave F said:
The trick is knowing the code.
Duane Hookom said:
shucks... just a couple lines of code and basic SQL. The trick is knowing
where to look.
--
Duane Hookom
MS Access MVP
Dave F said:
Wow, it works!
Duane you're a genius.
Thanks.
:
You confuse Access when you name the function and the module the same.
Use a
naming convention so your module names might begin with "bas" or
"mod".
This
avoids issues of duplicate names.
--
Duane Hookom
MS Access MVP
They're both named GetSQL.
:
Did you save the module with a name different from the function
name?
--
Duane Hookom
MS Access MVP
Hi Duane--interesting suggestion.
However, when I run the query you suggest, I get an error:
Undefined function '[GetSQL]' in expression.
I've entered the function in a module as you suggest, and saved
it,
so
I'm
not sure why I would get that error?
Dave
:
Use the documenter in the Tools menu. Or create a small
user-defined
function in a general module:
Function GetSQL(pstrQuery As String) As String
GetSQL = CurrentDb.QueryDefs(pstrQuery).SQL
End Function
then create a query with SQL like:
SELECT msysObjects.Name, GetSQL([Name]) AS TheSQL
FROM msysObjects
WHERE msysObjects.Name Not Like "~*" AND msysObjects.Type=5;
--
Duane Hookom
MS Access MVP
So here's a new one....
A client's internal auditors is looking for a list of all
queries
in
a
db
and their SQL statements. The weird thing is that if I copy
the
SQL
statement from Access' SQL window to Excel, MSFT puts each
clause
on
a
separate row in Excel, which forces me to, for example, do
=concatenate(b2,b3,b4) for a three clause statement, and then
copy,
paste
special, values.
Any way to force Access (or Excel) to copy the SQL statement
into
a
single
cell?
