Date Type with VB and Access[Resolved]

I want to take an inputted date from a text box, convert it to a date datatype then send it to my access database.

The data type of the Access field was text and then date, both of which didnt record the correct figures.

My program is sending the data to the db but it is captured incorrectly. i.e. Its being stored as an abstract time (00:00:43) or code(4.99001996007984E-04).

Heres my code:

Dim dtRentalDate as Date

If txtRentalDate.Text = "" Then
dtRentalDate = Now()
Else: dtRentalDate = txtRentalDate.Text
End If


Please help :confused:

Try formatting the input before you send it to the database

ie:

dtRentalDate = Format(dtRentalDate, "mm/dd/yyyy")

If the date type for the field in the db is a Date/Time field then you need to
pass the date wrapped with the "#" character. Also you shouldn't use the
colon in your code as it acts as a carraige return.

VB Code:

Dim dtRentalDate as Date
 
If txtRentalDate.Text = vbNullString Then
    dtRentalDate = Now()
Else
    dtRentalDate = txtRentalDate.Text
End If
 
'Then in you db update code if your passing a SQL string wrap the # chars.
'If your using the .Update method of a rs then dont.

I tried exactly what you suggested but the outcome was unfortunately the same:

The db was showing the value:(4.98753117206983E-04)

Should the text box in VB be formatted to date or just plain text?

Hey Rob, thanks for that! Worked a treat

Mark :thumb:

I would try:

Dim dtRentalDate as Date

If txtRentalDate.Text = vbNullString Then
dtRentalDate = Now()
Else
dtRentalDate = cdate(txtRentalDate.Text)
End If

No prob. Glad to help.
Dont forget to Resolve your thread if its working now. ;)

Matter is resolved

You need to edit your first post and either change the subject icon to the
green check mark or add "Resolved" to the subject. ;)

This will allow it to be seen at the forum view level instead at the post view level.