datagrid 1

[vbjock]

I created an application using the VB6 Wizard to show an Access table using datagrid. One of the fields is date/time. I want to be able to enter 080508 then convert it to 08/05/08 and store it back to the cell. It would not let me using AfterColUpdate. I get an error message from Datagrid saying it is an invalid entry. If I make the field as text, there is no problem. Is there a way around this?
Thanks

 

[SBerthold]

Try the BeforeColUpdate event.

You can also try using a StdFormat object from the Microsoft Data Formatting Object Library

 

[vbjock]

I tried BeforeColUpdate before and I get the message: Multiple-step operation generated errors. Check each status value. I just printed ms/315648 (Custom formatting/stddataFormat object). I will give it a try later this morning. I believe it should work for date fields.
Thanks for the help/suggestion.

 

[SBerthold]

>Multiple-step operation generated errors

You are then doing something else wrong

 

[vbjock]

Hi SBerthold,

Maybe I am but message only appears if datagrid thinks an invalid date is entered, e.g., 080508. If I enter 08/05/08, it is accepted. The simplified code is:
dim tmp as string
tmp = me.datagrid1.text
' code to check for user typing in 32/8/8, 1/32/8 is omitted
if isnumeric(tmp) and len(tmp) = 6 then
tmp = left(tmp,2) & "/" & mid(tmp,3,2) & "/" & _
mid(tmp,5,2)
me.datagrid1.text = tmp
exit sub
end if

The error message appears whether I use BeforeColUpdate or AfterColUpdate.
If I change my Data type from date/time to text, problem goes away. However, when I issue a sql/query like:
Select * from PaymentsTable order by cdate(dueDate), the query aborts if dueDate happens to be empty.

I tested KB/315648 and it works but quite a bit of coding is involved especially on the myFmt_unformat sub. I only just realize entering 32/08/08 gets converted to 08/08/32 when I want this flagged as invalid.
Thanks for the help.

 

[SBerthold]

Ah yes. You can turn off the error message from being shown, but the event cannot be ignored. The user will have to click a second time for the formated date to actually take.

Another solution is an unbound EditBox hovering over the grid cell.
Yet another solution using the StdDataFormat object:

Option Explicit
Private WithEvents stdfmtDate As StdDataFormat

Private Sub LoadDataGrid()
     Set stdfmtDate = New StdDataFormat
     stdfmtDate.Type = fmtCustom
     stdfmtDate.Format = "dd/mm/yyyy"

     Set DataGrid1.DataSource = oRecordset
     Set DataGrid1.Columns("SomeDateField").DataFormat = stdfmtDate
End Sub

Private Sub stdfmtDate_UnFormat(ByVal DataValue As StdFormat.StdDataValue)

    'Check and format the input into a real date
    If (Not IsDate(DataValue)) Then
        'Either re-format the input into a date
        If IsNumeric(DataValue) Then
            If (Len(DataValue)) = 6 Then
                DataValue = Format$(DataValue, "00\/00\/00")
            ElseIf (Len(DataValue) = 8) Then
                DataValue = Format$(DataValue, "00\/00\/0000")
            End If
        End If
        'or if it cannot be formated to a valid date, assign to the field a Null value=No Date
        If (Not IsDate(DataValue)) Then
            MsgBox "Not a Date input"
            DataValue = Null
        Else
            'Keep the input date within a certain range
            If CDate(DataValue) < DateSerial(2000, 1, 1) Or CDate(DataValue) > DateSerial(2010, 12, 31) Then
                MsgBox "Date out of range"
                DataValue = Null
            End If
        End If
        End If
End Sub

 

[vbjock]

Hi Sberthold,

This is an updated version of sub stdfmtDate_UnFormat(). It works very well and I modified a little to watch out for users typing 32/08/08, for example. Here it is:
Private Sub stdfmtDate_UnFormat(ByVal DataValue As StdFormat.StdDataValue)

'Check and format the input into a real date
If (Not IsDate(DataValue)) Then
'Either re-format the input into a date
If IsNumeric(DataValue) Then
If (Len(DataValue)) = 6 Then
DataValue = Format$(DataValue, "00\/00\/00")
ElseIf (Len(DataValue) = 8) Then
DataValue = Format$(DataValue, "00\/00\/0000")
End If
End If
'or if it cannot be formated to a valid date, assign to the field a Null value=No Date
If (Not IsDate(DataValue)) Then
MsgBox "Not a Date input"
DataValue = Null
Else
'Keep the input date within a certain range
If CDate(DataValue) < DateSerial(2000, 1, 1) Or CDate(DataValue) > DateSerial(2010, 12, 31) Then
MsgBox "Date out of range"
DataValue = Null
End If
End If
Else
' yes, it is a valid date, check if < 01/01/2000 or > 12/31/2010
If CDate(DataValue) < DateSerial(2000, 1, 1) Or CDate(DataValue) > DateSerial(2010, 12, 31) Then
MsgBox "Date out of range"
DataValue = Null
End If
End If
End Sub


'***************************
Thanks for the tip.

 

[SBerthold]

Yes, but use a boolean variable identifying that the date is a valid date

Dim bIsValidDate as boolean

Use this like this

Else
'Keep the input date within a certain range
bIsValidDate =true
End If
Else
' yes, it is a valid date, check if < 01/01/2000 or > 12/31/2010
bIsValidDate =true
End If

If bIsValidDate =True then
' yes, it is a valid date, check if < 01/01/2000 or > 12/31/2010
If CDate(DataValue) < DateSerial(2000, 1, 1) Or CDate(DataValue) > DateSerial(2010, 12, 31) Then
MsgBox "Date out of range"
DataValue = Null
End If
End If

 

[9milla]

Hi there vbjock

I'm not sure if my input counts at all, but as per my experience, any 'DATE' value your're trying to input into an Access Rowsource i.e. 'Cell' wont work directly unless you physically type it in the cell itself.

Reason for this is because Access will only take literals and not strings for a DATE\TIME value.

Any formatting of a DATE in anyway thus looses it's [!]internal value[/!] as being a date. Yes, it might display as a date but what is actually stored within Access is 'garbage' characters which doesn't have any relevance to a DATE\TIME format.

The 'Convert' function won't work either as this is physically converting the DATE to some other DATATYPE and thus not being a DATE/TIME value.

A work around this will be able to parse parameters to the 'Cell', thus insuring that no matter how you parse the date value, it will always be stored as a true DATE/TIME format.


If your Regional Settings are different, bear in mind that this is also only a way to display a 'DATE'.

So whether you have yyyy/mm/dd or dd/mm/yyyy or any other display for your date, remember that the Universal Storage of DATES in Access is yyyy/mm/dd. This is by no means a Regional Setting format it just happens that it's the same as the US DATE Format.


Cheers
9milla






"And so it begins!"
LOTR (Battle for Helms Deep)

 

[vbjock]

Hi SBerthold/9milla,

I eventually just made a callable subroutine to validate/reformat an input. Goal is to make any valid date
(slash delimited) or mmddyy returned as mm/dd/yy. For my purposes, I just want to be sure valid dates are in the range of 01/01/00 to 12/31/99.

On your suggestion to have a boolean to identify a valid date, my original approach was to check if a non valid date
but if a valid date (ELSE) then check if within the date range desired.

My MSHFlexgrid is what I understood as unbound. That is I would fill the grid from an access table so any date field
is treated as text. User would either change or add data and a command button (Save) is available to save the
additions/changes. Access table accepts the values as text and I would assume an auto conversion is being done such
that they are being accepted/stored as date/time.

By the way, I tried the bound approach where I set the MSHFlexgrid = adoRecordset. However, there is speed and timing concerns when the user goes fast on their typing and the Ado recordset does not act right away.

Thanks for all your suggestions.
VBJock




Public Sub VerifyReformatDate(ByRef strDate As String, ByRef ErrReturn As Integer)
' Input strDAte: slash delimited date or mmddyy
' ErrReturn: 0 = no error and within date range, 100 = invalid date, 101 = Valid date but not within
' valid range

Dim txtEdit As String
txtEdit = strDate

If IsDate(txtEdit) Then
If CDate(txtEdit) >= #1/1/2000# Then
strDate = Format(txtEdit, "mm/dd/yy")
ErrReturn = 0

Exit Sub
Else
ErrReturn = 101
strDate = ""
Exit Sub
End If
End If

' users is expected to input mmddyy (mmddyyyy is unlikely)
If IsNumeric(txtEdit) And Len(txtEdit) = 6 Then
txtEdit = Left(txtEdit, 2) & "/" & Mid(txtEdit, 3, 2) & "/" & Mid(txtEdit, 5)

If Not IsDate(txtEdit) Then

strDate = ""
ErrReturn = 100
Exit Sub
Else
If CDate(txtEdit) >= #1/1/2000# Then
strDate = Format(txtEdit, "mm/dd/yy")
ErrReturn = 0
Exit Sub
Else
strDate = ""
ErrReturn = 101
Exit Sub

End If
End If
Else

strDate = ""
ErrReturn = 100
Exit Sub


End If

End Sub