Need a kind helper to teach me like a third-grader

[AlfredNor]

Ok. Im new to Access and obviously need some help.

I've just made a Table/Form to track the free credits my home business gives to customers.

Generally, I record incoming credits that customers will have applied to their balance in the future.

Each customer has a unique Bill Day, which is the day they personally get billed every month - and it doesnt change. I made a set of fields where the date the credit was recorded can be taken, as well as AUTO generating the correct "date" the credit should be applied - i.e. the same bill DAY, but based on when the credit is taken an "application date" of this month or next month is generated.

Since I didnt really know how to autogenerate dates, I split each of the sections of the date into separate fields for month, day, and year. Then, thanks to the good folks here I made an IIf expression which autogenerated a NUMBER (not a date because I didnt know how to say A=(mm/dd/yy +1 month)

First, if anyone knows a better way using dates...Id love to hear it.

Second, what I need now is a report/query/filter (im not sure which) that will COMPARE these numbers (which approximate a date) to the current date (today's date if you are looking at the info today, tomorrow's date if you are looking at it tomorrow..etc.)...For the purpose of printing out a list every day of who should have their credits applied that day.

Either those individual digits need to be converted into a date then compared....or I need to re-work the way the dates are entered/generated so they can be compared apples to apples.


Can some kind person help me? Id be more than grateful and have my wife send you muffins THANKS

I'll attach the file so you can get a feel for what im doing.

 

[AlfredNor]

Great...getting closer

Ok..Ill reformat using actual date fields...and learn those specific commands.

Here's my next question....how would I insert a specific DAY to be auto inserted into a formula for mm/dd/yyyy ? (So that whatever variable the month becomes, the day stays constant)

 

[AlfredNor]

Yes..thats a start

Thanks thats a start. Can I insert a field that is a date using that command? E.g. DateAdd("m",1,[Field])??

Let me describe the way things happen. Let me say that I really appreciate any and all help.

Im an independent re-seller of phone lines for $35 or $60 per month. If some one refers a new customer they get a free month of service. When the person who has been referred calls in, we record the credit that should be applied to the referrer. If the free month of service comes in time to apply it to the referer's bill that month we apply it that month. If not the information has to be saved/and applied some time after. What Im trying to make is a way to not only record how much they have earned total, but how much they have used, and therefore how much they have left.

First the [Next Application Date] has to be generated to tell us a) what month to apply the credit and b)if the credit made it in before this person's billing date. We need a month, a day, and a year.

The [Referral Received Date] will tell us the year for the application date. The day will just be the same as the [Bill Day].
The month for application is a bit more complicated.

Comparing the [Referral Received Date] to the [Bill Day] (the unique day of the month that customer is billed on) will help us compute this. If [Referral Received Date] > [Bill Day] then the [Application Month]=[Referral Month] +1...otherwise its the same month.

I thought I could make [Application Date]=

Iff([Referral Received Date] > [Bill Day], DateAdd("m",1,[Referral Received Date]), value_negative)

but I dont know how to make the value_negative say [Application Date]= [Referral Received Date] with the day changed to the unique [Bill Day].

NOW..if THAT hasnt fried your brain...we still have to make the [Credits Used] number generate. [Credits Used] is just [Credits Earned] - ((sum Referral Amt 1, 2, 3...N) but only if their [Application Date] is older than today's date.

So, if pulled up today 9/19, the record would look like:
-----------------------------------------
Name: Al Norris Bill Day: 15th

Credit Earned: 100$ Credit Used: 50$ Credit Left:$50

Referral 1: Curtis McIntyre
Referral Amt: 50$
Referral Received: 9/10/02
Application Date: 9/15/02 (so its been applied)

Referral 2: John Padgett
Referral Amt: 50$
Referral Received: 9/19/02
Application Date: 10/15/02 (so it goes to next month)
---------------------------------------------------------------------

Confused yet? I REALLY need help and appreciate the help Im getting here. Im a home based business and dont have money to spend to buy someone to make this. Id like to learn as I go so that I could help others as well.

Thank you thank you thank you.
Alfred