|Back to Back Issues Page|
Microsoft Access Tips, Issue #001 (February 2012)
February 02, 2012
Welcome to the first edition of 2012. I decided to have a break in January and get my first edition out in February. I am pretty pleased with myself for having got it out to you so early in the month.
Hopefully you have all had a safe and wonderful holiday season and are all back at work, in full gear and ready to tackle 2012.
Not sure if one of your 2012 New Year's resolution was to learn VBA, well if it is, you will be please to know, I have the first bundle (lessons 1-5) of my VBA lessons. My aim is to produce a bundle (5 lessons) each month. But if all goes well, it will be a bit more frequently than that.
If you are interested in purchasing the first 5 lessons of my Microsoft Access 2007 VBA Lessons
for just $2.95 (AUD) then just click on the button below:
If you use an earlier version of Microsoft Access, then do not despair, my original 56 lessons for Microsoft Access 2003 and earlier, is still available for purchase for just $7.95 (AUD)
Microsoft Access Tips
Tip One: Build tables firstThis first tip was a question I received and it is a mistake many novice users make in Microsoft Access, so I thought I would share it with any newbies out there.
I have created a number of forms in an Access 2007 database that are made up of a combination of check boxes, list boxes and combo boxes. As each form gets filled out, I would like it to populate a new table that I create to store the data. How can I do this?
I think you have gone about things back the front. :0) Usually the tables are created first, then the forms are created based on those tables.
You can either build the tables and manually link the table and all the fields, but this may take a while. It maybe quicker to scrap the forms, create the tables and recreate the forms, based on the tables.
For those of you who are just learning. The tables are the bases for everything. They need to be created first. Then you build everything else based on the tables created. Note: You can always build new tables at a later stage if you have forgotten one. Just do not build the form first and then create the table, you will just make a lot of extra work for yourself.
Tip Two: Making a field invisible based on another fieldQuestion
I have been working to tie two fields together in a form; 'Tasked' and 'Date Tasked'. The first is a simple yes/no check box. What I want to do is make the Date Tasked field blocked or invisible if the Tasked box is not checked.
This tip is a bit more advanced and the answer involves a bit of coding. If the coding piques your interest and you wish to learn VBA, see my blurb at the beginning of this Newsletter.
The code will need to be in two places. On the On Current Event for the form and the On Update Event for 'Tasked'.
To do this:
First open the properties sheet for the form Click on the 'Event' tab Place your cursor next to 'On Current' and click on the button with the 3 dots. If prompted choose 'Code Builder'. This will open the VBA window. There will be two lines of code displayed:
Private Sub Form_Current()
In between these two lines of code type the following:
If Me!Tasked = -1 Then Me![Date Tasked].Visible = True Else Me![Date Tasked].Visible = False End If
Place this same code in the After Update Event for the 'Tasked' check box.
To do this:
If the properties window is no longer open
Right Click on the check box Select Properties
If it is still open just left click on it to select it.
Click on the Event Tab
Place your cursor next to 'After Update' then click on the button with the 3 dots. Place the same code above in between the two lines of code displayed.
Close and check everything is working.
Tip Three: Nested IIF exampleThis next tip looks at solving a problem using the IIF statement in a query
I'm trying to develop ONE formula that if the date wanted is less than the date completed, then subtract date wanted from date completed BUT if the date issued is greater than date wanted but less than the date completed,i need to subtract date completed from date issued---both providing how many days went by
To do this, create a query with relevant table. Add the fields required, then in the first blank column, in the top row add the following:
expr: IIf([DateWanted]<=[DateCompleted] And [DateIssued]<=[DateWanted],DateDiff("d",[DateWanted],[DateCompleted]),IIf([DateIssued]>[DateWanted] And [DateIssued]<[DateCompleted],DateDiff("d",[DateIssued],[DateCompleted])))
What I have done is created a Nested IIF statement. An IIF statement says.
If (x = y),then do this, otherwise do that)
For a nested IIF statement, the 'otherwise do that' is just another IIF statement.
I have also used the DateDiff function, to give the number of days between the two dates.
That is all for this month. Talk to you all in March. For those that have purchased the Moicrosoft Access 2007 VBA lessons, I will let you know when the next bundle of lessons are ready, if this should occur prior to the next edition.
|Back to Back Issues Page|