Welcome

Hi,

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:



Note: I am using a new delivery system, it still uses paypal, but now the lessons will be available for immediate download. So if you have any trouble PLEASE PLEASE let me know by emailing me at julie@simplyaccess.com

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)



OK enough of the sales talk, on to the tips. This week I have reverted back to my normal format of three tips. I am first going to share these with you here, then I am going to load them up on to my website.

Microsoft Access Tips

Tip One: Build tables first

Question

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?

Answer

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 field

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.

Answer

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()

End Sub

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 example

QUESTION

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

Answer

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.

Cheers