parameterized update query (1 Viewer)

BennyLinton

Registered User.
Local time
Yesterday, 18:56
Joined
Feb 21, 2014
Messages
263
I have a parameterized update query that is supposed to write in a user provided TackingNumber as long as no Tracking number exists for some records to be updated. However no rows are updated. I have the field set as simpleDate. Ideas


UPDATE Applicants SET Applicants.TrackingNumber = [Enter Tacking Number]
WHERE Applicants.[DateEmailedPrinter] = [Enter Date Sent To Printer]
AND Applicants.TrackingNumber is null;
 

BennyLinton

Registered User.
Local time
Yesterday, 18:56
Joined
Feb 21, 2014
Messages
263
To get it to work I have to type in the EXACT date: 11/13/2018 11:53:55 AM
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:56
Joined
Aug 30, 2003
Messages
36,118
Obviously your date values include a time component. A workaround:

WHERE DateValue(Applicants.[DateEmailedPrinter]) = [Enter Date Sent To Printer]

but it's less efficient since it has to be applied to all records. Hopefully the engine will apply the null test first.
 

BennyLinton

Registered User.
Local time
Yesterday, 18:56
Joined
Feb 21, 2014
Messages
263
Correct I have over 700 records to apply this to and it has to be automated.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:56
Joined
Aug 30, 2003
Messages
36,118
So did that work?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:56
Joined
Aug 30, 2003
Messages
36,118
Is that a date/time field? Could there be Null values? If so, try adding the Nz() function.
 

BennyLinton

Registered User.
Local time
Yesterday, 18:56
Joined
Feb 21, 2014
Messages
263
There are null values. Another part of the program creates the dates. Can that be reprogrammed to cast at the time of creation to a 11/07/2018 format or similar?
 

BennyLinton

Registered User.
Local time
Yesterday, 18:56
Joined
Feb 21, 2014
Messages
263
like UPDATE Applicants set Applicants.EmailedToPrinter = -1, Applicants.DateEmailedPrinter = format(now(),"mm/dd/yyyy") ?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:56
Joined
Aug 30, 2003
Messages
36,118
I was thinking

WHERE DateValue(Nz(Applicants.[DateEmailedPrinter], 0)) = [Enter Date Sent To Printer]
 

Users who are viewing this thread

Top Bottom