Printable View
this gives an overflow error
dates are in this format: 07-26-2016
how can this be written ?Code: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
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.
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.
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
What DM says....
make sure all variables are dimensioned.
If you do something like this, it might give you the clues:
Code: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
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.
You are right about the overflowQuote:
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.
changed Days from integer to long and now Days = 657290
tried that and still get the overflow problemQuote:
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
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?
also tried thisQuote:
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
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.Quote:
Originally Posted by isnoend07
This could also happen if you have a blank row in your grid
Quote:
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
look at your 'date'....the year is 216....that is a LONG time ago.....
Prints 7/20/2016Code:Debug.Print CDate("7-20-2016")
Prints 7/4/216Code:Debug.Print CDate("07-04-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
good eye, finally got it to work
All you people have helped me. thank you
each got a message "to spread reputation"