MS Access Table auto populate recored

Hi All,

I’m creating an MS 2007 Access database for my bills.  Some bills are due on the 5th of every month and some are due on the 25th of every month.   In a table, is it possible to have a column that will be populated via a form such as entering the due date as 10-25-12?  And another column in the same table auto display “October 2012” (Month Due)?  Same thing if I entered 10-5-12 the other column will display “October 2012”.

Thanks in advance
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
What you can do is create a "table" to enter the sample dates, then create a query from this table to "lookup" the corresponding date in the other table.
But here again I am confused by your design.
Why bother looking up the Month/Year, when you can look up the date, then format it as Month/year.
In other words, the Month/Year is just a formatted version of the date, so I don't see a real need to look up a formatted date..
So if you wanted to do this you would have to create yet another query to create the formatted date, then use the first query to lookup the formatted date in the second query...
In my sample I simply looked up the date, then formatted this looked up date as Month/year

The other kicker here is that you are not stating what you want displayed if you enter a date with no corresponding date.
In your XL file, #N/A is returned for invalid dates, so again, in my sample, #Error is returned.
Obviously, you can avoid this with an even more complex formula, ...but now you can see how complex this design may need to become...

But again, you can do anything you want.
If your goal here is to simply learn how a lookup works in Access you can try something like this (See: qryLookUpPayments)

NO.. It wont be possible in Table..
You can use calculated columns in table:
but usually you don't need it.
On your form you can show same data with different formats.
For example, if field txtDateDue is 10-25-12 (I hope it id date field, not text), other field could be:
and in format you can manually enter:
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

ReyesrjAuthor Commented:
Would it be possible to run a query that will display the data for all bills due on 10-05-12 and 10-25-12 only.  Some type of criteria range?  If so, please show me.
Jeffrey CoachmanMIS LiasonCommented:
<And another column in the same table auto display “October 2012” (Month Due)?  Same thing if I entered 10-5-12 the other column will display “October 2012”.>
You may wish to rethink this design...
Your requests here really does not fit with many database design "best practices".
What is your skill level with Access database design?

1. Adding Columns dynamically is a tip-off of an "un-normalized" structure.

It is far easier to populate "Records" with the recurring dates
So I am not quite sure of why you want new columns...

Besides, your new requirement of:
<a query that will display the data for all bills due on 10-05-12 and 10-25-12 only.>
...would be much easier if the dates were in records...
Think about it, ...after 5 years you will have 60 columns.
Access has a finite number of fields you can add, (255) then what...?

2. You also did not post any details on this system.
I see at least two tables:


This is not counting tables for your dropdown boxes for: categories, Late/Ontime, Fees, Acct Type, Institution, CheckNumber, confirmation code, ...etc

3. It is also not clear how far in advance to want the dates populated for...
Typically you may want to do this yearly.

There are many more things you need to also consider here...
For all of these reasons, ...and more, ...I would just by a Personal finance program to do this all for you.
Even for "simple" Personal finance needs, this may evolve into something more complex than you bargained for.
(Split deposits, Partial payments, reminders, alerts, ..etc)
An off-the-shelf product will have worked out all the bugs and will have Support, upgrades, web utilities (upload/download bank data, tax help, reconciliations, overdraws, ..etc)
Finally in an off the shelf product, the dates are simply "displayed" in a calendar, thus there is no need for any code to "generate" dates.
...or have to worry about the recurrence (and leap years, Holidays...ect),
...or if a user clicks the "Generate Dates" button twice by accident, or if a date is deleted by accident.
...Or remembering to click the Generate date button every time the recurrence end date is reached...
....or updating all the dates if the start date changes... other words, ...there are too many potential unforeseen contingencies to worry about here.

I have been working with Access since Access 97 and I just did not see any advantage to creating something like this in Access.
Quicken deluxe is $60 USD and your done.
So if you value your time at $20/hr, ...this project will take much longer than the 3 hr break-even time (3hrs*$20/hr=$60)
Again, I see no real reason to re-invent the wheel here.

But you can try the attached sample DB, if you are still interested.
Generate the dates for both "Bills , then run all three queries...

If you still want to go your original "Columns" way, then please continue on with als315.

ReyesrjAuthor Commented:
Thank you Jeff,

I’m fairly new to Ms Access. The purpose of me creating this database is purely for training.  Just something I chose to do to learn MS Access.  I think I’m above average in MS Excel.  I was in payroll and it forced me to learn excel to make the job easier.  And it did!

As for Month Due column reflecting the Date Due column, I can do this in excel with a vlookup formula.  I was inquiring if a lookup function, like vlookup, is available in Access.  The Date Due column is entered into the table via the form.  I was thinking if the Date Due column for a record showed 10-5-12 is there a lookup function that will auto populate the Month Due column, for that record, with October 2012.  The purpose is to compare bills paid in October last year to October this year.  This is just self training.

I agree there are better applications than Access for this purpose.

Any and all advise is welcomed.
Jeffrey CoachmanMIS LiasonCommented:
A basic "Lookup" in Access has a syntax like this:
Dlookup("YourField", "YourTable", YourCriteria")

So to lookup a payment in a certain month (March, 2012, for example) you would do something like this:
YourOldPayment=Dlookup("PaymentAmount","tblPayments", "Month(PaymentDate)=4 And Year(PaymentDate)=2012" )

Obviously here you could use values from another place to specify the Month and year.

But know that this is still rather "odd"
I know this is just Training,...but why practice something that is nonstandard and does not follow standard DB design rules?

For example, why waste time "Practicing" how to insert a car engine backwards?

Perhaps this would all be clearer if you posted a sample of the Excel file you are trying to replicate with Access.

ReyesrjAuthor Commented:
Thank you for being understanding.
Okay, I know excel is not access so maybe I'm trying to apply excel into access.  Such as, inserting a car engine backwards (LOL).  I attached an excel spreadsheet with a vlookup formula.  But if it can't be done that’s fine.  Is the example you gave VBA?

ReyesrjAuthor Commented:
Thanks Jeff,

I will work on the queries and just mess around with it.  This has been very helpful.  I’ll be posting more questions later.

Thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.