DateAdd in Query Criteria to display a date in a field 2 days prior to the date in another field

[MPXJohn]

Right justified. The code returns nothing. Here is what is supposed to happen. PW: Criteria "Is Null" is working properly. StartDate: Is just a date for example 1/29/2014. PurchaseDate: This is currently a black field that I would like to populate with a date that is 2 days before the date in the StartDate field. That is what my understanding the DateAdd criteria was supposed to do, but I am thinking it is not. Based on "rpeare's" reply. Which I will respond to next.

[MPXJohn]

So, what you are saying is that I would put this... dateadd("d", -2, cdate(production_schedule.startdate))...in the criteria for PurchaseDate? I am not that familiar with reading the SQL code.

[JoeM]

So, if the PurchaseDate field is blank, you do NOT want to put it in your Criteria! That will not return anything at all.

If you goal is to populate a blank PurchaseDate field with a value that is a calculation based on another field, you would need to use an Update Query to do that.

In rpeare's response, he shows you how to return the records that you want and return a calculated field that has the value you want. But it will not actually place it in the PurchaseDate field. Only an Update Query will actually update the value of an existing table field like that.

[MPXJohn]

That is exactly what I am trying to do. Guess I have some more reading to do. Thanks for everyone's help.

[JoeM]

It should just be a matter of taking the criteria that rpears gave you in his SQL code, and updating the PurchaseDate field with the calculation.
The SQL Code could look something like this:

UPDATE Production_Schedule
SET PurchaseDate = StartDate - 2
WHERE (((Production_Schedule.PW) Is Null) AND ((Production_Schedule.StartDate)>#1/1/2014#));

If you paste this in the SQL View of the Query Builder and save it, it should work.

If you do a Google Search on "Access Update Query", you can find lots of information, tutorials, and videos about creating Update Queries using the Query Builder.

[rpeare]

What are you trying to do because your descriptions are not clear.

If you are trying to update all the records where the field PW is null and you are trying to update the PURCHASEDATE field to be two days prior to the STARTDATE that is one thing.

If you are trying to display a calculated value ONLY, for the purposes of reporting or display on a form etc, that is an entirely different thing.

Let's assume this is your original data

PK	PW	StartDate	PurchaseDate
1	X	1/1/2014
2		1/5/2014
3	Y	1/6/2014
4		1/15/2014

This is the data as it exists in your table.

If you want to update the PURCHASEDATE to be 2 days prior to the start date you would use something like:

UPDATE tblTEST SET PurchaseDate = dateadd("d", -2, Startdate) WHERE PW Is Null

after you run this query your dataset would look like this:

PK	PW	StartDate	PurchaseDate
1	X	1/1/2014
2		1/5/2014	1/3/2014
3	Y	1/6/2014
4		1/15/2014	1/13/2014