Trying to update value in a table

[Guest]

I have the following procedure in which I am trying to update a text field in
tblSys after identifying the current date in the table is more than 30 days
old. The code runs but the value updated in the table is not the text
equivalent of the new date I am trying to add. How can I convert the the
variable strNewdate from a date to a text value so the value in tblSys is
text?

Thank you.

Private Sub Form_Close()
Dim datCurrDate As Date, strSQLNewDate As String, strNewDate
Dim strResetsysTblDate As String
Dim cnnSysTblDate As ADODB.Connection, rsNewDate As Recordset
Dim strSQLUpdateDate As String

datCurrDate = Date

'Gets text date value from tblSys
strResetsysTblDate = DLookup("[ReturnVal]", "tblSys", "[Comparison] =
""Backup""")

'Converts text in sysTbl to date value
strResetsysTblDate = DateValue(strResetsysTblDate) + 30

If datCurrDate > strResetsysTblDate Then
MsgBox "It has been more than 30 days since your last backup " &
vbNewLine & _
"Please consider doing a backup after this form closes " &
vbNewLine & vbNewLine & _
"You will not be reminded for another 30 days "
End If

'assign new date using date variable + 30
strNewDate = Date + 30

'convert strNewDate to a string???????????????

Set cnnSysTblDate = Application.CurrentProject.Connection
Set rsNewDate = New ADODB.Recordset

'open recordset in backend db
rsNewDate.Open Source:="tblSys", ActiveConnection:=cnnSysTblDate, _
CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic

strSQLUpdateDate = "UPDATE tblSys SET [ReturnVal] = " & strNewDate & " WHERE
[Comparison] = 'Backup'"

End Sub

 

[Stefan Hoffmann]

hi Billy,

I have the following procedure in which I am trying to update a text field in
tblSys after identifying the current date in the table is more than 30 days
old. The code runs but the value updated in the table is not the text
equivalent of the new date I am trying to add. How can I convert the the
variable strNewdate from a date to a text value so the value in tblSys is
text?

See comments in your code.

Private Sub Form_Close()
Dim datCurrDate As Date, strSQLNewDate As String, strNewDate

strNewDate seems to be declared as Variant.

Dim strResetsysTblDate As String
Dim cnnSysTblDate As ADODB.Connection, rsNewDate As Recordset
Dim strSQLUpdateDate As String

datCurrDate = Date

'Gets text date value from tblSys
strResetsysTblDate = DLookup("[ReturnVal]", "tblSys", "[Comparison] =
""Backup""")

I assume your tblSys is Key-Value table.

'Converts text in sysTbl to date value
strResetsysTblDate = DateValue(strResetsysTblDate) + 30

Then why are you storing it as a string?

If datCurrDate > strResetsysTblDate Then

Use

datCurrDate > CDate(strResetsysTblDate)

for a date comparision, otherwise you are comparing strings.

MsgBox "It has been more than 30 days since your last backup " &
vbNewLine & _
"Please consider doing a backup after this form closes " &
vbNewLine & vbNewLine & _
"You will not be reminded for another 30 days "
End If

'assign new date using date variable + 30
strNewDate = Date + 30

strNewDate is declared as Variant due to the missing type in the Dim
section.

'convert strNewDate to a string???????????????

Set cnnSysTblDate = Application.CurrentProject.Connection
Set rsNewDate = New ADODB.Recordset

'open recordset in backend db
rsNewDate.Open Source:="tblSys", ActiveConnection:=cnnSysTblDate, _
CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic

You are not using this recordset in the code furthermore, so why do you
open it?

strSQLUpdateDate = "UPDATE tblSys SET [ReturnVal] = " & strNewDate & " WHERE
[Comparison] = 'Backup'"

See http://www.mvps.org/access/datetime/date0005.htm for details. You
have make it a valid SQL statement.

Here is also

CurrentDb.Execute strSQLUpdateDate, dbFailOnError

missing.

End Sub

mfG
--> stefan <--

 

[Guest]

Stefan - Thanks for the comments I have fixed them. Regarding the table, I
have a two table field that I am using to change defaults for some form
properties after the form has been used the first time. The field that I need
the texted date field holds text data.

Can I convert a date to a text value?

hi Billy,

I have the following procedure in which I am trying to update a text field in
tblSys after identifying the current date in the table is more than 30 days
old. The code runs but the value updated in the table is not the text
equivalent of the new date I am trying to add. How can I convert the the
variable strNewdate from a date to a text value so the value in tblSys is
text?

See comments in your code.

Private Sub Form_Close()
Dim datCurrDate As Date, strSQLNewDate As String, strNewDate

strNewDate seems to be declared as Variant.

Dim strResetsysTblDate As String
Dim cnnSysTblDate As ADODB.Connection, rsNewDate As Recordset
Dim strSQLUpdateDate As String

datCurrDate = Date

'Gets text date value from tblSys
strResetsysTblDate = DLookup("[ReturnVal]", "tblSys", "[Comparison] =
""Backup""")

I assume your tblSys is Key-Value table.

'Converts text in sysTbl to date value
strResetsysTblDate = DateValue(strResetsysTblDate) + 30

Then why are you storing it as a string?

If datCurrDate > strResetsysTblDate Then

Use

datCurrDate > CDate(strResetsysTblDate)

for a date comparision, otherwise you are comparing strings.

MsgBox "It has been more than 30 days since your last backup " &
vbNewLine & _
"Please consider doing a backup after this form closes " &
vbNewLine & vbNewLine & _
"You will not be reminded for another 30 days "
End If

'assign new date using date variable + 30
strNewDate = Date + 30

strNewDate is declared as Variant due to the missing type in the Dim
section.

'convert strNewDate to a string???????????????

Set cnnSysTblDate = Application.CurrentProject.Connection
Set rsNewDate = New ADODB.Recordset

'open recordset in backend db
rsNewDate.Open Source:="tblSys", ActiveConnection:=cnnSysTblDate, _
CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic

You are not using this recordset in the code furthermore, so why do you
open it?

strSQLUpdateDate = "UPDATE tblSys SET [ReturnVal] = " & strNewDate & " WHERE
[Comparison] = 'Backup'"

See http://www.mvps.org/access/datetime/date0005.htm for details. You
have make it a valid SQL statement.

Here is also

CurrentDb.Execute strSQLUpdateDate, dbFailOnError

missing.

End Sub

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Billy,

Stefan - Thanks for the comments I have fixed them. Regarding the table, I
have a two table field that I am using to change defaults for some form
properties after the form has been used the first time. The field that I need
the texted date field holds text data.

Can I convert a date to a text value?

You can always use CStr(SomeValue) to get a string. But be aware that
using an explicit format gives you more control:

Global Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#"
Global Const JetDateFmt = "\#mm\/dd\/yyyy\#"
Global Const JetTimeFmt = "\#hh\:nn\:ss\#"

Format$(Now, JetDateTimeFmt)


mfG
--> stefan <--