UPDATE query won't update

[pnmng49]

Hi everyone,

This Access 2000 update query (in red) doesn't update the table. Can you help? I have posted the results from Immediate Window after end of sub.

Private Sub cmdCreatePurgeLetters_Agency_Click()
On Error GoTo Err_cmdCreatePurgeLetters_Agency_Click

DoCmd.SetWarnings (WarningsOff)

'DoCmd.RunSQL "Delete * from tblPurgeLettersTable;"
Dim strTableName As String
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strMonth As String
'Get table name from user
strTableName = InputBox("What month/year are these Reprint/Purge letters for? EX: jan09")
'Build SQL string using SELECT INTO to create a new table
strSQL = "SELECT [AGENCY],[ADDR1],[ADDR2],[CITY],[ZIP],[DueDate], [TAC],[ChiefAdministrator]INTO tblPurgeLettersTable FROM "
strSQL = strSQL + strTableName
strSQL = strSQL + " WHERE " + strTableName
strSQL = strSQL + ".[IT_PurgeDate]is not null AND "
strSQL = strSQL + strTableName + ".[ExtendedDueDate]is null;"
DoCmd.RunSQL (strSQL)
'Add a column to table
DoCmd.RunSQL ("ALTER TABLE tblPurgeLettersTable ADD COLUMN [Month] BYTE;")
'Assign table name to string to use in Update Query
strMonth = strTableName
Debug.Print strMonth
'Build Update Query and run
strSQL2 = "UPDATE tblPurgeLettersTable SET tblPurgeLettersTable.[Month] = "
strSQL2 = strSQL2 + strMonth
strSQL2 = strSQL2 + " WHERE tblPurgeLettersTable.[Month] is null;"
DoCmd.RunSQL (strSQL2)
Debug.Print strSQL2

'Create an instance of Word and open file specified by variable strFilePath
Dim objword As Object
Dim strFilePath As String
strFilePath = "C:\PurgeLetter-Agency.doc"
'Debug.Print strFilePath


Set objword = CreateObject("Word.Application")

objword.Visible = True
objword.Activate
objword.Documents.Open strFilePath

'DoCmd.RunSQL "Delete * from tblPurgeLettersTable;"

Exit_cmdCreatePurgeLetters_Agency_Click:
Exit Sub

Err_cmdCreatePurgeLetters_Agency_Click:
MsgBox Err.Description
'MsgBox "Action cancelled"
Resume Exit_cmdCreatePurgeLetters_Agency_Click

DoCmd.SetWarnings (WarningsOn)

End Sub

jan10
UPDATE tblPurgeLettersTable SET tblPurgeLettersTable.[Month] = jan10 WHERE tblPurgeLettersTable.[Month] is null;

 

[jal]

Did you enclose your date in pound signs?

#01/05/2010#

Also, do a MsgBox(SQL) and then hit Control-C to copy and paste your actual query here for others to examine it.

 

[jal]

What error msg are you getting?

 

[JANR]

UPDATE tblPurgeLettersTable SET tblPurgeLettersTable.[Month] = 'jan10' WHERE tblPurgeLettersTable.[Month] is null;

You need single quotes around the string value.

Try:

'Build Update Query and run
[COLOR=black]strSQL2 = "UPDATE tblPurgeLettersTable SET tblPurgeLettersTable.[Month] = "
strSQL2 = strSQL2 &[COLOR=red] '" & strMonth & "'
[/COLOR]strSQL2 = strSQL2 + " WHERE tblPurgeLettersTable.[Month] is null;"
[/COLOR]

Also altering a table to insert new column's at run time shoulden't bee done, you have some normalization issues.

JR

 

[G81]

Did you enclose your date in pound signs?

#01/05/2010#

Also, do a MsgBox(SQL) and then hit Control-C to copy and paste your actual query here for others to examine it.

I 2nd that.

Also if its a string field, enclose your sql clause in '

For example if strmonth = July

then:

strSQL2 = strSQL2 & "'" & strMonth & "'"

ps those are ' enclosed in "

 

[pnmng49]

Thank you, G81. You were right, it required this syntax:
strSQL2 = strSQL2 & "'" & strMonth & "'" because strMonth=JULY
Thanks to all that replied.
Regarding normalization- this is a temp table that I use to hold data for a Word mail merge. This temp table gets overwritten the next month when its time to create purge letters for a different month. Does that mitigate the poor design problem?
If not, please help me understand a better design. Thanks again.

 

[boblarson]

And next time you post code (especially long sets of code) please use CODE TAGS so that it is easier to read.