-
January 28th, 2011, 09:48 PM
#1
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
-
January 28th, 2011, 09:58 PM
#2
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|