Solved

MS VB6 and MD Access Database using ADODB strange retrieval problem hard to describe.

Posted on 2010-09-10
16
827 Views
Last Modified: 2012-05-10
Please help.

My program (written in VB6 Enterprise Edition) is used for entering psychotherapy notes for patients in group sessions.
I use a Masked Text box from which the user selects/enters the time of the different group sessions.
There are 10-30 people per session.

The program then automatically fills out and prints individual Excel 2007 sheets for each session (e.g., 8:00am, 12:00pm, and 6:00pm). These sheets have every member of that group listed on them.

The problem is that the program is not printing the members for every group time. It seems to work every time for certain times of the day, but not for others (see examples below):

Times that work:
8:00am, 9:00am, 11:00am, 12:00pm, 3:00pm, 4:00pm, 6:00pm

Times that don't:
7:00am, 10:00am, 2:00pm, 5:00pm, 11:00pm

Below I trace the code with the time value in it (the actual code does not include the reference numbers (e.g., 1), 2), etc):

1) sTimeStartLng = Format(CDate(mskStart.Text & " " & cmbAMPM.Text), "long time")
'This code is in the form that has the masked text box on it.
'sTimeStartLng is dim'd as a string in a module... It's actually probably a variant (and I don't know what that means) because I have it grouped together with a bunch of other variables like this: Public sDBpath, sInput, sFname, sTimeStartLng, sDate as string

2) rstlist!Start = Format(CDate(sTimeStartLng), "Long Time") 'sTimeStartLng
'This line of code is in the "WriteToGroupDB" sub routine using ADODB connection to an MS Access Database (see 'open' code below)
'I know i'm formatting it twice... this is a result of desperation in trying to get this thing to work...

Dim sQry As String
Dim cnnConnection As ADODB.Connection
Dim rstlist As ADODB.Recordset
Set cnnConnection = New Connection
cnnConnection.ConnectionString = sItsConnectionString

sQry = "SELECT * FROM T_GroupSession "

cnnConnection.Open
    Set rstlist = GetRecordSet(cnnConnection, sQry)
    rstlist.AddNew
    rstlist!Start = Format(CDate(sTimeStartLng), "Long Time") 'sTimeStartLng
    etc...
    rstlist.Update
    rstlist.Close
cnnConnection.Close
Set rstlist = Nothing
Set cnnConnection = Nothing


3) Below is the entire subroutine for pulling each group member name for the selected start time (e.g., if the group starts at 8:00am, pull all the names of the people that attended that group and list them here. The reason this looks so convoluted is a) I'm a crappy programmer, and b) it needs to list the first 15 people in one column of the Excel worksheet and any remaining names (up to 15 more) on another column of the Excel worksheet.

Sub PopulateGroupList()

Dim sQry As String
Dim cnnConnection As ADODB.Connection
Dim rstlist As ADODB.Recordset
Dim x As Integer
sDBPath = App.Path & "\clientdb.mdb"
sItsConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);" & "DBQ=" & sDBPath & ";"
Set cnnConnection = New Connection
cnnConnection.ConnectionString = sItsConnectionString


sQry = "SELECT * FROM T_GroupSession WHERE adoDate = #" & dDate1 & _
"# AND start = #" & sTimeStartLng & "#"

x = 0
cnnConnection.Open
    Set rstlist = GetRecordSet(cnnConnection, sQry)
'MsgBox "date = " & dDate1 & ", sTimeStartLng = " & Format(sTimeStartLng, "Long Time") & _
", & recordcount = " & rstlist.RecordCount & "program = " & sProgram

    With excel_app
    If rstlist.RecordCount <> 0 Then
        rstlist.MoveFirst
        For x = 1 To rstlist.RecordCount
        'MsgBox "sTimeStartLng = " & sTimeStartLng & ", x=" & x & ", and recordcount = " & rstlist.RecordCount
       
            If rstlist.EOF = True Or x > 30 Then Exit Sub
                If x + 5 < 21 Then
                    .Cells(x + 5, 2).Value = rstlist!fname & " " & Left(rstlist!lname, 1)
                        If rstlist!program = "Drug Court" Then
                            .Cells(x + 5, 5).Formula = "x"
                        ElseIf rstlist!program = "DUI Court" Then
                            .Cells(x + 5, 6).Formula = "x"
                        End If
                Else
                    .Cells(x - 10, 8).Value = rstlist!fname & " " & Left(rstlist!lname, 1)
                        If rstlist!program = "Drug Court" Then
                            .Cells(x - 10, 11).Formula = "x"
                        ElseIf rstlist!program = "DUI Court" Then
                            .Cells(x - 10, 12).Formula = "x"
                        End If
                End If
            rstlist.MoveNext
        Next x
    End If
    End With
rstlist.Close
cnnConnection.Close
Set rstlist = Nothing
Set cnnConnection = Nothing

End Sub

4) Finally, if I open up the database and open the table "T_GroupSession" and manually re-enter the time (e.g., "5:00:00 PM") in the start field of any group member who attended the 5:00 pm group, it prints on the Excel worksheet when I re-run the program and press the print button.

So, it would appear that somehow the 5:00:00 PM time (along with the other in the "not working" list above) are somehow getting written to the database in a different format or something? Why, when the same code is writing the other times, do SOME of the times not seem to be working.

This is killing me.

Thanks.

0
Comment
Question by:tlengnick
  • 11
  • 4
16 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33652084
tlengnick,

Can you trace (i.e., use message box or some other debug method to display) the value of sTimeStartLng after code (1) runs and additionally the value of sQry before the calling ADO.  Just to ensure what we think is being called is indeed what is.

Additionally, I don't see anything in the above preventing specific times from being inserted into the database; therefore, when retrieving, it isn't inconceivable that any value could exist.

mwvisa1
0
 
LVL 16

Accepted Solution

by:
HooKooDooKu earned 350 total points
ID: 33653457
What are the data types of the various fields in question in the data base.  You might be running into problems with mixed data types as well as rounding errors as it relates to time.

Microsoft tracks Date/Time with a double.  The whole number part of a double is the date (where 0 is something like 01/01/1900 I believe).  The fracitonal part represents the time as a fractional day, so 0.0 is midnight, 0.25 is 6am, 0.5 is noon, and 0.75 is 6pm.  But the thing with doubles (floating point numbers in computers) is that decimal values might only be an estimation of the value you want.  Basically, with fractional numbers, only "binary" fractions can be perfectly stored in a floating point number (i.e. 1/2, 1/4,1/8, 1/16, 1/32, etc).  So when you store a odd fractional value (say 0.3), the best approximation the computer can do is perhaps a value like 0.299999999987.  This means when dealing with floating point numbers, you frequently can't do "EQUALS" comparison.  Because if you have stored "0.3" in a database field that represents a floating point number, what is actually in the database is 0.299999999987.  Therefore when you do a query that says "Field1 = 0.3", you might not match the value in the database you think you are going to match.

So one POSSIBLE solution is to change queries that basically say (where time = 3:00) to (where time >2:29 and time < 3:01).

The other thing that can help is to try to use varibles in your program to match what is in the database.  So if a date is stored in the database as a date field (which is really a floating point number), then create and use variables of type Date (which again is really just a double).  This is what CDate creates, and that way you can perfectly track what the real value of your date/time is just before you build the query (i.e. save the Format command until you are ready to convert all your various data types to a string to build the query).

While I know this doesn't pinpoint your issue, I can tell you from experience I've had situations where a program behaved differently based on the time of day, and it all was because my programming was not accouting for the fact that "TIME" is a floating point number, and that computers can't store (fractional parts of) a floating point number exactly.
0
 

Author Comment

by:tlengnick
ID: 33653468
mwvisa1,

Thanks for the reply.

1) I can't figure out how to trace these variable (meaning, I tried to "add watch" thinking it would "watch" the changes to the variable value and display them in the watch window)... can you help?

2) I do have a format set in the Access database of "Long Time" (e.g., 00:00:00 AM, etc)... that should restrict the type of entries allowed. Furthermore, when I look at the values directly in the database, the time reads "5:00:00 PM" in the offending record (meaning, I see that "start" in the database = "5:00:00 PM" on a record that is NOT showing up... so, to my human eyes, it looks right...


db-view1.jpg
0
 

Author Comment

by:tlengnick
ID: 33653474
Here is an image of the "start" field in the database... it is formatted to access only date/time values that adhere to the "long time" format (00:00:00 AM/PM)
sTimeStartLng-format-in-Access.jpg
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33653484
(1) As I said, by trace you can simply put a message box immediately after those lines of code to see what the value of the strings are.

(2) Still not sure how setting a format of "Long Time" limits someone from entering "05:00:00 PM" which meets the format; however, I am probably just missing what you are trying to say there and this is really inconsequential to your issue which would appear to be that the entered time is being sent to database queries as something else.  

Therefore, let's focus on (1).
0
 

Author Comment

by:tlengnick
ID: 33653499
Hookoodooku,

Ok... thanks for that detailed explanation... I kind of played around with that possibility in the beginning, but I was running into some trouble getting Access to access my variable dim'd as a date. I don't recall exactly what it was, but I'm willing to go back and dim my sTimeStartLng to dTimeStartLng and write it to the DB. I'd rather do that than add the > or < aspect, just because that seems sloppier, and my code is sloppy enough and I want to learn how to do things more cleanly...

So, to be clear, are you recommending I dim the variable "sTimeStartLng" as a Date, or simply wait until I'm about to write it to the database and then do a cdate(sTimeStartLng) to convert it to a date?
0
 

Author Comment

by:tlengnick
ID: 33653521
mwvisa,

1) Sorry about that... right, I use msgbox a lot to troubleshoot... I've done that and it always shows up right (i.e., it looks like the right time...) sTimeStartLng is being created by a masked text box which checks for the proper format before moving on (i.e., you cannot enter an invalid time and continue - it will stop you... see code below and image.)


'Validate the time entry
If mskStart.Text = "" Or mskStart.Text = "__:__" Then
    MsgBox "You must enter the start time for this group."
    mskStart.SetFocus
    Exit Sub
End if
If IsNumeric(Left(mskStart.Text, 1)) Then
    If Not IsNumeric(Mid(mskStart.Text, 2, 1)) Then
        MsgBox "Invalid time entry."
        mskStart.SetFocus
        Exit Sub
    End If
End If

If Not IsNumeric(Mid(mskStart.Text, 4, 1)) Then
        MsgBox "Invalid time entry."
        mskStart.SetFocus
        Exit Sub
ElseIf Not IsNumeric(Right(mskStart.Text, 1)) Then
        MsgBox "Invalid time entry."
        mskStart.SetFocus
        Exit Sub
End If

The following code is run in the form displayed below to set the value of sTimeStartLng:

GroupForm-View1.jpg
0
 

Author Comment

by:tlengnick
ID: 33653525
forgot to include the code to go with that image I just posted:
sTimeStartLng = Format(CDate(mskStart.Text & " " & cmbAMPM.Text), "long time")
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tlengnick
ID: 33653553
I have a few comments/questions:

1) Would a TRIM statement do anything for this problem? and

2) Since many times DO work, it seems like this has to be an "internal problem", like what Hookoodooku was saying about how time is formatted...

3) It seems the fact that I can open the database and type in a "5" in the Start field replacing only the first number of 5:00:00 PM (see the entry above with the ID# ending in 3468) and then it works suggests that the time getting written to that database is screwed up somehow...
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33653577
Yes, if the column you are comparing to is an actual date and time column, then passing just the #5:00:00 PM# may not result in what you are expecting.
0
 

Author Comment

by:tlengnick
ID: 33655331
mwvisa1,

I don't understand your last comment... are you saying I should be writing the variable to the database in a different way?
0
 

Author Comment

by:tlengnick
ID: 33655395
When I do the following:
msgbox rstlist!Start

It returns "5:00:00 PM", which is the exact same thing I "see" when I do:

msgbox sTimeStartLng

So, it's pissing me off that MS VB6 and MS Access are not seeing those as a "match".... Is there any way I can see the exact (or "real") value of that "5:00:00 PM" in the 'Start' field of the Access DB?
0
 

Author Comment

by:tlengnick
ID: 33655420
why is this line returning an error?

sQry = "SELECT * FROM T_GroupSession WHERE adoDate = #" & dDate1 & "# AND start > #4:59:99#"
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 150 total points
ID: 33655453
My previous comment was agreeing with your that it may be there is an &quot;internal problem&quot; per your comment http:#a33653553The kind of problem may be dealing with the data type which was what my comment was trying to say.As far as your last post, there is no such time as 4:59:99.  There is 4:59:59 or 5:00:39 (which is 99 seconds after 4:59).
0
 

Author Comment

by:tlengnick
ID: 33655489
I've got it working...

it's ugly, but the solution is basically Hoodooku's recommendation about putting a RANGE of times to look for... here's what I did in case this comes up for someone else.

I just took the offending times (the ones that weren't working) and had it look for a range... I guess I could've made it do that for ALL the times, yielding less code... I don't really know how I'd tell it to find from 1 second less than sTimeStartLng to 1 second more than sTimeStartLng, however...

Select Case sTimeStartLng
    Case "07:00:00 AM"
        bSelect = True
        sQry = "SELECT * FROM T_GroupSession WHERE adoDate = #" & dDate1 & _
        "# AND (((start) Between #6:59:59 AM# And #7:00:01 AM#))"
       
    Case "10:00:00 AM"
        bSelect = True
        sQry = "SELECT * FROM T_GroupSession WHERE adoDate = #" & dDate1 & _
        "# AND (((start) Between #9:59:59 AM# And #10:00:01 AM#))"
   
    Case "02:00:00 PM"
        bSelect = True
        sQry = "SELECT * FROM T_GroupSession WHERE adoDate = #" & dDate1 & _
        "# AND (((start) Between #1:59:59 PM# And #2:00:01 PM#))"
   
    Case "05:00:00 PM"
        bSelect = True
        sQry = "SELECT * FROM T_GroupSession WHERE adoDate = #" & dDate1 & _
        "# AND (((start) Between #4:59:59 PM# And #5:00:01 PM#))"
End Select

If bSelect = False Then
    sQry = "SELECT * FROM T_GroupSession WHERE adoDate = #" & dDate1 & _
    "# AND start = #" & sTimeStartLng & "#"
End If

mwvisa1 - good catch on my stupid 4:59:99....  thanks for your efforts... I'm going to split points but give more to Hoodooku as his solution was on the mark...

thanks to you both, however... this thing has bugged the crap out of me for weeks...
0
 

Author Closing Comment

by:tlengnick
ID: 33655495
Thanks for the help on a tricky problem!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now