Date Problems

1. Senior Member
Join Date
Dec 1969
Posts
144

## Date Problems

I am putting a email schedule together where someone can input how often and when they would get alert emails in an ASP/Access Web page. I am then building a process to run through the list of users to see when/if they want to get emails. If it is the day they get emails, to send them, if not, move on to next user.<BR><BR>A user can schedule to not receive them, receive on a certain day of the month or any day of the week (Sun, Mon, etc) and how regular (once a week, every two weeks, three weeks or four weeks).<BR><BR>I have the database table set up with the following fields:<BR><BR>Schedule = “N” do not send, “W” weekly or “M” monthly<BR>Day = Day of the week (Sun, Mon, etc.) 1, 2, etc.<BR>Week = how often (every week, two weeks, three weeks, etc.) 1, 2, etc.<BR>Month = Day of the month to send. 1-28<BR>LastSchEmail = Date of last email sent 7/18/2004<BR><BR>I am guessing it should be done in a Select Case situation:<BR><BR>Bob = objRec(“Schedule”)<BR><BR>Select Case Bob<BR><BR>Case “N”<BR><BR>Do not send go to next record (user)<BR><BR>Case “W”<BR><BR>If today is the day of the week for the email, has it been the requested number of weeks since the last email?<BR>Yes – send email<BR>No – do not send go to next record (user)<BR><BR>Case “M”<BR><BR>If today is the date of the month for the email<BR>Send email<BR>Else<BR>Go to next record (user)<BR><BR>End Select<BR><BR><BR>I need help with how the Case(s) should be written.<BR><BR>Thanks in advance.<BR>

2. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Date Problems

Id split it into Two initially anyhow<BR><BR>Select * FROM &#060;table&#062; Where Schedule = &#039;M&#039; AND DayofMonthToSend = Day(Getdate())<BR><BR>Select * FROM &#060;table&#062; Where Schedule = &#039;W&#039; AND DayofWeekToSend = Datepart(WW,Getdate()) AND DateAdd(d, Datelastsent, Getdate()) = weeksbetween * 7

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## Why would you do it in VBS code?

Why wouldn&#039;t you do it in SQL, so that you don&#039;t even *GET* a record unless the email address is qualified for sending an email out today???<BR><BR>Let&#039;s see...<BR><BR>WHERE ( schedule = &#039;W&#039; AND [week] = 1 AND WEEKDAY(DATE()) = [day] )<BR> OR ( schedule = &#039;W&#039; AND [week] &#060;&#062; 1 <BR> AND DatePart(&#039;ww&#039;,DATE()) MOD [week] = 0 AND WEEKDAY(DATE()) = [day] )<BR> OR ( schedule = &#039;M&#039; AND DAY(DATE()) = [Month] )<BR><BR>Only tricky one is the middle one.<BR><BR>Actually, we *could* toss out the first one:<BR><BR>WHERE ( schedule = &#039;W&#039; AND DatePart(&#039;ww&#039;,DATE()) MOD [week] = 0 AND WEEKDAY(DATE()) = [day] )<BR> OR ( schedule = &#039;M&#039; AND DAY(DATE()) = [Month] )<BR><BR><BR>*********<BR><BR>Why this work:<BR>WHERE ( schedule = &#039;W&#039; AND DatePart(&#039;ww&#039;,DATE()) MOD [week] = 0 AND WEEKDAY(DATE()) = [day] )<BR><BR>DatePart(&#039;ww&#039;,Date()) gets the weeknumber of the current date. Then we take that modulo the weekly frequency requested by the user. So if this is week 33 and the user asked for every 3 weeks, then 33 MOD 3 is 0 and we will send out a newsletter...*IF* it is also the day of the week the user asked for.<BR><BR>Note that ANYNUMBER MOD 1 is always zero, so if the user asked for weekly (put a 1 in the [week] field) then he/she gets mail every week, on the day specified.<BR><BR>We don&#039;t need to do anything for the "N" types, as they won&#039;t match the WHERE, of course.<BR><BR>The above is all for Access. It needs several minor mods for SQL Server or other databases.<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Ummmm...why?

What&#039;s wrong with an OR in the WHERE clause??<BR><BR>And I ignored the Datelastsent field. I can&#039;t see why it matters.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
11,247

## Snap!

nice to see I was on the right wave ~~~

6. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Ummmm...why?

By ignoring the lastsentdate you are assuming that if they want it every three weeks and tody is a designated 3 weeks they would want it today...<BR><BR>I assumed that they would want 3 weeks from a specified date.<BR><BR>Two way to look at it your choice is one mine the other

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## True, but then...

There&#039;s no point in checking for the right day of the week, because if you simply add the right number of weeks to lastSent date then it *must* be the same day of the week!<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
11,247

## yeah yeah

its been a long hard day what can I say!

9. Senior Member
Join Date
Dec 1969
Posts
144

## can I but in...

Actually, I was thinking by a week in a month not weeks in a year. I guess the &#039;LastEmailSent&#039; does not matter does it.<BR><BR>I was look that, if it is the first week of the month, send email if they want it sent on the first Tuesday of the month. Or Second Saturday of the month, etc.<BR><BR>Is that possible?<BR><BR>This work great for now just, can I send them like I said above?<BR><BR>"SELECT * FROM tblCompEmail WHERE ( Schedule = &#039;W&#039; AND DatePart(&#039;ww&#039;,DATE()) MOD Week = 0 AND WEEKDAY(DATE()) = Day ) OR ( Schedule = &#039;M&#039; AND DAY(DATE()) = Month )"<BR><BR>Thanks.

10. Senior Member
Join Date
Dec 1969
Posts
96,118

## Hmmm...yeah...not too tough...

Let&#039;s see...<BR><BR>How about<BR> WHERE ( Schedule = &#039;W&#039; <BR> AND DAY(DATE()) BETWEEN ([Week]-1) * 7 AND [Week] * 7 - 1<BR> AND WEEKDAY(DATE()) = [Day])<BR> OR ( Schedule = &#039;M&#039; <BR> AND DAY(DATE()) = [Month] )<BR><BR>See how that works? If today is the 25th and the user specified he wanted email in the 2nd week. So we would end up doing:<BR> AND DAY(DATE()) BETWEEN (2-1) * 7 AND 2 * 7 - 1<BR>which becomes<BR> AND 25 BETWEEN 7 AND 13<BR>and of course it is *NOT* in the 2nd week.<BR><BR>The 25th *IS* in the 4th week:<BR> AND DAY(DATE()) BETWEEN (4-1) * 7 AND 4 * 7 - 1<BR>which becomes<BR> AND 25 BETWEEN 21 AND 27<BR><BR>And then, if the user specified Wednesday as the day of the week (4), the email would get sent today.<BR><BR>************<BR><BR>In all that code where I have stuff in red, I am showing you where you *NEED* to put your field names in [...] brackets. That&#039;s because you are using words that are KEYWORDS in Access and you want to make sure that your field names are not confused with the keywords.<BR><BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•