CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2011
    Posts
    3

    Convert date to number with data reader using access database in VB

    I am new to VB. I have been trying solve the following problem since yesterday but not able to.

    I have table structure as follows:

    CustLocID Auto Number Size
    CustomerID Number
    LocID Number
    DateID Number
    RowID Text 5

    I am trying to store string that actually contains a date into the above table. I was trying to convert that into number. I have extracted the month, day and year values from the actual date string and contactenated those. Now I am trying to insert that into dateID value of above table.

    Following is the code for extracting

    strEventDateString = strEventDate.Substring(0, 2) & strEventDate.Substring(3, 2) & strEventDate.Substring(6, 4)

    Now I am trying insert this into the above table with data reader. Here I was trying to convert it into Integer and inserting.

    I tried using CInt, VAL function but it gives me overflow error. If I remove CInt, it stores the value as zero. How do I really store this date value as a number in the table.

    Any help on this is really appreciated.

    Thanks
    ylsv

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Convert date to number with data reader using access database in VB

    Might want to learn to SEARCH before asking a question (asked THOUSANDS of times) [check the date]

    Code:
    Option Explicit
    
    Private Sub Form_Load()
    Dim dteDate     As Date
    Dim adoRec      As ADODB.Recordset
        dteDate = CDate("7 March 2005 10:04:00AM")
        Set adoRec = GetRecordset(dteDate)
        With adoRec
            Do While Not .EOF
                'your code goes here
                 MsgBox .Fields(0)
                .MoveNext
            Loop
            .Close
        End With
        Set adoRec = Nothing
    End Sub
    
    Private Function GetRecordset(ByVal pdteSearchDate As Date) As ADODB.Recordset
    Dim strSQL      As String
    Dim adoRec      As ADODB.Recordset
        strSQL = "SELECT * "
        strSQL = strSQL & "FROM AS_Createsched "
        strSQL = strSQL & "WHERE AS_Createsched.CSIN_dates = #" & Format$(pdteSearchDate, "d mmm yyyy") & "# "
        strSQL = strSQL & "AND HOUR(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "hh") & " "
        strSQL = strSQL & "AND MINUTE(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "nn") & " "
        Set adoRec = New ADODB.Recordset
        adoRec.Open strSQL, GetConnString, adOpenForwardOnly, adLockReadOnly
        Set GetRecordset = adoRec
        Set adoRec = Nothing
    End Function
    
    Private Function GetConnString() As String
        GetConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db4.mdb;Persist Security Info=False"
    End Function
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured