Base: Change DAY in select records

[JoeCastor]

In my table MemberMaster, my goal is to change the DAY in the DateJoined date to 01 if the current DAY = 15, run as a SQL Command statement. The original data excluded a DAY (only had month and year from an XL file). I forced DAY=15, but changing to 01 will solve several testing issues for reporting. I have tried the following code, but apparently I’m way off the mark, as I get a schema error on t1.
Any help will be appreciated.
Thanks in advance, Joe

UPDATE "MemberMaster"SET "DateJoined" = (SELECT "t1"."DateJoined" ( DateAdd( 'dd', - 14, "t1"."DateJoined") )FROM "MemberMaster" AS "t1"WHERE "t1".MemberID"="MemberMaster"."MemberID"AND (DAY, "t1"."DateJoined" ) =15

[Ratslinger]

Hello,

This works for me as tested:

UPDATE "MemberMaster" SET "DateJoined" = (DateAdd( 'dd', - 14, "DateJoined")) where DAY("DateJoined" ) = 15

FYI - The table I have is an old one but I noted the “DateJoined” fields all had a day-of-month of 14. Just a heads up. Otherwise should be no problem.

[JoeCastor]

Your code worked perfectly (and my data shows the 15th, now 01). Ran it and the results were perfect. I was really over working it (plus some syntax problems). I was close to this at one point, but then I went off on a bunny trail… I think the syntax is what was killing me, specifically on the DAY construction.
Thanks again for your wisdom.

[Ratslinger]

My pleasure. I’ve been working out my own blown-out-of-proportions problem for days now.