Error in SQL Statement

Local time
Today, 22:37
Joined
Apr 29, 2001
Messages
47
I am trying to update a table after a form is printed for members of a club.

The Table I am trying to update has two fields, 1 is membership card printed, and is a yes/no field. The other is membership card printed date, which is a date field.

I have a criteria that I have set to stOption.

Below is the two updates, firstly number 1 works, but the second does not can you tell me which one (2a or 2b) or is both wrong

1. sSql = "UPDATE Member_Detail SET membership_card_print = -1 WHERE
membership_card_print = 0 AND " & stOption

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 AND
" & stOption

Many Thanks
Paul
:( :confused: :confused:
 
Why are you formatting the Date in your SQL strings? What is the format of the membership_card_date field in the Member_Detail table?

This statement:
Code:
WHERE membership_card_date = Is Null
might also be redundant. I think you can just use: WHERE membership_card_date Is Null.
 
Last edited:
dcx693 said:
Why are you formatting the Date in your SQL strings? What is the format of the membership_card_date field in the Member_Detail table?

This statement:
Code:
WHERE membership_card_date = Is Null
might also be redundant. I think you can just use: WHERE membership_card_date Is Null.

I formatted the date because it did not work?
The date format is short date.

If I use the WHERE membership_card_date Is Null, how do I place the stOption on this?
 
Should look something like
DoCmd.RunSQL "UPDATE DISTINCTROW QueryJobs INNER JOIN Jobs ON QueryJobs.JobID = Jobs.JobID SET Jobs.fldDtePrinted = Date()WHERE (((Jobs.fldDtePrinted) Is Null));"
 
Rich said:
Should look something like
DoCmd.RunSQL "UPDATE DISTINCTROW QueryJobs INNER JOIN Jobs ON QueryJobs.JobID = Jobs.JobID SET Jobs.fldDtePrinted = Date()WHERE (((Jobs.fldDtePrinted) Is Null));"

John,
Why the inner join and the query. this is not based on a query?

Below is the complete code:

Dim stDocName As String
Dim stOption As String
Dim chkOption As Boolean
Dim ssql As String
Dim sdSql As Date
chkOption = False

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

If Me.chk_d < 0 Then
If chkOption Then
stOption = stOption & " OR "
chkOption = True
End If
stOption = stOption & "[Surname] Like 'D*' Or [Surname] Like 'E*' Or [Surname] Like 'F*'"
chkOption = True
End If
If Me.chk_g < 0 Then
If chkOption Then
stOption = stOption & " OR "
chkOption = True
End If
stOption = stOption & "[Surname] Like 'G*' Or [Surname] Like 'H*' Or [Surname] Like 'I*'"
chkOption = True
End If
DoCmd.OpenReport stDocName, acPreview, , stOption

ssql = "UPDATE Member_Detail SET membership_card_print = -1 WHERE membership_card_print = 0 AND " & stOption
sdSql = "UPDATE Member_Detail Set membership_card_date = Date() WHERE " & stOption

DoCmd.SetWarnings False
DoCmd.RunSQL ssql
DoCmd.RunSQL sdSql
DoCmd.SetWarnings True

Exit_cmd_print_all_membership_cards_Click:
Exit Sub

Err_cmd_print_all_membership_cards_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_cmd_print_all_membership_cards_Click
End Sub
Paul
 

Users who are viewing this thread

Back
Top Bottom