Solved IIF function and dates (1 Viewer)

eshai

Registered User.
Joined
Jul 14, 2015
Messages
195
Hello
I was asked to make a change in the code
I am currently calculating how much a student should pay per day
Code:
=IIf(IsNull([Form]![extday]),"0",[amount]/30*[Form]![extday])
where [extday] is the calculation of how many days the student was present in the month by the Diff2Dates function What they asked me now that from 01.04.2024 to 15.04.2024 is a full charge of a month and from 16.04.2024 to 31.04.2024 is half a charge of the month my code
Code:
=IIf([startdate] Between #01/04/2024# And #15/04/2024#,[amount],IIf([startdate] Between #16/04/2024# And #31/04/2024#,[amount]/2,[amount]/30*[extday]))
The question is how do I make the rule in the code apply to the following months and the following years because according to the code it is limited to 04.2024
 
1st of the month is easily calculated

date()-day(date())+1

the 15th
date()-day(date())+15
 
from 01.04.2024 to 15.04.2024 is a full charge of a month and from 16.04.2024 to 31.04.2024 is half a charge of the month my code

You're requirements need to be better stated or rethought. And what data you have to determine this explained.

1. Do you have a series of start dates and an end dates for students? I mean, What's the data look like if they attend every day but the 7th and 23rd?

2. If they attend the 4th but no other day, are they charged a full month?

3. Your description makes it seem like you now have only 2 billing options --.5 monthly cost or full monthly cost. But your new code still has a calculation for daily rate times days. Which is it?

I prefer my questions be answered with sample data. Show me data from your table(with actual table and field names) and then show me what you expect the calculation to be for that data example.
 
You're requirements need to be better stated or rethought. And what data you have to determine this explained.

1. Do you have a series of start dates and an end dates for students? I mean, What's the data look like if they attend every day but the 7th and 23rd?

2. If they attend the 4th but no other day, are they charged a full month?

3. Your description makes it seem like you now have only 2 billing options --.5 monthly cost or full monthly cost. But your new code still has a calculation for daily rate times days. Which is it?

I prefer my questions be answered with sample data. Show me data from your table(with actual table and field names) and then show me what you expect the calculation to be for that data example.
The students have an arrival date, from here it automatically calculates for them the amount according to what was agreed monthly and the days of the month according to the departure date, the default of which is the first of the current month, that way I know how much payment was received and if there is a debt
 
Because from 02 to 14 nothing will happen as I understand the code And I will have to activate the code only from 01.04.2024
as already suggested, provide some example data and the outcome required from that example data

your initial code provided won't work anyway
=IIf([startdate] Between #01/04/2024# And #15/04/2024#,[amount],IIf([startdate] Between #16/04/2024# And #31/04/2024#,[amount]/2,[amount]/30*[extday]))
because there is not a 31st April

I would hyave said if you had tried my suggestion in your example you would have started off with

Code:
=IIf([startdate] Between startdate-day(startdate)+1 And startdate-day(startdate)+15, amount, ....

the rest of your formula is not clear as to intention so I'll leave that to you to clarify
 
Last edited:
as already suggested, provide some example data and the outcome required from that example data

your initial code provided won't work anyway

because there is not a 31st April

I would hyave said if you had tried my suggestion in your example you would have started off with

Code:
=IIf([startdate] Between startdate-day(startdate)+1 And startdate-day(startdate)+15, amount, ....

the rest of your formula is not clear as to intention so I'll leave that to you to clarify
First of all, thank you
My code works if there is no 31 it refers to whatever is smaller
But anyway I did use the code you suggested only I had to add fields because I have an entry date and an exit date
so i add one more code
Code:
=IIf([startdate]>=#01/04/2024#,[paypaerday],[amount]/30*[daypart])
This way the new code will refer only to those who arrived after 01.04.2024
 
You DO NOT hard code the dates. :(
What are you going to do for May or any other month?
 
You DO NOT hard code the dates. :(
What are you going to do for May or any other month?
In the code I ignored the year and took out only the day of the month =Day([startdate]) I have another field that calculates months. In the end it looks like this [paypermonth]+[paypearday]
 

Users who are viewing this thread

Top Bottom