Code check for every month? 2

[ffleitas]

Hello programmers,

I have this code in Visual Basic and I would like for it to check for the first of every month rather than current month. How can I change this code to correspond with my request?


<= DateSerial(Year(Date()), Month(Date()), 1)

Thanks,
Felix

 

[KenReay]

Hi

Not sure I understand teh condition you are trying the test, but if you just want to know if a date is the first of any month then

Day([datMyDate]) = 1

or

Format([datMydate,&quot;dd&quot = &quot;01&quot;

would do it, wouldn't it?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ffleitas]

Thanks for responding,

I tried your suggestion but I can't seem to get it to work. I am positive that it is my fault. Maybe if I give you a bit more info it would help you assist me. Here is more of a sample of what I am trying to do:


Select Case Me!ReportToPrint
Case 1
DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),Month(Date()),1)&quot;
Case 2
DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),Month(Date()),10)&quot;
Case 3
DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),Month(Date()),15)&quot;
Case 4
DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),Month(Date()),25)&quot;
End Select

Sincerely,
Felix

 

[KenReay]

Hi

I do not understand what you want, if you just check for Less than the first of anymonth, then surely that will return just about everything, since you would be say if less than 1st Dec 2003 (for example), somehow I do not think that is what you intend ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulBricker]

You would have to loop thru your Select statement changing the value in the Month() argument for the DateSerial function. Something like this

Dim x as Integer
For x = 1 to 12
Select Case Me!ReportToPrint
Case 1
DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),x,1)&quot;
Case 2
DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),x,10)&quot;
Case 3
DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),x,15)&quot;
Case 4
DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),x,25)&quot;
End Select
Next


Paul

 

[ffleitas]

Paul Bricker: I tried your suggestion. Nonetheless, a windows pops up asking for the value of x. Any ideas?

Thanks.

Ken Reay: To explain my situation better, I am trying to get the code to check every month of the year on a certain day 1, 10, 15, and 25. I changed the code from <= to just = to perhaps be more precise. Any input is greatly appreciated.

 

[PaulBricker]

Felix, it's probably conflicting with the Case statement. You could try it this way.

Dim x as Integer
Select Case Me!ReportToPrint
        Case 1
           For x = 1 to 12
             DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),x,1)&quot;
           Next
        Case 2
           For x = 1 to 12

             DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),x,10)&quot;
           Next
        Case 3
           For x = 1 to 12
            DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),x,15)&quot;
           Next
        Case 4
           For x = 1 to 12
            DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= DateSerial(Year(Date()),x,25)&quot;
           Next
    End Select

See if that helps.

Paul

 

[ffleitas]

Sorry, I tried it but it still asks me for x.

 

[PaulBricker]

OK, I found the problem I think. Your syntax for the Where Condition is incorrect. It should be like this.

Dim x as Integer
Select Case Me!ReportToPrint
        Case 1
           For x = 1 to 12
             DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= #&quot; & DateSerial(Year(Date()),x,1) & &quot;#&quot;
           Next
        Case 2
           For x = 1 to 12

             DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= #&quot; & DateSerial(Year(Date()),x,10) & &quot;#&quot;
           Next
        Case 3
           For x = 1 to 12
            DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= #&quot; & DateSerial(Year(Date()),x,15) & &quot;#&quot;
           Next
        Case 4
           For x = 1 to 12
            DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] <= #&quot; & DateSerial(Year(Date()),x,25) & &quot;#&quot;
           Next
    End Select

Paul

 

[ffleitas]

Almost there. Does not ask for x anymore. But, the dates retrieved are from ex. 10/10/02 11/10/02 from only one Member. Is there an adjustment to be made to the DateSerial for finding this month and prior months?

Thank you,
Felix

 

[ffleitas]

I did some test with the values of x by changing For x =1 to 12 to x=9 to 10 and the results only reflected the months of 9. Maybe the loop is not processing all the x values rather the first value only. Hope this helps?

Thanks,
Felix

 

[PaulBricker]

The first time thru it should return any PaymentsDate <= 1/10/02
The second time thru it should return any dates <= 2/10/02
and so on right on thru 12/10/02.

Is it only returning the two dates you mention 10/10/02 and 11/10/02? What is the exact expression you are using in the DateSerial function to return records for the year 2002.

Paul

 

[ffleitas]

I am selecting Case 2 and changed the <= to just = in order to get the [PaymentsDate] on the 10 of every month. The expression I am using is this one:


Case 2
For x = 1 To 12
DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, , &quot;[PaymentsDate] = #&quot; & DateSerial(Year(Date), x, 10) & &quot;#&quot;
Next

The result is the date: 01/10/03. I wrote some info above maybe that helps?

 

[PaulBricker]

OK, the problem is we are just seeing the last date returned by the value of x. I assume you don't want records between 1/10/03 and 12/10/03 but rather you want the records from 1/10/03, 2/10/03, 3/10/03 and so on up to 12/10/03. I'll post a solution as soon as I can.

Paul

 

[PaulBricker]

Dim x as Integer
Dim myStr as String

Select Case Me!ReportToPrint
        Case 1
           For x = 1 to 12
myStr = myStr & "[PaymentsDate] = #" & DateSerial(Year(Date), x, 1) & "# Or "
           Next
myStr = Left(myStr, Len(myStr) - 3)

             DoCmd.OpenReport "rptDueDatesBySelection", PrintMode, , "[PaymentsDate] = " & myStr  
          Case 2
           For x = 1 to 12
myStr = myStr & "[PaymentsDate] = #" & DateSerial(Year(Date), x, 10) & "# Or "
           Next
myStr = Left(myStr, Len(myStr) - 3)

             DoCmd.OpenReport "rptDueDatesBySelection", PrintMode, , "[PaymentsDate] = " & myStr 
          Case 3
           For x = 1 to 12
myStr = myStr & "[PaymentsDate] = #" & DateSerial(Year(Date), x, 15) & "# Or "
           Next
myStr = Left(myStr, Len(myStr) - 3)

             DoCmd.OpenReport "rptDueDatesBySelection", PrintMode, , "[PaymentsDate] = " & myStr 
          Case 4
           For x = 1 to 12
myStr = myStr & "[PaymentsDate] = #" & DateSerial(Year(Date), x, 25) & "# Or "
           Next
myStr = Left(myStr, Len(myStr) - 3)

             DoCmd.OpenReport "rptDueDatesBySelection", PrintMode, , "[PaymentsDate] = " & myStr   
           End Select

Try this and see how it goes.

Paul

 

[PaulBricker]

In the DoCmd.OpenReport it should look like this.

DoCmd.OpenReport &quot;rptDueDatesBySelection&quot;, PrintMode, ,myStr

That should do it.

Paul