ADOBD recordset on Informix database - values always divided by 10

[Guest]

Disclaimer : I'm a programming idiot, who is programming via patching
together examples I find from other people to accomplish what I need.

I'm hoping this is an obvious error that I'm missing just because I'm
overwhelmed. I am extracting data from an Informix database, via Access.
It's combined with data from an Oracle database. I'm doing this via Access
just because it's what I (kind of) know.

My problem is that the value being returned, when I query and build my
recordset, is OFF, for ONE of my fields, by a multiple (divisor?) of 10, on
the FIRST record in the recordset. In other words, if the value is 10, I get
1. (It's usually 10 -- I'm looking at SICK DAYS EARNED, and most people are
at 10, at this date.) The next record in the recordset is usually PERSONAL
DAYS EARNED, which is 3 for most folks, and that's coming out right. Code
follows.

Don't criticize the rest of it (I'm sure it's horrible, but I'm just trying
to get a job done!), please, but focus on the part where I build the 2ND
RECORDSET, which I've surrounded with asterisks. AC_EARNED_TO_DATE is the
culprit field. I'm also including line numbers.

I appreciate any help!

1 Function ConvAccruals()
2 Dim conAccrual, conEEUnit As ADODB.Connection
3 Dim strAccrual, strEEUnit, sSQL(1 To 5), srst As String
4 Dim rst(1 To 5) As ADODB.Recordset
5 Dim vFld(1 To 6) As String
6 Dim ACCode(1 To 3) As String
7 Dim AccType(1 To 3) As String
8 Dim x, y As Integer
9
10 ACCode(1) = "A290"
11 AccType(1) = "Earnings"
12 ACCode(2) = "A300"
13 AccType(2) = "Entitlement"
14 ACCode(3) = "A900"
15 AccType(3) = "Previous Year"
16
17 sSQL(4) = "DELETE FROM [EXPORTACCRUALS];"
18 DoCmd.SetWarnings False
19 DoCmd.RunSQL (sSQL(4))
20 DoCmd.SetWarnings True
21
22
23 Set conEEUnit = New ADODB.Connection
24 strEEUnit = "Dsn=HRProd;uid=p2k;pwd=p2k;"
25 sSQL(1) = "SELECT EID.PERSON_CODE, DUN.UNIT_CODE, DGR.group_code,
EASD.HOURS_PER_DAY "
26 sSQL(1) = sSQL(1) & "FROM ((((p2k.P2K_HR_IDENTITIES EID LEFT JOIN "
27 sSQL(1) = sSQL(1) & "p2k.P2K_HR_EMPLOYMENTS EEM ON EID.ID = EEM.EID_ID) "
28 sSQL(1) = sSQL(1) & "LEFT JOIN P2K.P2K_HR_ASSIGNMENTS EAS ON EEM.ID =
EAS.EEM_ID) "
29 sSQL(1) = sSQL(1) & "LEFT JOIN P2K.P2K_HR_ASSIGNMENT_DETAILS EASD ON
EAS.ID = EASD.EAS_I
30 sSQL(1) = sSQL(1) & "LEFT JOIN P2K.P2K_CM_UNITS DUN ON EASD.DUN_ID =
DUN.ID) "
31 sSQL(1) = sSQL(1) & "LEFT JOIN P2K.P2K_CM_GROUPS DGR ON EASD.DGR_ID =
DGR.ID "
32 sSQL(1) = sSQL(1) & "WHERE EAS.PRIME_ASSIGNMENT='1' "
33 sSQL(1) = sSQL(1) & "AND (SYSDATE BETWEEN EEM.HIRE_DATE And
NVL(EEM.TERMINATION_DATE,'31
34 sSQL(1) = sSQL(1) & "AND SYSDATE BETWEEN EASD.effective And EASD.expiry) "
35 sSQL(1) = sSQL(1) & "ORDER BY EID.PERSON_CODE;"
36
37 conEEUnit.Open strEEUnit
38 Set rst(1) = conEEUnit.Execute(sSQL(1))
39
40 Do While Not rst(1).Fields("PERSON_CODE").Value > "0011000"
41
42 Set conAccrual = New ADODB.Connection
43 strAccrual = "Driver={Intersolv 3.10 32-bit Informix
9};server='munislive';Database='mu_
44 '*********************************************************
45 sSQL(2) = "SELECT a_employee_number, a_accrual_type, a_accrual_table,
ac_earned_to_date,
46 '*********************************************************
47 sSQL(2) = sSQL(2) & "'01-Jan-0001' as START_DATE, "
48 sSQL(2) = sSQL(2) & "'31-Dec-3999' as end_DATE, "
49 sSQL(2) = sSQL(2) & "ac_used_to_date, ac_start_year_bal "
50 sSQL(2) = sSQL(2) & "from munis.pr_accruals "
51 sSQL(2) = sSQL(2) & "where a_projection=0 and a_accrual_type in
('1','2','3') and ac_act
52 sSQL(2) = sSQL(2) & "and a_employee_number = " &
Right(rst(1).Fields("person_code"), 5)
53 sSQL(2) = sSQL(2) & "order by a_employee_number, a_accrual_type;"
54
55 conAccrual.Open strAccrual
56
57 Set rst(2) = conAccrual.Execute(sSQL(2))
58
59 Do While Not rst(2).EOF
60
61 vFld(3) = rst(1).Fields("person_code").Value
62
63 Select Case rst(2).Fields(1).Value
64 Case Is = 1: vFld(1) = "Vacation"
65 If rst(1).Fields("GROUP_CODE") = "SUPERINTENDENT" Then
66 vFld(2) = "VACATION-SUPT"
67 Else: vFld(2) = "Vacation"
68 End If
69 vFld(4) = rst(2).Fields("ac_earned_to_date").Value *
rst(1).Fields("HOURS_PE
70 vFld(5) = rst(2).Fields("ac_used_to_date").Value *
rst(1).Fields("HOURS_PER_
71 vFld(6) = rst(2).Fields("ac_start_year_bal").Value *
rst(1).Fields("HOURS_PE
72 Case Is = 2: vFld(1) = "Sick"
73 If rst(1).Fields("UNIT_CODE") = "NC-NONPCS" Then
74 vFld(2) = "SICK NONPCS"
75 Else: vFld(2) = "SICK PCS"

 

[Guest]

To anyone who may need this in the future -- it was simple, but took a little
to figure it out. Those values I was pulling from Informix were held, in
Informix, as FLOATING values. So, when the number was 10, Informix would
send it to my query as '1' (stripping the zero). I now use the CAST function
in my informix query to convert it to a DECIMAL datatype, and it works fine.

 

[Stefan Hoffmann]

hi MK,

Don't criticize the rest of it (I'm sure it's horrible, but I'm just trying
to get a job done!), please, but focus on the part where I build the 2ND
RECORDSET, which I've surrounded with asterisks.

As you solved it on your own, let me criticize some of your lines.)

1 Function ConvAccruals()
2 Dim conAccrual, conEEUnit As ADODB.Connection

conAccrual is declared implicitly as Variant, not as ADODB.Connection.
You may write in one line, but you cannot list the names of your
variables. Better:

Dim conAccrual AS ADODB.Connection
Dim conEEUnit As ADODB.Connection

17 sSQL(4) = "DELETE FROM [EXPORTACCRUALS];"
19 DoCmd.RunSQL (sSQL(4))

Use CurrentDb.Execute SQL, dbFailOnError instead of DoCmd.RunSQL:

On Local Error GoTo LocalError

Dim db As DAO.Database

Set db = CurrentDb

db.Execute SQL, dbFailOnError
' other stuff

Exit Sub

LocalError:
MsgBox Err.Description

24 strEEUnit = "Dsn=HRProd;uid=p2k;pwd=p2k;"
42 Set conAccrual = New ADODB.Connection
43 strAccrual = "Driver={Intersolv 3.10 32-bit Informix
9};server='munislive';Database='mu_

I don't know, how dynamic it must be, but why don't you use linked tables?

Take also a closer look at your Informix driver, you can tweak
interpretation of the data at the driver itself.


mfG
--> stefan <--