7 Replies - 1667 Views - Last Post: 29 November 2012 - 01:12 PM

#1 404notfound  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 01-November 12

Why is my date data going into my AccessDB as a date?

Posted 28 November 2012 - 09:29 PM

I've been working on this all evening. Using the code below, instead of a date being inserted,(11/28/12) its a time (please see attached image).
I've tried doing it without the if statement and the variable, but the same thing occurs. Help?

Dim DateUpdate As String

        If IsDate(dtpPaymentDate.Text) Then
            DateUpdate = dtpPaymentDate.Text
        Else
            DateUpdate = Date.Today
        End If

        sql = "UPDATE tblOutstanding SET AmountPaid = " & CInt(Outstanding.txtAmountPaid.Text) & _
            " WHERE InvoiceNumber = " & CInt(txtInvNumber.Text) & ";"

        sql1 = "UPDATE tblOutstanding SET BalanceDue = " & CInt(Outstanding.txtBalDue.Text) _
            & " WHERE InvoiceNumber = " & CInt(txtInvNumber.Text) & ";"

        sql2 = "UPDATE tblOutstanding SET LastPayment = " & DateUpdate _
            & " WHERE InvoiceNumber = " & txtInvNumber.Text & ";"

        Dim UpdateAmountPaid As New OleDb.OleDbCommand(sql, con)
        Dim UpdateBalanceDue As New OleDb.OleDbCommand(sql1, con)
        Dim UpdateLastPayment As New OleDb.OleDbCommand(sql2, con)



        con.Open()
        MsgBox(con.State.ToString)
        UpdateAmountPaid.ExecuteNonQuery()
        UpdateBalanceDue.ExecuteNonQuery()
        UpdateLastPayment.ExecuteNonQuery()

        con.Close()


        dgPopulate()
        MessageBox.Show("Database Updated")
        Me.Close()

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: Why is my date data going into my AccessDB as a date?

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5464
  • View blog
  • Posts: 11,740
  • Joined: 02-June 10

Re: Why is my date data going into my AccessDB as a date?

Posted 29 November 2012 - 09:06 AM

I'm not seeing a problem. What am I missing?

Quote

instead of a date being inserted,(11/28/12) its a time (please see attached image).


Last line, ID 14, third column... Looks like its the data 11/28/2012 to me.

I don't even see an entry that looks like a time anywhere.
Was This Post Helpful? 0
  • +
  • -

#3 404notfound  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 01-November 12

Re: Why is my date data going into my AccessDB as a date?

Posted 29 November 2012 - 11:01 AM

Sorry for not clarifying, that date is correct. Column 6, LastPayment. Time values are being inserted.
Was This Post Helpful? 0
  • +
  • -

#4 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5464
  • View blog
  • Posts: 11,740
  • Joined: 02-June 10

Re: Why is my date data going into my AccessDB as a date?

Posted 29 November 2012 - 11:11 AM

You're inserting this into a pre-existing spreadsheet, right?
Are you sure you don't have that column format set to time?
I'd bet it is about how the spreadsheet has its formatting set and not the code.


Hmmm... Thinking...

06 DateUpdate = Date.Today

Since in .NET DateTime object include times maybe this is confusing the spreadsheet. maybe format it to just the date first

06 DateUpdate = Date.Today.ToString("DDmmmYYYY")
Was This Post Helpful? 1
  • +
  • -

#5 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 443
  • View blog
  • Posts: 3,061
  • Joined: 12-January 10

Re: Why is my date data going into my AccessDB as a date?

Posted 29 November 2012 - 11:13 AM

looks liek you are telling it to use date


Quote

sql2 = "UPDATE tblOutstanding SET LastPayment = " & DateUpdate _



Quote

Else

06 DateUpdate = Date.Today

Was This Post Helpful? 0
  • +
  • -

#6 404notfound  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 01-November 12

Re: Why is my date data going into my AccessDB as a date?

Posted 29 November 2012 - 11:47 AM

I was tired when I posted this. I meant to write why ISN'T my data going in as a date.
Was This Post Helpful? 0
  • +
  • -

#7 404notfound  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 01-November 12

Re: Why is my date data going into my AccessDB as a date?

Posted 29 November 2012 - 12:33 PM

tlhIn`toq,
Yes its going into a predesigned spreadsheet. Its set to a value of datetime. Is there a way I could convert that to get it from the datetimepicker?
Was This Post Helpful? 0
  • +
  • -

#8 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 443
  • View blog
  • Posts: 3,061
  • Joined: 12-January 10

Re: Why is my date data going into my AccessDB as a date?

Posted 29 November 2012 - 01:12 PM

instead of date use datetime


Quote

Date.Today

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1