Problems saving dates to DB

[policechiefwiggum]

Hi All,
I have a field on a form that captures a project launch date, i have this field in 2 places, in the 1st place its been for a while and works perfectly, and i now want to add it in to another form as part of an update.
On both forms i have set "medium date" as the input mask, and have also set the DB to "medium date" as its input mask.
When i save the date in form a to the db it saves as 01-Jan-01, when i load form b it pulls this value from the db as expected.
if a user changes this my SQL says its saving the value 01-Dec-01.
but when i check the db whats its acctually saved is 00:00:43 and i have no idea why or how to fix it!!!
has anyone got any ideas?
Thanks

 

[dhookom]

Is the form bound? If not, can you at least share your code? It sound like you aren't using delimiters for your dates but I can't tell from what you have provided.

Duane
Hook'D on Access
MS Access MVP

 

[policechiefwiggum]

Sorry, i usually post my code - someone mark me down for a school boy error!!!! - Forms are not bound, code below (as i'm using the form i've commented out the problem code so i can continue without error)

code to validate check box & Field Value:

If Me.ChkLaunchDate.Value = 0 And Me.LaunchDate.Value = "" Then
'MsgBox "Please enter Launch Date or tick box to continue"
'GoTo Finish
'ElseIf Me.ChkLaunchDate.Value = 0 And Me.LaunchDate.Value > "" Then
'MsgBox "Please tick Launch Date tick box to continue"
'GoTo Finish
'End If

Code to build message:

'If Me.ChkLaunchDate.Value = -1 And Me.LaunchDate.Value > "" Then
'LaunchNum = "Launch Date - Captured"
'ElseIf Me.ChkLaunchDate.Value = -1 And Me.LaunchDate.Value = "" Then
'LaunchNum = "Launch Date - To be sourced"
'End If

code to build SQL:

ProcessNum = "UAT Proces Step - " & Me.UATProcess.Value

Dim PACodeSQL As String
Dim OneViewIDSQL As String
Dim DeliveryManagerSQL As String
Dim ProjectManagerSQL As String
Dim LaunchDateSQL As String
Dim UATProcessSQL As String

PACodeSQL = Me.PACode.Value
OneViewIDSQL = Me.Oneview123.Value
DeliveryManagerSQL = Me.DelManager.Value
ProjectManagerSQL = Me.ProjManager.Value
LaunchDateSQL = Me.LaunchDate.Value
UATProcessSQL = Me.UATProcess.Value
Me.LaunchDate.Format = "dd mmm yyyy"
Dim ConfUpdate As String

ConfUpdate = Username & " has acknowledged that the required infromation for project '" & RealNameCode & "' is as follows: " & vbCrLf & PACodeNum & vbCrLf & OneViewNum & vbCrLf & DelManNum & vbCrLf & ProjManNum & vbCrLf & LaunchNum & vbCrLf & ProcessNum


Dim UpdateProjectSQL As String
Set ConnectDatabase = CurrentProject.Connection
Set DatabaseSQL = New ADODB.Command

UpdateProjectSQL = "UPDATE [projects] SET [PACode] = '" & PACodeSQL & "', [OneView] = '" & OneViewIDSQL & "', [DelManager] = '" & DeliveryManagerSQL & "', [PM] = '" & ProjectManagerSQL & "',[LaunchDate] = " & LaunchDateSQL & ",[TypeofTest] = '" & UATProcessSQL & "' WHERE [UATID] = " & NameProjId '
MsgBox (NameProjId & UpdateProjectSQL)

DatabaseSQL.ActiveConnection = ConnectDatabase
DatabaseSQL.CommandText = UpdateProjectSQL
DatabaseSQL.Execute

 

[dhookom]

I don't see any date delimiters in your code. If you attempt to update a date field with 6/7/2010 this will result in 0.00043 which is 6 divided by 7 divided by 2010.

Duane
Hook'D on Access
MS Access MVP

 

[policechiefwiggum]

Thanks for your reply, i'm not sure what you mean! Could you expand a bit?
On the form i have set the format to dd mmm yyyy, will this not do the same thing?

i also have the column in the table set to the same format.

would this explain why its saving at 00:00:43?

 

[dhookom]

Date formats are for display purposes only. The values of the stored value in the table or the value of the controls are floating point numbers.

I would try change to:

Dim strDateDelim as String
strDateDelim = "#"
UpdateProjectSQL = "UPDATE [projects] SET [PACode] = '" & PACodeSQL & "', [OneView] = '" & OneViewIDSQL & "', [DelManager] = '" & DeliveryManagerSQL & "', [PM] = '" & ProjectManagerSQL & "',[LaunchDate] = " & strDateDelim & LaunchDateSQL & strDateDelim & ",[TypeofTest] = '" & UATProcessSQL & "' WHERE [UATID] = " & NameProjId '

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I like to use this function when running a date in a query string.

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

And then when running a date in a query you would use it something like
"..."& SqlDATE(someSring) & "..."
or
"..."& SqlDate(Me.SomeTextBox) & "..."

 

[policechiefwiggum]

Dhookom, thanks for your reply

Dim strDateDelim as StringstrDateDelim = "#"UpdateProjectSQL = "UPDATE [projects] SET [PACode] = '" & PACodeSQL & "', [OneView] = '" & OneViewIDSQL & "', [DelManager] = '" & DeliveryManagerSQL & "', [PM] = '" & ProjectManagerSQL & "',[LaunchDate] = " & strDateDelim & LaunchDateSQL & strDateDelim & ",[TypeofTest] = '" & UATProcessSQL & "' WHERE [UATID] = " & NameProjId '

this works perfectly - when there is a value in the LaunchDate field, if its blank it throws up a runtime error 'Syntax error in date in query expression "##"'
Is there any way i can put the delimiters as part of an if command so they are only added when there is a value?
such as the below for example?

LaunchDateDLim = Me.LaunchDate.Value

If LaunchDateDLim > "" Then
LaunchDateSQL = strDateDelim & LaunchDateDLim & strDateDelim
Else
LaunchDateSQL = ""
End If

 

[MajP]

Using my previous post accounts for this:

launchDateSql = SQLDate(LaunchDateDLim)