VB is unable to handle DB2 date = 0001-01-01

Hi onthemark,

please show us the actual sql stm.

do a
debug.print  sSql
and post the results..


Cheers!

Hi Lowfatspread,

I'll have to wait till Monday to post the actual sql statement as I can't run the code offsite.  The dbfield_ft, dbfield_ta, dbfield_cn, dbfield_tye are declared constants that are assigned to the db2 table fields as follows:

private const DBFIELD_BN = "BATCH_NUM"
private const DBFIELD_FT = "FORM_TP"
private const DBFIELD_TA = "TAX_ACT"
private const DBFIELD_CN = "CLIENT_NUM"
private const DBFIELD_TYE = "TAXN_END_DT"

TABLE_BATCH is another constant that contains the name of the DB2 table.  It is declared as follows:
Private const TABLE_BATCH  = "ritbpdb.enit"

The Batch.BatchPages(1).BatchPageIndexes(1).Value returns the batch number of the current batch.  This number has been keyed by the operator and validated against the TABLE_BATCH table.  

The "AND CHQ_AMT=0" excludes entries for items (cheques) that are not in the batch being processed.

So.... the "ssql = " statement in my original post results in the following:
SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM,TAXN_END_DT FROM RITBPDB.ENIT
  WHERE BATCH_NUM = '19100' AND CHQ_AMT=0
  ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;

Using the CHAR() function the sql statement is as follows:
SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM,CHAR('TAXN_END_DT') FROM RITBPDB.ENIT
  WHERE BATCH_NUM = '19100' AND CHQ_AMT=0
  ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;

This particular query returns 21 records.  9 of which have a TAXN_END_DT of 0001-01-01.

ok ,
I wanted to set the statement, really just to confirm you hadn't got any syntax type problems...

both your examples look ok....
although I'm not sure why you have quotes around the TAXN_END_DT in the char example...

have you considered

using
 case when taxn_endt_dt = '0001-01-01' then null else taxn_endt_dt end as TAXN_END_DT  
in the select...

Hi Lowfatspread,
Good suggestion.  I'll give it a try on Monday.  I'm not sure why I put the quotes in the char statement either.  I was just typing from memory.

Mark

Can you try having the default date later than 1756 or null as lowfatspread has told.  Most probably the problem is DB2 allows default date as low as 0001-01-01 which no other databases accept.

Just try and let us know..

The problem is definitely that 0001-01-01 is a valid BD2 date but not a VB date.  I can't change the default.  

I tried using the case statement as suggested by lowfatspread but as with the char statement, the taxn_end_dt field is still not accessible.  
Any attempt to reference the field in the recordset (e.g. if rssearch("TAXN_END_DT") = NULL results in an "Item cannot be found in the collection corresponding to the requested name or ordinal" error.

It appears that the taxn_end_dt has already been typed as a "date" field (at connection time? and any field value that is not seen as a date throws an error.

Is there another way of making the db2 records accessible from VBA?

For now I've asked for clarification of the business rules resulting in 0001-01-01 dates.... hopefully I can work around the issue based on form_tp.  It'll be ugly but may be an out.

can you create a view on the table
which includes the case statement to either nullify or move the date when its a default?


Just noticed we haven't both missed a T have we TAXN_END_DT  
                                                                               TAXN_ENDT_DT  
?

In the recordset, check for the value of the taxn_end_dt and if found equal to 0001-01-01 then do some thing else do something else..
if rssearch("TAXN_END_DT") = '0001-01-01' should be the condition. or try if rssearch("TAXN_END_DT").value = '0001-01-01'

You need to explore the functions in recordset.. Once you get the correct function then you are done. Will let you know once it works for me.

Thanks

I could create the situation you are facing and here is the solution for your problem


SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM,TAXN_END_DT FROM RITBPDB.ENIT
  WHERE BATCH_NUM = '19100' AND CHQ_AMT=0
  ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;
 has to be changed

SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM,cast (TAXN_END_DT as char(10)) FROM RITBPDB.ENIT
  WHERE BATCH_NUM = '19100' AND CHQ_AMT=0
  ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;


Thanks

Once the above statement works, the record set will have the taxn_end_dt with string data type. Here you can perform any operation. As already discussed, the problem was the mismatch between the minimum dates allowed in DB2 and Microsoft driver.

hth

TAXN_END_DT is the correct field name.

If I use the Cast function the same error occurs as with using either Char(taxn_end_dt) or a case statement (as innoted in previous posts).

The error is :
   "Item cannot be found in the collection corresponding to the requested name or ordinal"

This error occurs if the rssearch("TAXN_END_DT") field is referenced in a any way.  I can't process based on the value of this field as the error occurs whenever the field is referenced.

I'm pushing to see about the likelihood of getting the default changed.

Mark,

Your sql in the VB code is

Set mConDB = New Connection
 mConDB.Open "DSN=" & DSN & "; UID=" & UID & "; PWD=" & PWD

 'Construct sql to return one record for each document expected in the batch
 sSql = "SELECT " & DBFIELD_BN & "," & DBFIELD_FT & "," & DBFIELD_TA & "," & DBFIELD_CN & "," & DBFIELD_TYE & " FROM " & TABLE_BATCH & " WHERE " & DBFIELD_BN & " = '" & Batch.BatchPages(1).BatchPageIndexes(1).Value & "' AND CHQ_AMT=0 ORDER BY " & DBFIELD_FT & "," & DBFIELD_TA & "," & DBFIELD_CN

Set rsSearch = New Recordset
 rsSearch.Open sSql,mConDB     ' get the record set

Here you have to modify either the above query or the below definition you have.
private const DBFIELD_TYE = "TAXN_END_DT"

Basically you are not casting the date type to char(10). Please do that.. Have a simple hard-coded sSQL variable as i have mentioned above and test the result. This should work and then you can have this code of passing the pre-defined variables. I have tried the same and got the same error. Then did the casting and i could compare with "0001-01-01" and then i could do what ever. The comparison has to be done in recordset level. But the sql statement has to be modified to return character and not date. Refer to my above query.. or as below..

SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM,cast (TAXN_END_DT as char(10)) FROM RITBPDB.ENIT
  WHERE BATCH_NUM = '19100' AND CHQ_AMT=0
  ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;

 and then in recordset do the comparison. It worked for me. Please check..

Thanks

Askanivg,

I think I have done what you suggested but still get errors.

I created a simple VB test app with one form consisting of a listbox and 2 buttons.  
Button1 opens the connection to the DB2 table, constructs the sql for batch_num='19100', executes the sql then loops through the record set adding the field values of each to the listbox.  

Button2 closes the db2 connection and frees resources.

To simplify the sql string construction and eliminate potential for any oddities the sql statement has the table and field names hard-coded.

If the item being added to the listbox has a TAXN_END_DT of "0001-01-01"  then the following error occurs:
"Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal."
If TAXN_END_DT = an actual date everything works.

The code is below.

Is there something I'm missing?

****** Start of VB Test **********************************
Option Explicit

'Data access constants
Private Const DSN = "db2p"
Private Const UID = "userid"
Private Const PWD = "password"
Private Const TABLE_BATCH = "ritbpdb.enit"

'Fields used for DB2 reconciliation queries
Private Const DBFIELD_BN = "BATCH_NUM"
Private Const DBFIELD_CN = "CLIENT_NUM"
Private Const DBFIELD_FT = "FORM_TP"
Private Const DBFIELD_TA = "TAX_ACT"
Private Const DBFIELD_TYE = "TAXN_END_DT"

Private mConDB As Connection
Private sSql As String
Private rsSearch As Recordset
Private Temp As String

Private Sub Command1_Click()

 Set mConDB = New Connection
 mConDB.Open "DSN=" & DSN & "; UID=" & UID & "; PWD=" & PWD

 'Construct sql to return one record for each document expected in the batch
sSql = "SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM," & _
        "CAST(TAXN_END_DT AS CHAR(10)) " & _
        "FROM RITBPDB.ENIT WHERE BATCH_NUM = '19100' AND CHQ_AMT=0 " & _
        "ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;"

 Set rsSearch = New Recordset
 rsSearch.Open sSql, mConDB ' get the record set

 While (rsSearch.BOF = False And rsSearch.EOF = False)
   List1.AddItem (rsSearch("FORM_TP") & rsSearch("TAX_ACT") & rsSearch("CLIENT_NUM") & "    " & rsSearch("TAXN_END_DT"))
 Wend

 rsSearch.Close
 Set rsSearch = Nothing

End Sub

Private Sub Command2_Click()
 mConDB.Close
 Set mConDB = Nothing
 ' free resources
 Unload Form1
End Sub

Askanivg,

It works. Excellent hlp my fired, greatly appreciated.
I'm new to experts exchange.
What is the process for giving you full points?

Onthemark,

I too and new and i donno how you give full points to me.

Well its works and thats great.. Congratulations.............

Thanks...

So i can't type...
I meant excellent help my friend.

Thanks and here you go .. i got the details from help..

There are two kinds of points at Experts Exchange: Question points and Expert points, and they have little relationship to each other. As a member, you receive five Question points every day with which to ask questions. When you ask a question, the points are removed from your Available points total into Escrow. Once you've accepted a comment as an answer, the points are removed from Escrow; if your question is deleted, they are returned to your Available total.

If you are a Premium Services member, then your available points total never decreases (it just increases by 5 points per day), no matter how many questions you ask and how many points the questions are worth.

When you accept a comment as an answer, the Question points are multiplied by a factor depending on the grade you give and added to the Expert point total of the person who answered your question. Giving a higher grade does not affect your Question points. For more information regarding grades, please look at What's the right grade to give.

The points you offer for a question are to be a reward for getting assistance from Experts; they are not necessarily an indication of the "value" of the answer. For instance, you may want a very simple question answered very quickly; one way to get your question attended to is to offer more points for it -- but it's still a simple question. For more information, see the For more tips on how to grade, please look at The Member page.