Calling query which uses alias results in different value Version

[akinter]

Hello, and thank you for your help. I have a query that I call from a
VBA function which results in a different value than when viewed in the
datasheet window. The query does a calculation, and stores an
intermediate value using an alias, then uses that value to
format(round()) so that a pdf file can accept it. If I don't use the
intermediate alias (I use it as this value is also used for other
calculations) it works fine. Also, when I view the value either in
datasheet view, or from within a form - it's correct. But after coming
out of the function - it is incorrect.

The code is below.

Function MergeAndPrint4(tableName As String, pdfPath As String,
prntrName As String)

Dim ofArray As Variant
Dim base As String
Dim xfdfFileName As String
Dim Rec As Integer
Dim hFile As Long
Dim Vacuum_Gyro_ID As Long

Dim rst As New ADODB.Recordset
Set objShell = CreateObject("WScript.Shell")

If Dir(pdfPath) = "" Then
pdfPath = CurrentProject.Path & "\" & pdfPath
If Dir(pdfPath) = "" Then
MsgBox "ERROR - File Not Found: '" & pdfPath & "'"
Exit Function
End If
End If

' get the Vacuum_Gyro_ID from the frm1903B_VacuumGyro
Vacuum_Gyro_ID = [Forms]![frm1903B_VacuumGyro]![Vaccum_Gyro_ID]

DoCmd.Close

ofArray = Split(pdfPath, ".", 2)
base = ofArray(0)
Debug.Print base

SQL = Trim(SQLGet_MEI("1903B_VacuumGyroqry"))
SQL = Left(SQL, Len(SQL) - 3) & " WHERE
(tblJMF_VacuumGyro.Vacuum_Gyro_ID=" & Vacuum_Gyro_ID & ");"
Debug.Print SQL

rst.Open SQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.MoveFirst
firstJob = True

MsgBox "rst!con19 is " & rst!con19 & ""
Rec = 1

rst.Close
End Function

Thank you again for your help.
Lex

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What's the SQL statement look like? Is there a column named "con19"?

If you know the column position you can use rst(x), where x is the
column position-1. E.g.:

SELECT colA, colB, colC ...

rst(2) would return the value of colC. The rst() index is zero-based.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAYESoechKqOuFEgEQJaxACfbb/6cA3ko8K3Lxh+qQK7ilV9NZIAoKan
rot570zUljmUfT81Zi8zJzuz
=LNrJ
-----END PGP SIGNATURE-----
ak*****@umich.edu wrote:

Hello, and thank you for your help. I have a query that I call from a
VBA function which results in a different value than when viewed in the
datasheet window. The query does a calculation, and stores an
intermediate value using an alias, then uses that value to
format(round()) so that a pdf file can accept it. If I don't use the
intermediate alias (I use it as this value is also used for other
calculations) it works fine. Also, when I view the value either in
datasheet view, or from within a form - it's correct. But after coming
out of the function - it is incorrect.

The code is below.

Function MergeAndPrint4(tableName As String, pdfPath As String,
prntrName As String)

Dim ofArray As Variant
Dim base As String
Dim xfdfFileName As String
Dim Rec As Integer
Dim hFile As Long
Dim Vacuum_Gyro_ID As Long

Dim rst As New ADODB.Recordset
Set objShell = CreateObject("WScript.Shell")

If Dir(pdfPath) = "" Then
pdfPath = CurrentProject.Path & "\" & pdfPath
If Dir(pdfPath) = "" Then
MsgBox "ERROR - File Not Found: '" & pdfPath & "'"
Exit Function
End If
End If

' get the Vacuum_Gyro_ID from the frm1903B_VacuumGyro
Vacuum_Gyro_ID = [Forms]![frm1903B_VacuumGyro]![Vaccum_Gyro_ID]

DoCmd.Close

ofArray = Split(pdfPath, ".", 2)
base = ofArray(0)
Debug.Print base

SQL = Trim(SQLGet_MEI("1903B_VacuumGyroqry"))
SQL = Left(SQL, Len(SQL) - 3) & " WHERE
(tblJMF_VacuumGyro.Vacuum_Gyro_ID=" & Vacuum_Gyro_ID & ");"
Debug.Print SQL

rst.Open SQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.MoveFirst
firstJob = True

MsgBox "rst!con19 is " & rst!con19 & ""
Rec = 1

rst.Close
End Function