I have a macro that I want to run t

[SandraF]

I have a macro that I want to run that will loop through records (in a particular field) in a table and look for any that have the date MM/03/YYYY, where the month and the year is the current month and year, but it has to be the third of that month and year.

I have tried it with the now() function, but that includes the time and i don't have that.

The table name is - Tbl_Payment_Information
The field is called - NextPaymentDueDate

I want to do something like the following:

While loop
sql to retrieve all the records
Compare Date in the field to current month/03/current year
end loop

 

[TulsaJeff]

Is there any reason why you are not using a simple query for this?...just asking.

If you really need the macro for a specific purpose then I will just have to see if I can make it work...otherwise...I just used your table name and field names to recreate your situation and used a query that worked perfectly to pull out only the 3rd day of each month.

Let me know a little more about your requirements.

 

[SandraF]

If I am able to only use a query than the better.

Here is an example.

Say today I want to print a report of all the people that owe me money friday (March 2, 2001)

I want to look in the field called NextPaymentDueDate that is equal to 03/02/2001 - Everyone in the DB will have the same date in that field, the only difference will be the month and year.

I don't want to have to type in the month and year that I need to search by. I want it to be done automatically.

So far I have in the SQL -
WHERE Tbl_Payment_Information.NextPaymentDueDate = "??/02/????";

How do I get the current month and year in that formula?

 

[TulsaJeff]

Yes...I think a query would be your best bet. Wanting to only pull up the current month and year adds a little twist to the situation.

give ma a bit and I will figure it out....I love a challenge.

Just hang on to your socks and I will let you know what I come up withs-)

 

[Dreamboat]

Why not just a formula, and then use the value Y to determine if those people get the letter...



=IF(DAY(A1)=3,"Y","N&quot
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[SandraF]

But how then do you compare to the current month.

I only want to print this report once a month. So I wouls have to check to see if the person owes money this month on the third.

 

[Guest_imported]

You can use these:

=Month(now())
=Year(now())

to return current month and year.