[RESOLVED] trying to determine the days between dates between now and the dates listed using vb6

[isnoend07]

this gives an overflow error
dates are in this format: 07-26-2016

Dim i As Integer
 GRD_TIME_DATE = 3 'hard coded
Dim Days As Integer
With gridMaster

For i = 1 To .Rows - 1
Days = DateDiff("d", .TextMatrix(i, GRD_TIME_DATE), Now)

Next
End With

how can this be written ?

[dilettante]

When you format data for display you are "killing" it. It is now dead and you can't use it for general operations (calculations, etc.).

Grids and other UI controls need to have a data structure of some sort behind them to hold "living" data. In the absence of such a thing you have to write logic to "resurrect" dead data. This is called parsing.

Newbies often play fast and loose with text, hoping that the compiler will insert coercion logic for them, to take care of data resurrection as if by magic. This works after a fashion, right up until it begins to corrupt data or fail entirely.

This is what you have here.


What you'll have to do if you insist on trying to treat display elements as data structures is write parsing/resurrecting functions that can handle unscrambling your text into data.

In the simplest case you'd need to break your text into numeric substrings, apply a conversion function that can parse text to binary such as CInt() in this case, and then recombine the three resulting Integer values using the DateSerial() function.

Ideally you'd start with real (binary) data from some source like a database and only use your grid for display and retain the binary data for computation. However at some point data has to be supplied by a person and then this either requries a proper date-input control like DateTimePicker or else you are back to text parsing again. But it is far better to parse the data from text to binary forms during input and then retain the binary for all future use.

[DataMiser]

You should convert the text to a date before using DateDiff

That said are all the values in column3 holding date values as your sample?
Have you steeped through the code or checked after the error to see which row the error is on and/or what the value of the variables are?
If not then that should be your first step.

[isnoend07]

I am using datepicker to enter values
the grid only has 1 row for now
maybe i should change the format to 07/26/2016

[SamOscarBrown]

What DM says....
make sure all variables are dimensioned.

If you do something like this, it might give you the clues:

Dim myDate as Date

With gridMaster
  For i = 1 To .Rows - 1
  myDate = .TextMatrix(i, GRD_TIME_DATE)
    Days = DateDiff("d", myDate, Now)
  Next
End With

[dilettante]

The "overflow" error is the key I suppose.

A string like "07-26-2016" will probably coerce as expected, though values such as "07-12-2016" can yield unpredictable results which is why you can't rely on coercion. Depending on the locale this will be taken as either a date in July 2016 or a date in December 2016.


Most likely the overflow is because there are more days between two of your dates than will fit in an Integer, which can only contain values up to 32767.

[isnoend07]

Most likely the overflow is because there are more days between two of your dates than will fit in an Integer, which can only contain values up to 32767.

You are right about the overflow
changed Days from integer to long and now Days = 657290

[isnoend07]

Dim myDate as Date

With gridMaster
For i = 1 To .Rows - 1
myDate = .TextMatrix(i, GRD_TIME_DATE)
Days = DateDiff("d", myDate, Now)
Next
End With

tried that and still get the overflow problem

[DataMiser]

Which line do you get the overflow on? What is days defined as there?

You should also use CDate() for date conversion rather than just assigning a string to a date.

Have you tried stepping through the code and checking variable content?

[isnoend07]

Dim myDate as Date

With gridMaster
For i = 1 To .Rows - 1
myDate = .TextMatrix(i, GRD_TIME_DATE)
Days = DateDiff("d", myDate, Now)'<-overflow here
Next
End With

also tried this
MyDate = CDate(.TextMatrix(i, GRD_TIME_DATE))<-Type Mismatch error

[DataMiser]

also tried this
MyDate = CDate(.TextMatrix(i, GRD_TIME_DATE))<-Type Mismatch error

That would indicate that your string is not in a valid format to be converted to a date.
This could also happen if you have a blank row in your grid

[isnoend07]

That would indicate that your string is not in a valid format to be converted to a date.
This could also happen if you have a blank row in your grid

Had already checked that
Debug.Print .TextMatrix(i, GRD_TIME_DATE) produces 07-04-216
next line the error

[SamOscarBrown]

look at your 'date'....the year is 216....that is a LONG time ago.....

[DataMiser]

Debug.Print CDate("7-20-2016")

Prints 7/20/2016

Debug.Print CDate("07-04-216")

Prints 7/4/216

So when you got a type mismatch there was something wrong with your data that made it invalid.

the year 216 could very well cause an overflow as liek Sam says that is a really long time ago. Need a bigger var to hold that value

[isnoend07]

good eye, finally got it to work

[isnoend07]

All you people have helped me. thank you
each got a message "to spread reputation"