query for records less than a month away

[mikelauwrie]

hello all

I have a database in which there are records (what's new!?)

what I am trying to do is to have an automatic email sent which contains records which hold dates which fall in the next 28 days. But I only want it to happend once for each record.

does anyone know how I can do this?

cheers


Mike

 

[PaulBricker]

You will have to accomplish this in a couple stages. First, you will need to add a field (probably a yes/no field called something like Sent) to your Table. Then you will need to create a query that filters the records on the date field using
Between Date() and DateAdd("d",28,Date())
and where Sent = No
and you will have to attach this query to your email. Then you will need to update the Sent Field in your Table so that those records don't get selected a second time.
There are various ways to do this. You could run the process from a module, creating the QueryDef, filtering the records, attaching to and sending the email, and finally updating the table. That would probably be the way I would do it.
You request was a little vague, so it's hard to say exactly what would work best for you, but this should give you a place to start.

Paul

 

[mikelauwrie]

ok, well I have done the query. although unfortunately I don't know how to code VBA (any suitable links where I could learn would be appreciate).

this means that I have had to try using macros (I am aware that this is rather frowned upon).

my current macro opens the query and asks if you want to send it. However I want to stop the macro/not offer to send the data if the query result is null. How can I do this?

I wonder if I've tried to do something that's a bit too complicated !

Mike

 

[PaulBricker]

The process isn't real complicated but it's very important to get all the parts to work properly so that you maintain data integrity. Any time you update or change record values you have to be very confident that you know what you are doing. That, I think, will be the key for you. It isn't likey that you can do the whole thing using macro's so you will need to take some time to learn the basics of using the VBA modules. You'll want to learn how to create the code, step thru it, and debug it. Almost any VBA book will give you the basics of coding, but they might not teach you how to use a VBA module. You could do a search in the VBA forum, forum705 and check out the FAQ's in that forum as well. There is probably a wealth of info right there to get you started. Once you get rolling with that, you can post back with specific problems and I'm sure someone will be able to get you over the humps.

Good luck.

Paul

 

[mikelauwrie]

ok cheers. I'll see how I get on.

Mike