Problem storing DATE fields that are NULL

[smsmail]

Hello,

When my update sql statement is executed, my program displays message to "Enter Parameter Value" for a DATE field.

The DATE field is null, is there a special way to store date fields that are NULL?

Thanks for your help!

 

[PHV]

my update sql statement
Well, what is the SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[smsmail]

Hi PVH,

Thanks for your help! The date fields on my form can be blank, they are not required fields. My sql code is:

updaterec = "update tblclientmailschedule"
      updaterec = updaterec & " set mailerstatus = mailerstatus.value, [b] ProjMailDate1 = ProjMailDate1.value [/b],"
      updaterec = updaterec & " DistributionMethod = DistributionMethod.value, MethodName = MethodName.value,"
      updaterec = updaterec & " mailingfrequency = mailingfrequency.value, mailingdays = mailingdays.value, nbrmailingday = nbrmailingday.value,"
      updaterec = updaterec & " nbrmailfreq = nbrmailfreq.value, campaigntype = campaigntype.value,"
      updaterec = updaterec & " MailerName = MailerName.value, comments = comments.value, mailertype = mailertype.value, quantity = quantity.value,"
      updaterec = updaterec & " dropcount = dropcount.value, postage = postage.value, postagerate = postagerate.value,"
      updaterec = updaterec & " postoffice = postoffice.value, insert1 = insert1.value, insert2 = insert2.value, emailname = emailname.value,"
      updaterec = updaterec & " listused = listused.value, scheduledtime = scheduledtime.value, jobstage = jobstage.value,"
      updaterec = updaterec & " [b] projmaildate2 = projmailedate2.value [/b],[b] projmaildate3 = projmaildate3.value [/b],[b] actualmaildate1 = actualmaildate1.value [/b],"
      updaterec = updaterec & " [b] actualmaildate2 = actualmaildate2.value [/b], [b]actualmaildate3 = actualmaildate3.value [/b], insert3 = insert3.value "
      updaterec = updaterec & " where clientid = " & Me.ClientID
      updaterec = updaterec & " and jobnumber = '" & Me.JobNumber & "'"
       
Debug.Print updaterec
    
DoCmd.RunSQL updaterec

 

[Andrzejek]

.... and what does your:[tt]

Debug.Print updaterec[/tt]

show?

Have fun.

---- Andy

 

[smsmail]

Hi Andrzejek,

It shows that the field values are NULL.

 

[Andrzejek]

What I am getting to is that your SQL statement in updaterec is something like:
[tt]
update tblclientmailschedule set
mailerstatus = mailerstatus.value,
ProjMailDate1 = ProjMailDate1.value ,
DistributionMethod = DistributionMethod.value,
MethodName = MethodName.value, ...

[/tt]and that's not a valid Update SQL statement

Try something like

updaterec = "update tblclientmailschedule"
updaterec = updaterec & " set mailerstatus = [blue]" & [/blue]mailerstatus.value [blue]& ", [/blue]ProjMailDate1 = [blue]& " [/blue]ProjMailDate1.value [blue]& "[/blue], "
updaterec = updaterec & " DistributionMethod = [blue]" & [/blue]DistributionMethod.value [blue]& "[/blue], MethodName = [blue]" &[/blue]  MethodName.value [blue]& "[/blue], ..."

Have fun.

---- Andy

 

[smsmail]

Andrzejek,

I found the error, the date field names were not correct (I had been staring at the code way too long).

Thank you so much for your help!

smsmail