Running a VBA query that doesn';t null out fields

[dowlingm815]

the following is a vba query that is to null fields, however, the fields are NOT being null out.

any suggestions would be appreciate.

the code is as follows:

Private Sub Update_Pending_Rec_Dates()

On Error GoTo Err_Hndlr

'**********************************************
Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef

'set variable values
Set dbs = CurrentDb
strQueryName = "sql_Update_Pending_Rec_Dates"

'Delete old query first - we want fresh data!
dbs.QueryDefs.Delete strQueryName

'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.


strSQL = "UPDATE ContractImport SET ContractImport.Active = Null, ContractImport.EffectiveDate = Null, ContractImport.ExpirationDate = Null, ContractImport.RenewalsRemaining = Null, ContractImport.[End of Contract Status] = Null WHERE (((ContractImport.[Contract Status]) Like 'pending%'));"


'Create query definition
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)


Update_Pending_Rec_Dates_Exit:
  Exit Sub


Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Update_Pending_Rec_Dates()"
End Sub

[TheSmileyCoder]

Well the code you have there just creates a query, it doesn't run it.

To run SQL directly from VBA you can use:

Docmd.SetWarnings(False)
  Docmd.Runsql strSQL
docmd.SetWarnings(True)

The SetWarnings is just for supressing the warning messages that you are about to update XX records.

[dowlingm815]

added to code, doesn't null the fields...

Private Sub Update_Pending_Rec_Dates()

On Error GoTo Err_Hndlr

'**********************************************
Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef

'set variable values
Set dbs = CurrentDb
strQueryName = "sql_Update_Pending_Rec_Dates"

'Delete old query first - we want fresh data!
dbs.QueryDefs.Delete strQueryName

'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.


strSQL = "UPDATE ContractImport SET ContractImport.Active = Null, ContractImport.EffectiveDate = Null, ContractImport.ExpirationDate = Null, ContractImport.RenewalsRemaining = Null, ContractImport.[End of Contract Status] = Null WHERE (((ContractImport.[Contract Status]) Like 'pending%'));"


'Create query definition
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)

DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQLF

' The SetWarnings is just for supressing the warning messages that you are about to update XX
DoCmd.SetWarnings (True)

Update_Pending_Rec_Dates_Exit:
  Exit Sub


Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Update_Pending_Rec_Dates()"
End Sub

[TheSmileyCoder]

Assumming the code is a direct copy/paste, its because you have a typo.
strSQLF should have been strSQL

DoCmd.RunSQL strSQLF

Please read this article, it will save you (and us) ALOT of headaches!
Require Variable Declaration

[gershwyn]

Another thing to try if your query still won't run after fixing the typo is to use a * as your wildcard character. Your query as written does not work in my version of Access, but the following does:

strSQL = "UPDATE ContractImport SET ContractImport.Active = Null, ContractImport.EffectiveDate = Null, ContractImport.ExpirationDate = Null, ContractImport.RenewalsRemaining = Null, ContractImport.[End of Contract Status] = Null WHERE (((ContractImport.[Contract Status]) Like 'pending*'));"

[dowlingm815]

Another thing to try if your query still won't run after fixing the typo is to use a * as your wildcard character. Your query as written does not work in my version of Access, but the following does:

strSQL = "UPDATE ContractImport SET ContractImport.Active = Null, ContractImport.EffectiveDate = Null, ContractImport.ExpirationDate = Null, ContractImport.RenewalsRemaining = Null, ContractImport.[End of Contract Status] = Null WHERE (((ContractImport.[Contract Status]) Like 'pending*'));"

Thanks that did the trick...