Solved Help with an inquiry

[abanob]

Hello every body I just want a help on inquiry
I have supervision customer table I have a date of supervision and I want to make inquiry every month of the year at the same date
appeared on the same date on month
please help

 

[theDBguy]

Hi. Welcome to AWF!

To help us better understand your request, could you please post a sample set of data for the result you want to get out of your table?

 

[abanob]

Sub_date is the field I want to make inquiry to appear every month at the same day
for example: if I have client signed on date 30/11/2021 i want to make inquiry to appear on 30/12/2021,30/01/2022...etc.

 

[cdoner]

You've come to the right place for help. The community here is amazing.

I believe I understand what you are requesting. Your expression would need to check the value in [SUP_DATE] and somehow calculate your desired result against DATE(). I'm thinking an IF_THEN statment for this.

If that is correct, does your internal process HAVE TO use this approach? Would it not be easier to change your internal process to run on a fixed day of the month, say for example on the 15th of every month? Or, from an accounting perspective change your calendar months into PERIODS of 28 days with 13 periods each calendar year.

I'm suggesting this because not every month has the same amount of days and then there is leap year to contend with. Sometimes a simple approach is better design. Your thoughts?

ID	SUP_DATE	[Expression to Calculate Next Month with same day of the day from [SUP_DATE]	Result to Bound or Unbound Field?
5	12/13/2021		1/13/2022
6	11/30/2021		12/30/2021
7	11/10/2021		12/10/2021
9	11/30/2021		12/30/2021

 

[oleronesoftwares]

Welcome

 

[bastanu]

Please have a look at the qryAllInquiry and qryTodayInquiry. The latest should pop-up when you open the db but the VBA project seems corrupt so the AutoExec macro fails, look for the code in Module1.
I have added qryAllInquiry so you can see what happens if some of the months do not have the day in question. I modified the first record to be Dec 31 and in the last calculated column I am showing month # 4 (April); notice how the date automatically changes to May 1.

Cheers,

 

[abanob]

Good morning every one
thank you all for yours concern I want to make remainder at every 30 days from the [SUB_DATE]

1. Do you want one person to see the notification or everyone?
2. @cdoner has already mentioned the date problem. Not all months contain 30 or 31 or even 29 days. What happens then?
3. You don't open the database every day so what happens if you don't open the db on the day where the notice would show up?

It looks to me that it might be better to do have the query run once each week and look for all the records that you need to process in the next 7 days. You will need to keep a table to keep track of when the query was last run.

Answer the questions and we'll move on and try to come up with something that will work.

I Want everone to see this notifications
we open this database all over the day
i want to see this notification every 30 days all over the year

 

[oleronesoftwares]

I Want everone to see this notifications

The notification/query will be placed on the startup form of the application, i.e if the application has one.

 

[arnelgp]

you must Open Main form before you can see the notification.
i change one record to be Nov-25-2021, for you to see the Notification.
open Main Form.

 

[abanob]

you must Open Main form before you can see the notification.
i change one record to be Nov-25-2021, for you to see the Notification.
open Main Form.

thank you very much

 

[bastanu]

In my sample that I uploaded in post # 7 and that seems to have been ignored I made the notification show up every month on the same day by using DateSerial to reconstruct the date.
Here it is again with an AutoExec macro opening the query every day there is at least one entry.
Cheers,

 

[abanob]

Thanks you all
I have clients that pay every 30 days as of [SUP_DATE], so I needed a reminder every 30 days, also we open this database every day and there are no holidays in our work, so this database will open every day.

 

[arnelgp]

What if a tree falls through a window and you have to close your office for two days until the damage is repaired?

they work as Ants so there is no holiday or calamity for them.