Solved

Masked Edit from VB to Access

Posted on 1998-06-09
4
415 Views
Last Modified: 2010-05-03
When I have a masked edit box in VB as ##/##/## (Date) and a short date format in my input mask in Access, I can't retrieve the data correctly from Access if the month contains a zero as the first digit (01/11/98) will be 11/19/8 in VB masked edit box. How do I correct it?
0
Comment
Question by:paulca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 2

Accepted Solution

by:
swilt earned 20 total points
ID: 1462908
In Access
    Format : Short Date
    My Input Mask is blank

In Visual Basic (Masked Edit)
    Unbound data control
    Mask = "##/##/##"



Option Explicit

Private Sub Data1_Reposition()
    Dim sMask As String, sDate As String
   
    If IsNull(Data1.Recordset.MyDate) Then
        sMask = MaskEdBox1.Mask
        MaskEdBox1.Mask = ""
        MaskEdBox1.Text = ""
        MaskEdBox1.Mask = sMask
    Else
        sDate = Format$(Data1.Recordset.MyDate, "short date")
        If Len(sDate) = 10 Then 'chop the century
            sDate = Left$(sDate, 6) & Right$(sDate, 2)
        End If
        MaskEdBox1.Text = ReplaceChar(sDate, ".", "/")
    End If
End Sub

Private Function ReplaceChar(ByVal sInp As String, _
                             ByVal sChar As String, _
                             ByVal sRepl As String) As String
    Dim nPos As Integer
   
    nPos = InStr(sInp, sChar)
    While nPos > 0
        Mid$(sInp, nPos, 1) = sRepl
        nPos = InStr(sInp, sChar)
    Wend
    ReplaceChar = sInp
End Function


0
 
LVL 2

Expert Comment

by:swilt
ID: 1462909
I forgot to update the date

Private Sub Data1_Validate(Action As Integer, Save As Integer)
    If InStr(MaskEdBox1.Text, "_") = 0 Then
        Data1.Recordset.Edit
        Data1.Recordset!MyDate = MaskEdBox1.Text
        Data1.Recordset.Update
    Else
        MsgBox "Invalid Date"
    End If
End Sub

0
 
LVL 2

Expert Comment

by:swilt
ID: 1462910
A bit more validation - should allow empty date entry and not move on if the date is invalid

Private Sub Data1_Validate(Action As Integer, Save As Integer)
    If MaskEdBox1.Text = ReplaceChar(MaskEdBox1.Mask, "#", "_") Then
        Data1.Recordset.Edit
        Data1.Recordset!MyDate = Null
        Data1.Recordset.Update
    Else
        If InStr(MaskEdBox1.Text, "_") = 0 And IsDate(MaskEdBox1.Text) Then
            Data1.Recordset.Edit
            Data1.Recordset!MyDate = MaskEdBox1.Text
            Data1.Recordset.Update
        Else
            MsgBox "Invalid Date"
            Action = vbDataActionCancel
        End If
    End If
End Sub
0
 
LVL 1

Expert Comment

by:Celia
ID: 3294132
Hi Swilt,

I have two masked edit box in VB6 as ##/##/####(Short Date) and ##:##(Short Time).My only problem is that I can't display my 1st MsgBox.

Coding(Date) as as below:

Private Sub txtAdmdate_Validate(Cancel As Boolean)

If txtAdmdate.Text = ReplaceChar(txtAdmdate.Mask, "_") Then
    '1st Msgbox
    MsgBox "Blank date"
Else
    If InStr(txtAdmdate.Text, "_") = 0 And IsDate(txtAdmdate.Text) Then
    '2nd MsgBox
    MsgBox "Valid Date"
Else
    '3rd MsgBox
    MsgBox "Invalid Date"
End If

End Sub


Compile Error : Argument Not Optional.(ReplaceChar)

I appreciated a lot if you can help me.

With Thx,
Celia


0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 1 hour left to enroll

624 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