Ask Your Question
0

Base: Change DAY in select records [closed]

asked 2017-10-15 23:17:08 +0100

Joe Castor gravatar image

updated 2017-10-15 23:20:51 +0100

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
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2017-10-16 15:19:28.656447

1 Answer

Sort by » oldest newest most voted
0

answered 2017-10-16 04:25:40 +0100

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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.

Joe Castor gravatar imageJoe Castor ( 2017-10-16 05:23:07 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-10-16 05:27:37 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-10-15 23:17:08 +0100

Seen: 18 times

Last updated: Oct 16 '17