Error Updating A Date Field

  • Thread starter Thread starter Paul R
  • Start date Start date
P

Paul R

I am trying to update a field in a table with the date() (today). I have
tried to use the following code but the code to update the true / false
field works, the code for the date filed does not?

Paul

Dim sSql
Dim sdSql As Date
Dim stOption As String
Dim chkOption As Boolean

stDocName = "rpt_membership_card"
If Me.chk_a < 0 Then
stOption = "[Surname] Like 'A*' Or [Surname] Like 'B*' Or
[Surname] Like 'C*'"
chkOption = True
End If

DoCmd.OpenReport stDocName, acPreview, , stOption

' update the print card field to Yes

sSql = "UPDATE Member_Detail SET membership_card_print = -1 WHERE
membership_card_print = 0 AND " & stOption
' update the date print card field to Today

sdSql = "UPDATE Member_Detail Set membership_card_date = Date() HERE
membership_card_print = 0 AND " & stOption
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.RunSQL sdSql
 
First, change:

sdSql = "UPDATE Member_Detail Set membership_card_date = Date() HERE
membership_card_print = 0 AND " & stOption

to:

sdSql = "UPDATE Member_Detail Set membership_card_date = " & format$
(date(), "\#mm/dd/yyyy\#") & " WHERE membership_card_print = 0 AND " &
stOption

or equivalently (but better formatted & easier to read):

sdSql = "UPDATE Member_Detail" & _
" SET membership_card_date = " & format$ (date(),
"\#mm/dd/yyyy\#") & _
" WHERE membership_card_print = 0 AND " & stOption

Second, you do realize that you can update several fields in the same
record, simulatenously? :

UPDATE MyTable
SET Field1 = ... ,
Field2 = ...,
etc.
WHERE ...

HTH,
TC
 
TC,
Thanks

if I wish to use this part of your code:
sdSql = "UPDATE Member_Detail" & " SET membership_card_date = " & format$
(date(),"\#mm/dd/yyyy\#") & " WHERE membership_card_print = 0 AND " &
stOption
and I did not want to use the "WHERE membership_card_print = 0" part how do
I construct the code?

Paul


TC said:
First, change:

sdSql = "UPDATE Member_Detail Set membership_card_date = Date() HERE
membership_card_print = 0 AND " & stOption

to:

sdSql = "UPDATE Member_Detail Set membership_card_date = " & format$
(date(), "\#mm/dd/yyyy\#") & " WHERE membership_card_print = 0 AND " &
stOption

or equivalently (but better formatted & easier to read):

sdSql = "UPDATE Member_Detail" & _
" SET membership_card_date = " & format$ (date(),
"\#mm/dd/yyyy\#") & _
" WHERE membership_card_print = 0 AND " & stOption

Second, you do realize that you can update several fields in the same
record, simulatenously? :

UPDATE MyTable
SET Field1 = ... ,
Field2 = ...,
etc.
WHERE ...

HTH,
TC


Paul R said:
I am trying to update a field in a table with the date() (today). I have
tried to use the following code but the code to update the true / false
field works, the code for the date filed does not?

Paul

Dim sSql
Dim sdSql As Date
Dim stOption As String
Dim chkOption As Boolean

stDocName = "rpt_membership_card"
If Me.chk_a < 0 Then
stOption = "[Surname] Like 'A*' Or [Surname] Like 'B*' Or
[Surname] Like 'C*'"
chkOption = True
End If

DoCmd.OpenReport stDocName, acPreview, , stOption

' update the print card field to Yes

sSql = "UPDATE Member_Detail SET membership_card_print = -1 WHERE
membership_card_print = 0 AND " & stOption
' update the date print card field to Today

sdSql = "UPDATE Member_Detail Set membership_card_date = Date() HERE
membership_card_print = 0 AND " & stOption
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.RunSQL sdSql
Click to expand...
Click to expand...
 
Tc I have tried your suggestion below, but get errors, what I am doing wrong?

sdSql = Update Member_Detail
Set membership_card_print = -1
Set membership_card_date = Format$(Date, "\#dd/mm/yyyy\#")
WHERE membership_card_print = 0 And " & stOption"

TC said:
First, change:

sdSql = "UPDATE Member_Detail Set membership_card_date = Date() HERE
membership_card_print = 0 AND " & stOption

to:

sdSql = "UPDATE Member_Detail Set membership_card_date = " & format$
(date(), "\#mm/dd/yyyy\#") & " WHERE membership_card_print = 0 AND " &
stOption

or equivalently (but better formatted & easier to read):

sdSql = "UPDATE Member_Detail" & _
" SET membership_card_date = " & format$ (date(),
"\#mm/dd/yyyy\#") & _
" WHERE membership_card_print = 0 AND " & stOption

Second, you do realize that you can update several fields in the same
record, simulatenously? :

UPDATE MyTable
SET Field1 = ... ,
Field2 = ...,
etc.
WHERE ...

HTH,
TC


Paul R said:
I am trying to update a field in a table with the date() (today). I have
tried to use the following code but the code to update the true / false
field works, the code for the date filed does not?

Paul

Dim sSql
Dim sdSql As Date
Dim stOption As String
Dim chkOption As Boolean

stDocName = "rpt_membership_card"
If Me.chk_a < 0 Then
stOption = "[Surname] Like 'A*' Or [Surname] Like 'B*' Or
[Surname] Like 'C*'"
chkOption = True
End If

DoCmd.OpenReport stDocName, acPreview, , stOption

' update the print card field to Yes

sSql = "UPDATE Member_Detail SET membership_card_print = -1 WHERE
membership_card_print = 0 AND " & stOption
' update the date print card field to Today

sdSql = "UPDATE Member_Detail Set membership_card_date = Date() HERE
membership_card_print = 0 AND " & stOption
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.RunSQL sdSql
Click to expand...
Click to expand...
 
(1) My example was just the SQL part. If you want to put the SQL into
a string, you have to do that properly. So, to put this (dummy) SQL
into a string:

UPDATE BLAH
SET XYZ

you would have to write this code:

sdSql = "UPDATE BLAH" & _
" SET XYZ"

(2) You have not followed the syntax exactly. You've skipped a comma,
and added a second SET keyword. Go back & compare the two, I'm sure
that you will spot the differences.

Cheers,
TC


Paul R said:
Tc I have tried your suggestion below, but get errors, what I am doing
wrong?

sdSql = Update Member Detail
Set membership card print = -1
Set membership card date = Format$(Date, "\#dd/mm/yyyy\#")
WHERE membership card print = 0 And " & stOption"

TC said:
First, change:

sdSql = "UPDATE Member Detail Set membership card date = Date() HERE
membership card print = 0 AND " & stOption

to:

sdSql = "UPDATE Member Detail Set membership card date = " & format$
(date(), "\#mm/dd/yyyy\#") & " WHERE membership card print = 0 AND " &
stOption

or equivalently (but better formatted & easier to read):

sdSql = "UPDATE Member Detail" &
" SET membership card date = " & format$ (date(),
"\#mm/dd/yyyy\#") &
" WHERE membership card print = 0 AND " & stOption

Second, you do realize that you can update several fields in the same
record, simulatenously? :

UPDATE MyTable
SET Field1 = ... ,
Field2 = ...,
etc.
WHERE ...

HTH,
TC


Paul R said:
I am trying to update a field in a table with the date() (today). I have
tried to use the following code but the code to update the true / false
field works, the code for the date filed does not?

Paul

Dim sSql
Dim sdSql As Date
Dim stOption As String
Dim chkOption As Boolean

stDocName = "rpt membership card"
If Me.chk a < 0 Then
stOption = "[Surname] Like 'A*' Or [Surname] Like 'B*' Or
[Surname] Like 'C*'"
chkOption = True
End If

DoCmd.OpenReport stDocName, acPreview, , stOption

' update the print card field to Yes

sSql = "UPDATE Member Detail SET membership card print = -1 WHERE
membership card print = 0 AND " & stOption
' update the date print card field to Today

sdSql = "UPDATE Member Detail Set membership card date =
Click to expand...
Date()
HERE
membership card print = 0 AND " & stOption
DoCmd.SetWarnings False
DoCmd.RunSQL sSql
DoCmd.RunSQL sdSql
Click to expand...
Click to expand...
--
Click to expand...
 
TC
I have changed the sql but still get errors, I want to try it on two line
at the moment and then once it is working try your suggestion of multipl
updates

so below is the two updates I am trying to do the first works, but th
second does not. can you tell me which ones is correct or if I have it wron
still

1. sSql = "UPDATE Member_Detail SET membership_card_print = -1 WHER
membership_card_print = 0 AND " & stOptio

2a. sdSql = "UPDATE Member_Detail" & " SET membership_card_date = "
Format$(Date, "\#dd/mm/yyyy\#"
2b sdSql = "UPDATE Member_Detail Set membership_card_date = "
Format$(Date, "\#dd/mm/yyyy\#") & " WHERE membership_card_date = Is Null AN
" & stOptio

Many Thank
Pau
 
Back
Top