displaying 15th and last day of month

[corics15 via AccessMonster.com]

hello everyone!
i have been thinking of how to display on a table the 15th and last day of
every month based on date text field entered by the user. hope someone can
really help me on this!! this is the idea:
when the user enters on a date text field let's say 3/22/2008, when he hits
the button, it will display something like this on a table with periods of
let us say 10 counts.

period
3/31/2008
4/15/2008
4/30/2008
5/15/2008
5/31/2008
6/15/2008
6/30/2008
7/15/2008
7/31/2008
8/15/2008

i don't know how to do this, please i desperately need your help!!!

 

[raskew via AccessMonster.com]

Hi -

Try copying/pasting this to the criteria cell of your datefield, replacing
[datefield] with your field name:

dateserial(year([datefield]), month([datefield]),15) OR DateSerial(year(
[datefield]), month([datefield]) + 1, 0)

HTH - Bob

 

[corics15 via AccessMonster.com]

Hi -

Try copying/pasting this to the criteria cell of your datefield, replacing
[datefield] with your field name:

dateserial(year([datefield]), month([datefield]),15) OR DateSerial(year(
[datefield]), month([datefield]) + 1, 0)

HTH - Bob

hi! thanks for the immediate response! i really appreciate it, but, it
displays only the last day of the month without the 15th day...
i tried your code and it displayed this....assuming it has 10 counts...

period
3/31/2008
3/31/2008
3/31/2008

 

[John Spencer]

I'm not sure how you are going to handle 10 iterations, but the
following should return all records where the date is on the 15th or the
last day of the month.

SELECT *
FROM YourTable as T
WHERE Day(T.DateField+1) In (1,16)
AND T.DateField > #3/22/2008#

If you are always returning 10 days then

SELECT TOP 10 *
FROM YourTable as T
WHERE Day(T.DateField+1) in (1,16)
AND T.DateField > #3/22/2008#
ORDER BY T.DateField

In the query grid
--Select the fields you want to see
--In a new field "cell" type
Day([YourDateFieldName] + 1)
--Enter the following as the criteria
IN (1,16)
--Under your Datefield enter the start date
--Double click in a blank area of the upper section of the query
--Change Top Values to 10
--Sort your records by the DateField in ascending order


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi -

Try copying/pasting this to the criteria cell of your datefield, replacing
[datefield] with your field name:

dateserial(year([datefield]), month([datefield]),15) OR DateSerial(year(
[datefield]), month([datefield]) + 1, 0)

HTH - Bob

hello everyone!
i have been thinking of how to display on a table the 15th and last day of
every month based on date text field entered by the user. hope someone can
really help me on this!! this is the idea:
when the user enters on a date text field let's say 3/22/2008, when he hits
the button, it will display something like this on a table with periods of
let us say 10 counts.

period
3/31/2008
4/15/2008
4/30/2008
5/15/2008
5/31/2008
6/15/2008
6/30/2008
7/15/2008
7/31/2008
8/15/2008

i don't know how to do this, please i desperately need your help!!!

 

[Linq Adams via AccessMonster.com]

If you're trying to generate 10 new records with dates as stated, this may be
what you need. This code assumes a textbox (SeedDate) where an intital date
is entered, a textbox (Iterations) where the number of records to be
generated are entered and a command button (GenerateDatedRecords) to trigger
the creation of the records. This code assumes an even number will be entered
in Iteration; if you want to be able to enter odd numbers here, you'll have
to do some tweaking of the code. It also assumes if the 15th of a month is
entered, i.e. 3/15/2008, that the series of records will start with that
number. This too can easily be tweaked if it doesn't suit your needs.

Private Sub GenerateDatedRecords_Click()
Dim RecSeedDate1, RecSeedDate2 As Date

If Me.Iterations mod 2 = 0 Then

If Day(Me.SeedDate) <= 15 Then
RecSeedDate1 = Month(Me.SeedDate) & "/15/" & Year(Me.SeedDate)
RecSeedDate2 = DateSerial(Year(Me.SeedDate), Month(Me.SeedDate) + 1, 0)

Me.MyDates = RecSeedDate1

For i = 1 To (Me.Iterations / 2) - 1
DoCmd.GoToRecord , , acNewRec
Me.MyDates = DateAdd("m", i, RecSeedDate1)
Next i

For i = 1 To (Me.Iterations / 2)
DoCmd.GoToRecord , , acNewRec
Me.MyDates = DateSerial(Year(RecSeedDate2), Month(RecSeedDate2) + i, 0)
Next i

Else

RecSeedDate1 = DateSerial(Year(Me.SeedDate), Month(Me.SeedDate) + 1, 0)

RecSeedDate2 = Month(Me.SeedDate) + 1 & "/15/" & Year(Me.SeedDate)

Me.MyDates = RecSeedDate2

For i = 1 To (Me.Iterations / 2)
DoCmd.GoToRecord , , acNewRec
Me.MyDates = DateAdd("m", i, RecSeedDate2)
Next i

Me.MyDates = RecSeedDate1

For i = 1 To (Me.Iterations / 2) - 1
DoCmd.GoToRecord , , acNewRec
Me.MyDates = DateSerial(Year(RecSeedDate2), Month(RecSeedDate2) + i, 0)
Next i
End If

Me.Requery

Else

Msgbox "The number for Iterations must be an even number"

End If
End Sub

 

[raskew via AccessMonster.com]

You having said what kind of data you are storing, but keep in mind that you
are asking for future dates. If your table doesn't contain future dates,
you are not going to get the output you're looking for.

Bob

I'm not sure how you are going to handle 10 iterations, but the
following should return all records where the date is on the 15th or the
last day of the month.

SELECT *
FROM YourTable as T
WHERE Day(T.DateField+1) In (1,16)
AND T.DateField > #3/22/2008#

If you are always returning 10 days then

SELECT TOP 10 *
FROM YourTable as T
WHERE Day(T.DateField+1) in (1,16)
AND T.DateField > #3/22/2008#
ORDER BY T.DateField

In the query grid
--Select the fields you want to see
--In a new field "cell" type
Day([YourDateFieldName] + 1)
--Enter the following as the criteria
IN (1,16)
--Under your Datefield enter the start date
--Double click in a blank area of the upper section of the query
--Change Top Values to 10
--Sort your records by the DateField in ascending order

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi -

[quoted text clipped - 27 lines]

 

[corics15 via AccessMonster.com]

wow! thanks for all the info you gave me....i will definitely try all of your
suggestions and see what will I come up with. i will try that now...

 

[corics15 via AccessMonster.com]

hello! i tried your code and I entered on the SeedDate = 4/15/2008 and
iterations = 10 and it displayed this:

4/15/2008
5/15/2008
6/15/2008
7/15/2008
8/15/2008
9/15/2008
10/15/2008
11/15/2008
12/15/2008
1/15/2009

i was hoping it would display this:

4/15/2008
4/30/2008
5/15/2008
5/31/2008
6/15/2008
6/30/2008
7/15/2008
7/31/2008
8/15/2008
8/31/2008

my idea is add N months from a given date based on number of iterations. a
month has 2 periods that is 15th and the last day of month, and that should
be considered as 2 iterations. so generally, if we start with 4/15/2008 it
would end up at 3/31/2009 with iterations of 24 and not with 4/15/2009 if we
add 12 months from 4/15/2008. the number of iterations plays a key role here,
i think.

i have here a procedure which does exactly that but my main problem is, it
adds 12 months from given date. as i have said 1 month = 2 iterations, so
that would be 24 iterations in all. 4/15/2008 should end with 3/31/2009 with
24 iterations, not with 4/15/2009 with 25 iterations.

Sub FillTBL()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim start_date As Date
Dim end_date As Date
Dim I As Date

start_date = stDTE
end_date = enDTE

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblDates", dbOpenTable)
' Set our counter to zero
counter = 0
With rst
For I = start_date To end_date
If Day(I) = 15 Or Day(DateAdd("d", 1, I)) = 1 Then ' 15th or
last day of month
counter = counter + 1 ' let us
increment our counter!
.AddNew
![due_date] = I
.Update
End If
Next I
End With
rst.Close
Set rst = Nothing
Set dbs = Nothing

End Sub

please help!!

 

[Linq Adams via AccessMonster.com]

I don't know what to tell you, corics15! I ran both your scenarios with the
code I gave you and got the exact answers you said you were looking for!

A SeedDate of 4/15/2008 and iterations = 10 yielded

4/15/2008
4/30/2008
5/15/2008
5/31/2008
6/15/2008
6/30/2008
7/15/2008
7/31/2008
8/15/2008
8/31/2008

And a SeedDate of 4/15/2008 with IIterations of 24 ended up 3/31/2009.

Without seeing your exact code, my guess would be that you either haven't
used the exact code I gave you or you've replaced some object names
incorrectly. If you copy and post the ***exact*** code you're using, I'll be
happy to check it.

 

[raskew via AccessMonster.com]

Would you clarify your intent. Are you:

1) Trying to add records (dates) to an existing table?
-- or --
2) Query existing dates in an existing table?

Take a look at the responses you've received and you'll see that responders
are attacking your request for assistance from different points of view based
on their understanding of the problem.

Best wishes - Bob

 

[corics15 via AccessMonster.com]

hello Bob! sorry if i got it all messed up. i am trying to add date records
to an existing table. i was just so desperate and my mind is all cluttered up.
...thanks for the immediate response...

Would you clarify your intent. Are you:

1) Trying to add records (dates) to an existing table?
-- or --
2) Query existing dates in an existing table?

Take a look at the responses you've received and you'll see that responders
are attacking your request for assistance from different points of view based
on their understanding of the problem.

Best wishes - Bob

I don't know what to tell you, corics15! I ran both your scenarios with the
code I gave you and got the exact answers you said you were looking for!

[quoted text clipped - 18 lines]

incorrectly. If you copy and post the ***exact*** code you're using, I'll be
happy to check it.

 

[corics15 via AccessMonster.com]

you're right, i changed some of the objects and didn't bother to check it
with the code you gave me. i'll try it again and i'll let you know....thanks!

 

[corics15 via AccessMonster.com]

hello again! i forgot to ask if Me.MyDates is a textbox with an underlying
record.

here is my code....

Private Sub Command13_Click()
Dim RecSeedDate1, RecSeedDate2 As Date

If Me.iterations Mod 2 = 0 Then

If Day(Me.SeedDate) <= 15 Then
RecSeedDate1 = Month(Me.SeedDate) & "/15/" & Year(Me.SeedDate)
RecSeedDate2 = DateSerial(Year(Me.SeedDate), Month(Me.SeedDate) + 1, 0)

Me.txtDueDate = RecSeedDate1

For I = 1 To (Me.iterations / 2) - 1
DoCmd.GoToRecord , , acNewRec
Me.txtDueDate = DateAdd("m", I, RecSeedDate1)
Next I

For I = 1 To (Me.iterations / 2)
DoCmd.GoToRecord , , acNewRec
Me.txtDueDate = DateSerial(Year(RecSeedDate2), Month(RecSeedDate2) + I, 0)
Next I

Else

RecSeedDate1 = DateSerial(Year(Me.SeedDate), Month(Me.SeedDate) + 1, 0)

RecSeedDate2 = Month(Me.SeedDate) + 1 & "/15/" & Year(Me.SeedDate)

Me.txtDueDate = RecSeedDate2

For I = 1 To (Me.iterations / 2)
DoCmd.GoToRecord , , acNewRec
Me.txtDueDate = DateAdd("m", I, RecSeedDate2)
Next I

Me.txtDueDate = RecSeedDate1

For I = 1 To (Me.iterations / 2) - 1
DoCmd.GoToRecord , , acNewRec
Me.txtDueDate = DateSerial(Year(RecSeedDate2), Month(RecSeedDate2) + I, 0)
Next I
End If

Me.Requery

Else

MsgBox "The number for Iterations must be an even number"

End If
End Sub