The same SQLs?

[salvelinus]

I coded this version of a DELETE statement:

strSQL = "DELETE Main.Month FROM Main WHERE Main.Month <> '" & mstrMonth & "';"
    'DoCmd.RunSQL (strSQL)

instead of this query in the DB:

DELETE Main.Month
FROM Main
WHERE (((Main.Month)<>[Which month are you working on?]));

Is the coded SQL correct? I'm suspicious of it, even though it returns the same results.

[alex_read]

Yep! no problems there at all.

Just as a side note, when working with a single table, you don't need to write out Main.Month all the time, generally you only need to put the table name before the column when working with Joins in the statement, you can also use this to accomplish the same thing:

strSQL = "DELETE Month FROM Main WHERE Month <> '" & mstrMonth & "';"

[salvelinus]

Thanks, I know I don't have to include the table name, but I was copying from an existing Access query created in designer view.
The reason I asked is that the queries look the same to me, but the query is called by a form, and I was getting different results from a different query that's based on the results of this query depending on which way I did it.
I use an Inputbox to get mstrMonth in the code query instead of the parameter in the Query Designer style so that I can use mstrMonth later in code.
The results of both queries are the same, just the result of the subsequent query are different.
Since the one query is part of the db and the other is in code, do I need to run some sort of refresh statement on the table? While the code statement returns the correct results, it doesn't seem to actually change the table data.

[alex_read]

Private Sub Command2_Click()
Dim strSQL As String
strSQL = "DELETE FROM Table1"

CurrentDb.Execute strSQL
End Sub

Is what I'm using. Okay there's a couple of things I want to check here -

Firstly is the month column set to a string/text data type?
Secondly, if I understood the last message right, you're opening a recordset in your code & then running the DELETE statement upon this recordset? if so, try calling a recordsetvariable.update after the DELETE call has been made...

[salvelinus]

Here's the code in the sub. Most of it was inherited, I'm just trying to automate it a bit for my own use. I want to get the mstrMonth via Input box instead of by query parameter so I can automatically fill in an email heading and message body that gets sent later via DoCmd.SendObject. The commented out code is mine, the rest was already there. And yes, Month is a text field.

'Get database info and select appropriate month
Private Sub GetDataBase_Click()
Dim StartTime, PauseTime As Integer
Dim strSQL As String
Dim rs As Recordset
Dim db As Database
'On Error GoTo errMsg
 
  If GetData = 0 Then   'call to GetData function in Module1
    Exit Sub            'no database found
  End If
  
  'Wait, presumably to give db time to load before running query
  PauseTime = 5
  StartTime = Timer
  While Timer < StartTime + PauseTime
  Wend
  
'errHandler:
 ' mstrMonth = InputBox("Which month are you working on?", "Select Month")
  
  'If Len(mstrMonth) <> 0 Then
   ' strSQL = "DELETE Main.Month FROM Main WHERE Main.Month <> '" & mstrMonth & "';"
    'DoCmd.RunSQL (strSQL)
    'Exit Sub
  'Else
   ' MsgBox "Please enter a month!", vbCritical, "Missing Month"
    'GoTo errHandler
  'End If
  'Exit Sub
'errMsg:
 ' MsgBox "Error in GetDatabase in OpCheck form"
 
  
  'Choose which month to work on via parameter query
  DoCmd.OpenQuery "MainQuery"
End Sub