Solved IIF function and dates (1 Viewer)

[eshai]

Hello
I was asked to make a change in the code
I am currently calculating how much a student should pay per day

=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

=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

 

[CJ_London]

1st of the month is easily calculated

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

the 15th
date()-day(date())+15

 

[eshai]

1st of the month is easily calculated

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

the 15th
date()-day(date())+15

I didn't understand your solution

 

[Gasman]

Use DateSerial function.

 

[Gasman]

I didn't understand your solution

Why not just try it out?

 

[plog]

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.

 

[eshai]

Why not just try it out?

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

 

[eshai]

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

 

[CJ_London]

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

=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

 

[eshai]

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

=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

=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

 

[Gasman]

You DO NOT hard code the dates.
What are you going to do for May or any other month?

 

[eshai]

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]