User defined function in SQL statement

[robert d via AccessMonster.com]

I'm getting gibberish returned in my listbox when applying my user-defined
fucntion on an SQL statement. I've never done this before, so I need help.
Here's the code:


'SELECT records from the temp table and order by Utility Type
strSQLUSAGELIST = "SELECT UTILITY_TYPE As Utility, UOM, SUM(SQLConvertUOM
(UTILITY_TYPE, UOM," & _
" EXISTING_USAGE)) As Existing, SUM(SQLConvertUOM(UTILITY_TYPE, UOM,
PROPOSED_USAGE)) As" & _ "
" Proposed FROM [TEMPTABLEA] GROUP BY UTILITY_TYPE, UOM ORDER BY
UTILITY_TYPE"

Me.Usage_Listbox.RowSource = strSQLUSAGELIST



**********************************************************************************************
Public Function SQLConvertUOM(UtilType As String, UOMPassed As String,
UsagePassed As Double) As Double
'This function gets the conversion factor for the passed usage to convert it
to
'native units.


Dim strSQLUSAGE As String, wsUSAGE As DAO.Workspace, dbUSAGE As DAO.Database,
rsUSAGE As DAO.Recordset



strSQLUSAGE = "SELECT CONVERSION _FACTOR FROM [UNITS CONVERSION TABLE] WHERE"
& _
" UTILITY_CODE = '" & UtilType & "' AND UNIT = '" & UOMPassed & "';"

Set wsUSAGE = DBEngine.Workspaces(0)
Set dbUSAGE = wsUSAGE.OpenDatabase(CurrentProject.FullName)
Set rsUSAGE = dbUSAGE.OpenRecordset(strSQLUSAGE, dbOpenDynaset, dbSeeChanges)

If rsUSAGE.RecordCount = 0 Then
rsUSAGE.Close
Exit Function
Else
SQLConvertUOM = UsagePassed * CDbl(rsUSAGE("CONVERSION_FACTOR"))
End If

rsUSAGE.Close

End Function


Thanks for any help.

 

[Allen Browne]

If you are getting gibberish, it suggests that JET is misunderstanding the
data type being returned, and then treating it as text.

To avoid the problem, typecast the results, as described in this article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

There also seems to be a spurious space in the SQL statement in your
function. Did you intend:
strSQLUSAGE = "SELECT CONVERSION_FACTOR FROM ...

The performance of this kind of query is going to be rather bad, as you are
calling a function that performs its own i/o operations *twice* for every
record in the query. That's a helava lot of i/o's and for some reason,
User-defined functions became even slower in JET 4. Alternatives:

a) Include the [UNITS CONVERSION TABLE] in the query. If you have some
records where UTILITY_TYPE is null, double-click the line joining the 2
tables in the upper pane of query design. Access gives you are dialog where
you can choose:
All records from TEMPTABLEA, and any matches in ...

b) Since you are grouping by UTILITY_TYPE, you could Sum() the field, and
apply the conversion factor after summing.

c) If the above is not possible, consider a subquery rather than a function
call. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Assuming you can join the [UNITS CONVERSION TABLE] table in your query, and
include the CONVERSION_FACTOR field in the GROUP BY clause, combining
suggestions (a) and (b) would get you something like this as the first
calculated field:
CDbl(Nz(Sum([TEMPTABLEA].[EXISTING_USAGE]) * [UNITS CONVERSION
TABLE].[CONVERSION_FACTOR]),0)) AS Existing,

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

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

I'm getting gibberish returned in my listbox when applying my user-defined
fucntion on an SQL statement. I've never done this before, so I need
help.
Here's the code:


'SELECT records from the temp table and order by Utility Type
strSQLUSAGELIST = "SELECT UTILITY_TYPE As Utility, UOM, SUM(SQLConvertUOM
(UTILITY_TYPE, UOM," & _
" EXISTING_USAGE)) As Existing, SUM(SQLConvertUOM(UTILITY_TYPE, UOM,
PROPOSED_USAGE)) As" & _ "
" Proposed FROM [TEMPTABLEA] GROUP BY UTILITY_TYPE, UOM ORDER BY
UTILITY_TYPE"

Me.Usage_Listbox.RowSource = strSQLUSAGELIST



**********************************************************************************************
Public Function SQLConvertUOM(UtilType As String, UOMPassed As String,
UsagePassed As Double) As Double
'This function gets the conversion factor for the passed usage to convert
it
to
'native units.


Dim strSQLUSAGE As String, wsUSAGE As DAO.Workspace, dbUSAGE As
DAO.Database,
rsUSAGE As DAO.Recordset



strSQLUSAGE = "SELECT CONVERSION _FACTOR FROM [UNITS CONVERSION TABLE]
WHERE"
& _
" UTILITY_CODE = '" & UtilType & "' AND UNIT = '" & UOMPassed & "';"

Set wsUSAGE = DBEngine.Workspaces(0)
Set dbUSAGE = wsUSAGE.OpenDatabase(CurrentProject.FullName)
Set rsUSAGE = dbUSAGE.OpenRecordset(strSQLUSAGE, dbOpenDynaset,
dbSeeChanges)

If rsUSAGE.RecordCount = 0 Then
rsUSAGE.Close
Exit Function
Else
SQLConvertUOM = UsagePassed * CDbl(rsUSAGE("CONVERSION_FACTOR"))
End If

rsUSAGE.Close

End Function


Thanks for any help.

 

[robert d via AccessMonster.com]

Thanks, Allen:

I'm taking your advice and trying to incorporate the second table in the SQL
statement. It's close to working.
Here's what I have:

SELECT A.UTILITY_TYPE, B.UNIT, CDbl(Sum(Nz(A.[EXISTING_USAGE],0) * B.
[CONVERSION_FACTOR])) AS Existing, CDbl(Sum(Nz(A.[PROPOSED_USAGE],0) * B.
[CONVERSION_FACTOR])) AS Proposed
FROM [TEMPTABLEA] AS A, [UNITS CONVERSION TABLE] AS B
WHERE B.CONVERSION_FACTOR IN (SELECT B.CONVERSION_FACTOR FROM [UNITS
CONVERSION TABLE] AS B
WHERE B.UTILITY_CODE = A.UTILITY_TYPE AND B.UNIT = A.UOM)
GROUP BY A.UTILITY_TYPE, B.UNIT;

Actually the creation of Existing and Proposed appears to be working, but I'm
only testing it with one record in TEMPTABLEA at this time. The problem I'm
having is that every Unit of measure (UOM) where the conversion factor is
equal to 0.001 (which is the correct value for the one record in TEMPTABLEA)
is being returned. So, I'm getting 3 records in the query results instead of
1. The other two records have B.UNIT equal to gallons for water and BTU for
natural gas which is clearly incorrect since this one record has a utility
type of E for electricity. I don't understand why this is happening as the
Subselect clearly states the criteria for both Utility Code and Unit in Table
B.

I also had some trouble with what you posted on how to construct the
calculated field for both Existing and Proposed so I had to move the Nz and
Sum around a bit before Access would accept it.

I'm still getting gibberish in my listbox though on the form. Even the
header fields don't come out correctly. We can get to that later once the
Query works.

Thanks.

If you are getting gibberish, it suggests that JET is misunderstanding the
data type being returned, and then treating it as text.

To avoid the problem, typecast the results, as described in this article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

There also seems to be a spurious space in the SQL statement in your
function. Did you intend:
strSQLUSAGE = "SELECT CONVERSION_FACTOR FROM ...

The performance of this kind of query is going to be rather bad, as you are
calling a function that performs its own i/o operations *twice* for every
record in the query. That's a helava lot of i/o's and for some reason,
User-defined functions became even slower in JET 4. Alternatives:

a) Include the [UNITS CONVERSION TABLE] in the query. If you have some
records where UTILITY_TYPE is null, double-click the line joining the 2
tables in the upper pane of query design. Access gives you are dialog where
you can choose:
All records from TEMPTABLEA, and any matches in ...

b) Since you are grouping by UTILITY_TYPE, you could Sum() the field, and
apply the conversion factor after summing.

c) If the above is not possible, consider a subquery rather than a function
call. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Assuming you can join the [UNITS CONVERSION TABLE] table in your query, and
include the CONVERSION_FACTOR field in the GROUP BY clause, combining
suggestions (a) and (b) would get you something like this as the first
calculated field:
CDbl(Nz(Sum([TEMPTABLEA].[EXISTING_USAGE]) * [UNITS CONVERSION
TABLE].[CONVERSION_FACTOR]),0)) AS Existing,

I'm getting gibberish returned in my listbox when applying my user-defined
fucntion on an SQL statement. I've never done this before, so I need

[quoted text clipped - 45 lines]

Thanks for any help.

 

[Vincent Johns]

Can you provide a record or two of example data, plus a description of
the results you'd like to see? (A copy of the "gibberish", whatever it
is, you probably don't need to include.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[robert d via AccessMonster.com]

Hi Vincent:

Here's the table data:

TEMPTABLEA
UTILITY_TYPE UOM EXISTING_USAGE PROPOSED_USAGE
E MWH 600
350

UNITS CONVERSION TABLE
UTILITY_CODE UNIT CONVERSION_FACTOR
E MWH 1.00
E KWH 0.001
G MBTU 1.00
G BTU 0.001
W KGAL 1.00
W GAL 0.001

For this one record in TEMPTABLEA , the correct record from UNITS CONVERSION
TABLE that should be returned is the first record only because UTILITY_TYPE =
E and UOM = MWH. The CONVERSION_FACTOR, then for the SQL calculation is
supposed to be 1. There is only one matching record in the 2nd table. But
I'm getting more records than that returned in the query results.

Can you provide a record or two of example data, plus a description of
the results you'd like to see? (A copy of the "gibberish", whatever it
is, you probably don't need to include.)

I'm getting gibberish returned in my listbox when applying my user-defined
fucntion on an SQL statement. I've never done this before, so I need help.

[quoted text clipped - 42 lines]

Thanks for any help.

 

[robert d via AccessMonster.com]

I think I have the correct SQL statement now. I should have been selecting B.
NATIVE_UOM instead of B.UNIT and I need to add a second subquery. Here is
the statement that appears to be working (still needs more testing).

SELECT A.UTILITY_TYPE, B.NATIVE_UOM, CDbl(Sum(Nz(A.[EXISTING_USAGE],0)*B.
[CONVERSION_FACTOR])) AS Existing, CDbl(Sum(Nz(A.[PROPOSED_USAGE],0)*B.
[CONVERSION_FACTOR])) AS Proposed
FROM [TEMPTABLEA] AS A, [UNITS CONVERSION TABLE] AS B
WHERE B.CONVERSION_FACTOR In (SELECT B.CONVERSION_FACTOR FROM [UNITS
CONVERSION TABLE] AS B WHERE B.UTILITY_CODE = A.UTILITY_TYPE AND B.UNIT = A.
UOM) AND B.UNIT In (SELECT B.UNIT FROM [UNITS CONVERSION TABLE] AS B WHERE B.
UTILITY_CODE = A.UTILITY_TYPE AND B.UNIT = A.UOM)
GROUP BY A.UTILITY_TYPE, B.SABER_UOM;

One other thing that's somewhat off-topic, but today it looks like I've been
having some kind of weird corruption going on. Access failing to save.
Startup code that I haven't touched in months, suddenly failing along with
other weird results. Finally, I got the dreaded corruption message on
opening the app that prevents ever getting in again. Fortunately, I
discoverd about 10 months ago that if I open my app (default file A2K but
development in Access 2002) in Access 2000, the app will open and the
corruption disappears.

Still this makes me awfully nervous about deploying my app. I've heard that
the .mde files are much more stable and not typically subject to this kind of
corruption. Also, I was coding all day and I do have a tendency to repair
the code while Access is in code execution mode.

Hi Vincent:

Here's the table data:

TEMPTABLEA
UTILITY_TYPE UOM EXISTING_USAGE PROPOSED_USAGE
E MWH 600
350

UNITS CONVERSION TABLE
UTILITY_CODE UNIT CONVERSION_FACTOR
E MWH 1.00
E KWH 0.001
G MBTU 1.00
G BTU 0.001
W KGAL 1.00
W GAL 0.001

For this one record in TEMPTABLEA , the correct record from UNITS CONVERSION
TABLE that should be returned is the first record only because UTILITY_TYPE =
E and UOM = MWH. The CONVERSION_FACTOR, then for the SQL calculation is
supposed to be 1. There is only one matching record in the 2nd table. But
I'm getting more records than that returned in the query results.

Can you provide a record or two of example data, plus a description of
the results you'd like to see? (A copy of the "gibberish", whatever it

[quoted text clipped - 8 lines]

 

[Allen Browne]

Well, I was expecting some kind of join between TEMPTABLEA and [UNITS
CONVERSION TABLE], such as:
FROM TEMPTABLEA INNER JOIN [UNITS CONVERSION TABLE]
ON TEMPTABLEA.UTILITY_TYPE = [UNITS CONVERSION TABLE].UTILITY_CODE

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

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

Thanks, Allen:

I'm taking your advice and trying to incorporate the second table in the
SQL
statement. It's close to working.
Here's what I have:

SELECT A.UTILITY_TYPE, B.UNIT, CDbl(Sum(Nz(A.[EXISTING_USAGE],0) * B.
[CONVERSION_FACTOR])) AS Existing, CDbl(Sum(Nz(A.[PROPOSED_USAGE],0) * B.
[CONVERSION_FACTOR])) AS Proposed
FROM [TEMPTABLEA] AS A, [UNITS CONVERSION TABLE] AS B
WHERE B.CONVERSION_FACTOR IN (SELECT B.CONVERSION_FACTOR FROM [UNITS
CONVERSION TABLE] AS B
WHERE B.UTILITY_CODE = A.UTILITY_TYPE AND B.UNIT = A.UOM)
GROUP BY A.UTILITY_TYPE, B.UNIT;

Actually the creation of Existing and Proposed appears to be working, but
I'm
only testing it with one record in TEMPTABLEA at this time. The problem
I'm
having is that every Unit of measure (UOM) where the conversion factor is
equal to 0.001 (which is the correct value for the one record in
TEMPTABLEA)
is being returned. So, I'm getting 3 records in the query results instead
of
1. The other two records have B.UNIT equal to gallons for water and BTU
for
natural gas which is clearly incorrect since this one record has a utility
type of E for electricity. I don't understand why this is happening as
the
Subselect clearly states the criteria for both Utility Code and Unit in
Table
B.

I also had some trouble with what you posted on how to construct the
calculated field for both Existing and Proposed so I had to move the Nz
and
Sum around a bit before Access would accept it.

I'm still getting gibberish in my listbox though on the form. Even the
header fields don't come out correctly. We can get to that later once the
Query works.

Thanks.

If you are getting gibberish, it suggests that JET is misunderstanding the
data type being returned, and then treating it as text.

To avoid the problem, typecast the results, as described in this article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

There also seems to be a spurious space in the SQL statement in your
function. Did you intend:
strSQLUSAGE = "SELECT CONVERSION_FACTOR FROM ...

The performance of this kind of query is going to be rather bad, as you
are
calling a function that performs its own i/o operations *twice* for every
record in the query. That's a helava lot of i/o's and for some reason,
User-defined functions became even slower in JET 4. Alternatives:

a) Include the [UNITS CONVERSION TABLE] in the query. If you have some
records where UTILITY_TYPE is null, double-click the line joining the 2
tables in the upper pane of query design. Access gives you are dialog
where
you can choose:
All records from TEMPTABLEA, and any matches in ...

b) Since you are grouping by UTILITY_TYPE, you could Sum() the field, and
apply the conversion factor after summing.

c) If the above is not possible, consider a subquery rather than a
function
call. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Assuming you can join the [UNITS CONVERSION TABLE] table in your query,
and
include the CONVERSION_FACTOR field in the GROUP BY clause, combining
suggestions (a) and (b) would get you something like this as the first
calculated field:
CDbl(Nz(Sum([TEMPTABLEA].[EXISTING_USAGE]) * [UNITS CONVERSION
TABLE].[CONVERSION_FACTOR]),0)) AS Existing,

I'm getting gibberish returned in my listbox when applying my
user-defined
fucntion on an SQL statement. I've never done this before, so I need

[quoted text clipped - 45 lines]

Thanks for any help.

 

[Vincent Johns]

Robert,

I think you don't need your function at all (but I've included it
here for comparison purposes). Your function, as I used it, is shown
here...

Option Compare Database
Option Explicit

'SELECT records from the temp table and order by Utility Type
'strSQLUSAGELIST = "SELECT UTILITY_TYPE As Utility, " _
& "UOM, " _
& "SUM(SQLConvertUOM(UTILITY_TYPE, UOM, " _
& "EXISTING_USAGE)) As Existing, " _
& "SUM(SQLConvertUOM(UTILITY_TYPE, UOM, " _
& "PROPOSED_USAGE)) As Proposed " _
& "FROM [TEMPTABLEA] " _
& "GROUP BY UTILITY_TYPE, UOM " _
& "ORDER BY UTILITY_TYPE;"

'****************************************
'This function gets the conversion factor
'for the passed usage to convert it to
'native units.
'
Public Function SQLConvertUOM( _
UtilType As String, _
UOMPassed As String, _
UsagePassed As Double) _
As Double

Dim strSQLUSAGE As String
Dim wsUSAGE As DAO.Workspace
Dim dbUSAGE As DAO.Database
Dim rsUSAGE As DAO.Recordset

strSQLUSAGE = "SELECT CONVERSION_FACTOR " _
& "FROM [UNITS CONVERSION TABLE] " _
& "WHERE" _
& " UTILITY_CODE = '" & UtilType _
& "' AND UNIT = '" & UOMPassed & "';"

Set wsUSAGE = DBEngine.Workspaces(0)

Set dbUSAGE = wsUSAGE.OpenDatabase( _
CurrentProject.FullName)

Set rsUSAGE = dbUSAGE.OpenRecordset( _
strSQLUSAGE, _
dbOpenDynaset, _
dbSeeChanges)

If rsUSAGE.RecordCount = 0 Then

rsUSAGE.Close
Exit Function

Else

SQLConvertUOM = UsagePassed _
* CDbl(rsUSAGE("CONVERSION_FACTOR"))

End If 'rsUSAGE.RecordCount...

rsUSAGE.Close

End Function 'SQLConvertUOM


.... and I call it (and then ignore the result) in the [Q_UsageList]
Query shown below.

I added some stuff to your [TEMPTABLEA] Table, since a single record
doesn't give the Sum() function much exercise. Its name caused me to
yield to temptation and rename its fields to match the names of the
matching fields in [UNITS CONVERSION TABLE].

I originally added Autonumber fields to both Tables to serve as primary
keys, then decided this message would be easier to understand without
fiddling with that -- but I normally would do that in my own database,
and would define a Lookup property on that value when used as a foreign
key. In fact, what I did here was to declare [UTILITY_CODE] and [UNIT]
to jointly form the primary key of the [UNITS CONVERSION TABLE] Table.
(In [TEMPTABLEA], the same-named fields form a foreign key linking to
[UNITS CONVERSION TABLE].)

[TEMPTABLEA] Table Datasheet View:

UTILITY_CODE UNIT EXISTING_USAGE PROPOSED_USAGE
------------ ---- -------------- --------------
E MWH 600 350
E KWH 1500 1500
G MBTU 300 200
E KWH 1000 350

Although I was suspicious of the value for "G BTU", thinking it to be
inaccurate by about 3 orders of magnitude, I left it alone here -- it
doesn't materially affect the operation of the database, but I suggest
you check it.

[UNITS CONVERSION TABLE] Table Datasheet View:
UTILITY_CODE UNIT CONVERSION_FACTOR
------------ ---- -----------------
E KWH 0.001
E MWH 1
G BTU 0.001
G MBTU 1
W GAL 0.001
W KGAL 1

The following Query is inspired by the string constant strSQLUSAGELIST
from your Module, which I figured could just as easily be a named Query,
saved it as such, and then modified the heck out of it.

Fields [ExistingF] and [ProposedF] involve calls to SQLConvertUOM(}, and
fields [ExistingT] and [ProposedT] instead refer directly to [UNITS
CONVERSION TABLE]![CONVERSION_FACTOR]. [ExistingF] and [ProposedF] are
included just for comparison purposes, so that by running tests on more
extensive data you can gain a warm fuzzy feeling that the vaues returned
by the Table are just as useful as those returned by the function. (Or
you can peruse the design to convince yourself that it makes sense. Or
both.)

[Q_UsageList] SQL:

SELECT [TEMPTABLEA]![UTILITY_CODE] AS Utility,
TEMPTABLEA.UNIT,
Sum(SQLConvertUOM([TEMPTABLEA]![UTILITY_CODE],
[TEMPTABLEA]![UNIT],[EXISTING_USAGE]))
AS ExistingF,
Sum([TEMPTABLEA]![EXISTING_USAGE]
*[UNITS CONVERSION TABLE]![CONVERSION_FACTOR])
AS ExistingT,
Sum(SQLConvertUOM([TEMPTABLEA]![UTILITY_CODE],
[TEMPTABLEA]![UNIT],[PROPOSED_USAGE]))
AS ProposedF,
Sum([TEMPTABLEA]![PROPOSED_USAGE]
*[UNITS CONVERSION TABLE]![CONVERSION_FACTOR])
AS ProposedT
FROM TEMPTABLEA INNER JOIN [UNITS CONVERSION TABLE]
ON (TEMPTABLEA.UTILITY_CODE
= [UNITS CONVERSION TABLE].UTILITY_CODE)
AND (TEMPTABLEA.UNIT = [UNITS CONVERSION TABLE].UNIT)
GROUP BY [TEMPTABLEA]![UTILITY_CODE], TEMPTABLEA.UNIT
ORDER BY [TEMPTABLEA]![UTILITY_CODE];

For my sample data, this produces sums of records sorted by (converted)
unit of measure, preparatory to being summed in another Query. Despite
appearances, all the units in [ExistingT], &c., are expressed in the
standard unit for the given utility code.

[Q_UsageList] Query Datasheet View:

Utility UNIT ExistingF ExistingT ProposedF ProposedT
------- ---- --------- --------- --------- ---------
E KWH 2.50 2.50 1.85 1.85
E MWH 600.00 600.00 350.00 350.00
G MBTU 300.00 300.00 200.00 200.00

Now I define a new Query to express the sums of these values.

[Q_UsageListSum] SQL:

SELECT Q_UsageList.Utility,
Sum([Q_UsageList]![ExistingT]) AS SumOfExisting,
Sum([Q_UsageList]![ProposedT]) AS SumOfProposed,
[UNITS CONVERSION TABLE].UNIT
FROM [UNITS CONVERSION TABLE]
INNER JOIN Q_UsageList
ON [UNITS CONVERSION TABLE].UTILITY_CODE
= Q_UsageList.Utility
WHERE ((([UNITS CONVERSION TABLE].CONVERSION_FACTOR)=1))
GROUP BY Q_UsageList.Utility,
[UNITS CONVERSION TABLE].UNIT;

This just adds up the converted values and displays the unit in which
the totals are expressed, and it makes no use of the SQLConvertUOM()
function.

[Q_UsageListSum] Query Datasheet View:

Utility SumOfExisting SumOfProposed UNIT
------- ------------- ------------- ----
E 602.50 351.85 MWH
G 300.00 200.00 MBTU

Although I haven't looked too closely at your revised SQL statement, it
does appear to be malformed, as B.SABER_UOM seems to be undefined. I
assume it's a typo, but wasn't sure what you meant there.

Concerning losing your database, I'd be nervous, too, but I assume
you're making frequent backups. If, after deploying your database, you
learn that some user couldn't open his copy, you can recover from your
backups and send a new copy to the user. Users should be warned to back
up what they do, too. I sometimes include in my databases code to dump
the contents of Tables to text files (which are not normally subject to
subtle corruption -- if they're bad, it's usually immediately obvious),
and then ZIP the text to save space.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

I think I have the correct SQL statement now. I should have been selecting B.
NATIVE_UOM instead of B.UNIT and I need to add a second subquery. Here is
the statement that appears to be working (still needs more testing).

SELECT A.UTILITY_TYPE, B.NATIVE_UOM, CDbl(Sum(Nz(A.[EXISTING_USAGE],0)*B.
[CONVERSION_FACTOR])) AS Existing, CDbl(Sum(Nz(A.[PROPOSED_USAGE],0)*B.
[CONVERSION_FACTOR])) AS Proposed
FROM [TEMPTABLEA] AS A, [UNITS CONVERSION TABLE] AS B
WHERE B.CONVERSION_FACTOR In (SELECT B.CONVERSION_FACTOR FROM [UNITS
CONVERSION TABLE] AS B WHERE B.UTILITY_CODE = A.UTILITY_TYPE AND B.UNIT = A.
UOM) AND B.UNIT In (SELECT B.UNIT FROM [UNITS CONVERSION TABLE] AS B WHERE B.
UTILITY_CODE = A.UTILITY_TYPE AND B.UNIT = A.UOM)
GROUP BY A.UTILITY_TYPE, B.SABER_UOM;

One other thing that's somewhat off-topic, but today it looks like I've been
having some kind of weird corruption going on. Access failing to save.
Startup code that I haven't touched in months, suddenly failing along with
other weird results. Finally, I got the dreaded corruption message on
opening the app that prevents ever getting in again. Fortunately, I
discoverd about 10 months ago that if I open my app (default file A2K but
development in Access 2002) in Access 2000, the app will open and the
corruption disappears.

Still this makes me awfully nervous about deploying my app. I've heard that
the .mde files are much more stable and not typically subject to this kind of
corruption. Also, I was coding all day and I do have a tendency to repair
the code while Access is in code execution mode.

Hi Vincent:

Here's the table data:

TEMPTABLEA
UTILITY_TYPE UOM EXISTING_USAGE PROPOSED_USAGE
E MWH 600
350

UNITS CONVERSION TABLE
UTILITY_CODE UNIT CONVERSION_FACTOR
E MWH 1.00
E KWH 0.001
G MBTU 1.00
G BTU 0.001
W KGAL 1.00
W GAL 0.001

For this one record in TEMPTABLEA , the correct record from UNITS CONVERSION
TABLE that should be returned is the first record only because UTILITY_TYPE =
E and UOM = MWH. The CONVERSION_FACTOR, then for the SQL calculation is
supposed to be 1. There is only one matching record in the 2nd table. But
I'm getting more records than that returned in the query results.

Can you provide a record or two of example data, plus a description of
the results you'd like to see? (A copy of the "gibberish", whatever it

[quoted text clipped - 8 lines]

Thanks for any help.

 

[robert d via AccessMonster.com]

Vincent:

Thank you for the considerable work you've put into this issue. Yes, the
SABER_UOM is a typo and should be NATIVE_UOM. The SQL statement I have seems
to be working fine, without the need for calling the custom function. I
generally find it preferable to not use custom functions.

Many months ago I added a module to my app to allow for the easy creation of
backups. When the form comes up, it automatically shows the folder where the
backup will be stored and creates a name for the backup that includes the
word "Backup" and the date and time. One click and the backup is created and
the Compact on close parameter set to True. In retrospect this has saved me
many times since if it required more effort to create the backup, I probably
would do it less often.

The really interesting thing is how opening my app in Access 2000 has always
solved the corruption problem, without fail. This especially has been a
godsend, but I have no idea why this is the case.

Again, thank you very much.

Robert,

I think you don't need your function at all (but I've included it
here for comparison purposes). Your function, as I used it, is shown
here...

Option Compare Database
Option Explicit

'SELECT records from the temp table and order by Utility Type
'strSQLUSAGELIST = "SELECT UTILITY_TYPE As Utility, " _
& "UOM, " _
& "SUM(SQLConvertUOM(UTILITY_TYPE, UOM, " _
& "EXISTING_USAGE)) As Existing, " _
& "SUM(SQLConvertUOM(UTILITY_TYPE, UOM, " _
& "PROPOSED_USAGE)) As Proposed " _
& "FROM [TEMPTABLEA] " _
& "GROUP BY UTILITY_TYPE, UOM " _
& "ORDER BY UTILITY_TYPE;"

'****************************************
'This function gets the conversion factor
'for the passed usage to convert it to
'native units.
'
Public Function SQLConvertUOM( _
UtilType As String, _
UOMPassed As String, _
UsagePassed As Double) _
As Double

Dim strSQLUSAGE As String
Dim wsUSAGE As DAO.Workspace
Dim dbUSAGE As DAO.Database
Dim rsUSAGE As DAO.Recordset

strSQLUSAGE = "SELECT CONVERSION_FACTOR " _
& "FROM [UNITS CONVERSION TABLE] " _
& "WHERE" _
& " UTILITY_CODE = '" & UtilType _
& "' AND UNIT = '" & UOMPassed & "';"

Set wsUSAGE = DBEngine.Workspaces(0)

Set dbUSAGE = wsUSAGE.OpenDatabase( _
CurrentProject.FullName)

Set rsUSAGE = dbUSAGE.OpenRecordset( _
strSQLUSAGE, _
dbOpenDynaset, _
dbSeeChanges)

If rsUSAGE.RecordCount = 0 Then

rsUSAGE.Close
Exit Function

Else

SQLConvertUOM = UsagePassed _
* CDbl(rsUSAGE("CONVERSION_FACTOR"))

End If 'rsUSAGE.RecordCount...

rsUSAGE.Close

End Function 'SQLConvertUOM

... and I call it (and then ignore the result) in the [Q_UsageList]
Query shown below.

I added some stuff to your [TEMPTABLEA] Table, since a single record
doesn't give the Sum() function much exercise. Its name caused me to
yield to temptation and rename its fields to match the names of the
matching fields in [UNITS CONVERSION TABLE].

I originally added Autonumber fields to both Tables to serve as primary
keys, then decided this message would be easier to understand without
fiddling with that -- but I normally would do that in my own database,
and would define a Lookup property on that value when used as a foreign
key. In fact, what I did here was to declare [UTILITY_CODE] and [UNIT]
to jointly form the primary key of the [UNITS CONVERSION TABLE] Table.
(In [TEMPTABLEA], the same-named fields form a foreign key linking to
[UNITS CONVERSION TABLE].)

[TEMPTABLEA] Table Datasheet View:

UTILITY_CODE UNIT EXISTING_USAGE PROPOSED_USAGE
------------ ---- -------------- --------------
E MWH 600 350
E KWH 1500 1500
G MBTU 300 200
E KWH 1000 350

Although I was suspicious of the value for "G BTU", thinking it to be
inaccurate by about 3 orders of magnitude, I left it alone here -- it
doesn't materially affect the operation of the database, but I suggest
you check it.

[UNITS CONVERSION TABLE] Table Datasheet View:
UTILITY_CODE UNIT CONVERSION_FACTOR
------------ ---- -----------------
E KWH 0.001
E MWH 1
G BTU 0.001
G MBTU 1
W GAL 0.001
W KGAL 1

The following Query is inspired by the string constant strSQLUSAGELIST
from your Module, which I figured could just as easily be a named Query,
saved it as such, and then modified the heck out of it.

Fields [ExistingF] and [ProposedF] involve calls to SQLConvertUOM(}, and
fields [ExistingT] and [ProposedT] instead refer directly to [UNITS
CONVERSION TABLE]![CONVERSION_FACTOR]. [ExistingF] and [ProposedF] are
included just for comparison purposes, so that by running tests on more
extensive data you can gain a warm fuzzy feeling that the vaues returned
by the Table are just as useful as those returned by the function. (Or
you can peruse the design to convince yourself that it makes sense. Or
both.)

[Q_UsageList] SQL:

SELECT [TEMPTABLEA]![UTILITY_CODE] AS Utility,
TEMPTABLEA.UNIT,
Sum(SQLConvertUOM([TEMPTABLEA]![UTILITY_CODE],
[TEMPTABLEA]![UNIT],[EXISTING_USAGE]))
AS ExistingF,
Sum([TEMPTABLEA]![EXISTING_USAGE]
*[UNITS CONVERSION TABLE]![CONVERSION_FACTOR])
AS ExistingT,
Sum(SQLConvertUOM([TEMPTABLEA]![UTILITY_CODE],
[TEMPTABLEA]![UNIT],[PROPOSED_USAGE]))
AS ProposedF,
Sum([TEMPTABLEA]![PROPOSED_USAGE]
*[UNITS CONVERSION TABLE]![CONVERSION_FACTOR])
AS ProposedT
FROM TEMPTABLEA INNER JOIN [UNITS CONVERSION TABLE]
ON (TEMPTABLEA.UTILITY_CODE
= [UNITS CONVERSION TABLE].UTILITY_CODE)
AND (TEMPTABLEA.UNIT = [UNITS CONVERSION TABLE].UNIT)
GROUP BY [TEMPTABLEA]![UTILITY_CODE], TEMPTABLEA.UNIT
ORDER BY [TEMPTABLEA]![UTILITY_CODE];

For my sample data, this produces sums of records sorted by (converted)
unit of measure, preparatory to being summed in another Query. Despite
appearances, all the units in [ExistingT], &c., are expressed in the
standard unit for the given utility code.

[Q_UsageList] Query Datasheet View:

Utility UNIT ExistingF ExistingT ProposedF ProposedT
------- ---- --------- --------- --------- ---------
E KWH 2.50 2.50 1.85 1.85
E MWH 600.00 600.00 350.00 350.00
G MBTU 300.00 300.00 200.00 200.00

Now I define a new Query to express the sums of these values.

[Q_UsageListSum] SQL:

SELECT Q_UsageList.Utility,
Sum([Q_UsageList]![ExistingT]) AS SumOfExisting,
Sum([Q_UsageList]![ProposedT]) AS SumOfProposed,
[UNITS CONVERSION TABLE].UNIT
FROM [UNITS CONVERSION TABLE]
INNER JOIN Q_UsageList
ON [UNITS CONVERSION TABLE].UTILITY_CODE
= Q_UsageList.Utility
WHERE ((([UNITS CONVERSION TABLE].CONVERSION_FACTOR)=1))
GROUP BY Q_UsageList.Utility,
[UNITS CONVERSION TABLE].UNIT;

This just adds up the converted values and displays the unit in which
the totals are expressed, and it makes no use of the SQLConvertUOM()
function.

[Q_UsageListSum] Query Datasheet View:

Utility SumOfExisting SumOfProposed UNIT
------- ------------- ------------- ----
E 602.50 351.85 MWH
G 300.00 200.00 MBTU

Although I haven't looked too closely at your revised SQL statement, it
does appear to be malformed, as B.SABER_UOM seems to be undefined. I
assume it's a typo, but wasn't sure what you meant there.

Concerning losing your database, I'd be nervous, too, but I assume
you're making frequent backups. If, after deploying your database, you
learn that some user couldn't open his copy, you can recover from your
backups and send a new copy to the user. Users should be warned to back
up what they do, too. I sometimes include in my databases code to dump
the contents of Tables to text files (which are not normally subject to
subtle corruption -- if they're bad, it's usually immediately obvious),
and then ZIP the text to save space.

I think I have the correct SQL statement now. I should have been selecting B.
NATIVE_UOM instead of B.UNIT and I need to add a second subquery. Here is

[quoted text clipped - 54 lines]

 

[robert d via AccessMonster.com]

Oh, by the way. The gibberish in the listbox was a result of the fact that
the listbox originally had a RowSourceType = "Value List". I forgot to
change it when I decided to try to fill the listbox using SQL instead. Once
changed, the listbox filled as expected.

Vincent:

Thank you for the considerable work you've put into this issue. Yes, the
SABER_UOM is a typo and should be NATIVE_UOM. The SQL statement I have seems
to be working fine, without the need for calling the custom function. I
generally find it preferable to not use custom functions.

Many months ago I added a module to my app to allow for the easy creation of
backups. When the form comes up, it automatically shows the folder where the
backup will be stored and creates a name for the backup that includes the
word "Backup" and the date and time. One click and the backup is created and
the Compact on close parameter set to True. In retrospect this has saved me
many times since if it required more effort to create the backup, I probably
would do it less often.

The really interesting thing is how opening my app in Access 2000 has always
solved the corruption problem, without fail. This especially has been a
godsend, but I have no idea why this is the case.

Again, thank you very much.

Robert,

[quoted text clipped - 202 lines]

 

[Vincent Johns]

Oh, by the way. The gibberish in the listbox was a result of the fact that
the listbox originally had a RowSourceType = "Value List". I forgot to
change it when I decided to try to fill the listbox using SQL instead. Once
changed, the listbox filled as expected.

Then that should give you no more trouble.

It's certainly preferable if there's another way to do the same work.
Access provides lots of helpful ready-made tools. But sometimes a
custom function is necessary, just not in the present case.

Outside of Access, you could perhaps do this via a procedure (*.BAT
file, for example) that could automatically add the compacted database
file to a ZIP file, scheduled to run maybe once a day, and not require
any attention at all, not even a mouse click. But that would be more
suitable for when you're not modifying the database. When I'm making
design changes, I usually try to make a new backup as soon as I've
changed enough stuff that I would really rather not have to re-do it.


I suppose only Microsoft knows for sure. Maybe the next version of
Access will fix that.

Again, thank you very much.

Robert,

[quoted text clipped - 202 lines]

Thanks for any help.

I'm still curious. Was I right about "G BTU"?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[robert d via AccessMonster.com]

Yes, you are correct. G is the code for natural gas and BTU is the unit
British Thermal Unit. The application native unit of measure for G is MBTU
(mega BTU), so the conversion factor should be .000001 or as you noted 3
orders of magnitude different.

Thanks for noticing that.

Oh, by the way. The gibberish in the listbox was a result of the fact that
the listbox originally had a RowSourceType = "Value List". I forgot to
change it when I decided to try to fill the listbox using SQL instead. Once
changed, the listbox filled as expected.

Then that should give you no more trouble.

It's certainly preferable if there's another way to do the same work.
Access provides lots of helpful ready-made tools. But sometimes a
custom function is necessary, just not in the present case.

Many months ago I added a module to my app to allow for the easy creation of
backups. When the form comes up, it automatically shows the folder where the [quoted text clipped - 3 lines]
many times since if it required more effort to create the backup, I probably
would do it less often.

Outside of Access, you could perhaps do this via a procedure (*.BAT
file, for example) that could automatically add the compacted database
file to a ZIP file, scheduled to run maybe once a day, and not require
any attention at all, not even a mouse click. But that would be more
suitable for when you're not modifying the database. When I'm making
design changes, I usually try to make a new backup as soon as I've
changed enough stuff that I would really rather not have to re-do it.

I suppose only Microsoft knows for sure. Maybe the next version of
Access will fix that.

Again, thank you very much.
[quoted text clipped - 3 lines]

Thanks for any help.

I'm still curious. Was I right about "G BTU"?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.