Update Query failing! 1

[policechiefwiggum]

Hi All,

Please help!
my Update query is failing:

the query is quite long, and i'm guessing its just a problem with my code somewhere, but i'm self taught in SQL / VBA, so dont know what or where to look for the problem!

Ok, this is the SQL command

UpdateProjectSQL = " UPDATE [projects] SET [UATID] = '" & uatproject & "', [PACode] = '" & PACode & "', [OnTrack] = '" & ontrack & "', [OneView] = '" & oneview & "', [Status] = '" & proj_status & _
"', [Rag] = '" & project_rag & "', [PM] = '" & projectmanger & "', [TPM] = '" & tech_proj_manager & "', [TM] = '" & test_mamanger & "', [UATTester] = '" & assign_res & "', [SuppRes] = '" & _
add_uat & "', [LaunchDate] = '" & launch & "', [ScriptTarget] = '" & script_date & "', [UATStart] = '" & uat_start & ", [UATDuration] = '" & uat_dur & _
"', [UATPercent] = '" & uat_complete & "', [PITStart] = '" & pit_start & "', [PITDuration] = '" & pit_dur & "', [PITPercent] = '" & pit_complete & "', [TestPlanApprove] = '" & _
tp_complete & "', [TestPlanApprove] = '" & test_plan_approve & "', [ScriptsComplete] = '" & scripts_complete & "', [ScriptsApprove] = '" & sign_off & "', [UATStartCh] = '" & uat_st_ch & _
"', [UATComplete] = '" & uat_complete_ch & "', [PITStartCh] = '" & pit_st_ch & "', [PITComplete] = '" & pit_comp_ch & "', [ExitReport] = '" & exit_report & "', [UATSignoff] = '" & _
uat_signoff & "', [TypeofTest] = '" & type_of_test & "', [AreaofBusiness] = '" & area_of_bus & "', [NewUpdate] = '" & new_update & "', [OldUpdate] = '" & prev_update & "'"

i've Dim'd all the form elements like so:

Dim new_update As String
Dim prev_update As String
Dim pit_st_ch As String
Dim pit_comp_ch As String
 
If Me.uatproject.Value >= "" Then
uatproject = " = " & Me.uatproject.Value
Else
uatproject = " IsNull"
End If
 
If Me.pa_code.Value >= "" Then
PACode = " = " & Me.pa_code.Value
Else
pa_code = " is Null "
End If
 
If Me.ontrack.Value >= "" Then
ontrack = " = " & Me.ontrack.Value
Else
ontrack = " is Null "
End If

(Obviously theres a lot more!)

when i click on my "submit" button i'm getting a debug error:

 

[Golom]

Could be

[UATStart] = '" & uat_start & "[red][b]'[/b][/red],

You seem to be missing a single quote.

 

[policechiefwiggum]

Thanks Golom, thats stopped that error!

now i'm getting another "Data type Mismatch in criteria expression" i'm assuming that either A) a value is not being picked up correctly or B) i've set a field to be a certain data type and what the form is trying to insert is different?

 

[Golom]

Further, expressions like

[UATDuration] = 'Is NULL'

are incorrect syntax.

In an UPDATE statement, if you want to set a field to NULL, then the syntax is

[UATDuration] = NULL

IS NULL tests if the field contains NULL. It does not assign a NULL to it.

 

[Golom]

You are probably getting the mismatch because something like

[UATDuration] = 'Is NULL'

is attempting to assign the character string 'Is NULL' to the field. If it isn't a text or memo field then you will get a type mismatch.

 

[policechiefwiggum]

Thanks Again for your help Golom,

Just to clarify i've got it right, if its a text field or memo then

[UATDuration] = 'Is Null'

is correct, if its anything else then it needs to be

[UATDuration] = Null

is that correct?

 

[Golom]

Not exactly ...

If you want the text string "Is NULL" stored in a text or Memo field then

[UATDuration] = 'Is Null'

Will do that. If, however, you want a NULL value stored in the field (regardless of data type) then

[UATDuration] = Null

is what you want.

Note that the definition of the field in the table may cause an attempt to set the field to NULL to fail. Specifically, if the "Required" property is "Yes" or it is an Indexed field that does not permit NULLs then an attempt to set it to NULL will fail.

 

[policechiefwiggum]

OK, i've tried to change them all to

[UATDuration] = Null

but now get an "invalid use of null" error. i can't see any tables that are set to required, and i've checked all the forms to make sure there is no validation that differs from the data in the field.

i guess my next question would be, is there a better (or preferably easier!) way to write this query?

also, do i need a WHERE as part of the string?

Thanks again for your help Golom