Formatted date not sent to database, need to keep constant date format

[ambra19]

I am having a problem with saving dates to my database via my VB 6 application.

I have dates automatically put into a textbox when the user clicks "Add notes"

When the button is clicked, the following happens
txtNDate.Text = Format(Date, "yyyy-mm-dd&quot
It shows up in the text box in this manner, however when I save it to my database (the field in the database is simply a text field in Access2000) it goes in as mm/dd/yyyy. How can I fix this?
How can I get this to go into the database no matter what location a person is in ie in US mm/dd/yyyy and I want to use yyyy-mm-dd no matter which computer it is on?
Please help as soon as is possible.
Thanks you very much.
ambra19

 

[SonOfEmidec1100]

I believe what you are doing is correct assuming your update statemant is similar to
UPDATE Table1 SET datefield = '" & txtNDate.Text & "'"
WHERE ...

I suspect you are using # instead of '

cjw

 

[ambra19]

First of all SonOfEmidec1100, thank you very much for your prompt response.

My SQL string has the '
strSQL = "INSERT INTO tblNotes (CallDate, Notes, Pid)" & _
"VALUES('" & _
varNotes(1) & "','" & _
varNotes(2) & "', " & _
varNotes(3) & &quot"

Here is the code for the hitting the Add Button that puts the date on, then the Save (I took out the validation stuff). Upon hitting the Add note button, the date goes into the text box as the format shows. However, once it goes into the database, it still goes in as mm/dd/yyyy.
Can you see anywhere where I might be missing something?

Case 4
txtNDate.Text = Format(Date, "yyyy-mm-dd&quot

Case 5
Dim lintPid As Long

Set objNotes = New clsNotes

With objNotes
.Nid = 0
lintPid = lstContacts.ItemData(lstContacts.ListIndex)
.Pid = lintPid
.CallDate = txtNDate.Text
If IsEmpty(Trim(txtNotes.Text)) = True Then
MsgBox "Please enter a brief note about your conversation."
txtNotes.SetFocus
Exit Sub
Else
.Notes = txtNotes.Text
End If

Call objNotes.Update

Set objNotes = Nothing

End With


Thanks again.

 

[ambra19]

I figured out the error.
Thanks for your help.

 

[SemperFiDownUnda]

strSQL = "INSERT INTO tblNotes (CallDate, Notes, Pid)" & _
"VALUES('" & _
varNotes(1) & "','" & _
varNotes(2) & "', " & _
varNotes(3) & &quot"

varNotes is a variant? If so VB maybe detecting that the string is a valid date and storing it as a date then automatic conversion to standard date format is taking place when being thrown back into a string.

Calldate is a string type column?

 

[ambra19]

SemperFiDownUnda,

I have it as a variant and it is going into my database in the proper date format that i want. now when i bring it back, it's going back to mm/dd/yyyy
is this because i am using a variant?
i am not that great a coder and am using previous examples of work as my guide. So when I bring it back, I bring it back as a variant to fill my list box, where can i put the formatting on so that it shows up as yyyy-mm-dd in my list box?

Set objNotes = New clsNotes

'call retrieve
varNotes = objNotes.Retrieve(lintPid)

'populate date list box
If IsEmpty(varNotes) Then
MsgBox "There are no Notes in the database yet."
Set objNotes = Nothing
Exit Sub
Else
For intCounter = 0 To UBound(varNotes, 2)
lstDate.AddItem varNotes(0, intCounter)
lstDate.ItemData(intCounter) = varNotes(1, intCounter)
Next
End If


Thanks

 

[ambra19]

Thanks to both of you, after all this, I finally realized that I could place format certain pieces of code.
ambra19

 

[ambra19]

you guys were definitely helpful, much appreciated (forgot to put that in my previous post)
ambra19