Only into Blank Field. 1

[lars7]

Hi,
I need some help with coding so that I can put a date in a field "datetopayroll" but only if the field is blank. I'm sending an email grouped by a code number and when it is sent it will put a date in a field. But all records with the same code number might not be available to get sent at the same time so if a group of the code numbers gets sent today I don't want todays date to go into the field of the group of the same code numbers that got sent yesterday. I hope this is clear but I will be happy to explain further. Here is the code that I use at the moment to add the date.

SQL = "UPDATE tbltruststaff " & _
"SET datetopayroll = date()WHERE Code IN (" & strCode & ")" _

 

[PHV]

Something like this ?
SQL = "UPDATE tbltruststaff " _
& "SET datetopayroll = Date() " _
& "WHERE Code IN (" & strCode & ") " _
& "AND datetopayroll Is Null"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Dor100]

Well, it does seem like you're likely looking for just "WHERE Code IN (" & strCode & ") And datetopayroll is null", but only as long as every record 'currently' containing the right code number(s) that also happens to have a blank datetopayroll field truly is available for this immediate use. (Ordinarily, that might be something of a tautology, but not in this case ). Hope this is an accurate addressing of your problem.

 

[lars7]

Thanks PHV,
It worked great.

 

[Dor100]

Looks like PHV's post updated to the thread while I was still editing this one.

 

[Dor100]

Wow - even lars7 updated to the thread while I was still writing the one above .

 

[lars7]

Hi Dor,
I have the query so that it only calls a code if the "datetopayroll" field is null so those will be the only ones that will receive the new date now thanks to PHV.
What does tautology mean.