Welcome

Hi,

Welcome to the second edition for 2013. So far so good, my goal for my Newsletter is to be fortnightly (for those of you who are not Australian, this means once every 2 weeks), my second one is out in the two week time frame.

Lots of things happening on the home front:

My daughter is due any day now. First child for her and her husband, first grand child for us. Exciting.

My son who has been off work for over 6 months due to breaking his leg in a snowboading accident in New Zealand last year, looks like being back at work by the end of March.

My husband and myself are in Sydney. My husband is having acupuncture for his retinitis pigmentosa. Strange I know, but it seems to help.

I hope this newsletter finds you well and that you have had a chance to work on your Microsoft Access projects.

The other objective this year is to split my original VBA lessons (which comprises of 56 lessons, each about 15 - 20 pages long) into individual lessons and sell them seperately via topic. I will be starting this week.

Microsoft Access Tips

Tip One: DSum and the formatting of dates

The following question, is about the trouble a user had when using the DSum function in a query. The result is returning #error.

This is the scenario they presented me with:

I have recently been introduced to the hell that is "DSum" and it's not going well.

I have a Table that tracks Employee Start Dates and Employee End Dates. I want to be able to Count employee retention stats per year which is not complicated.

I take the Employee table and create a query using the Totals Function to group the Start Dates by Year and Count the Number of Records within that year.

I then create a new query and using the previously Totaled Query's Data I use the fields to create the DSum Running Total.

It works for the Start Dates just fine so I know my formula is correct. However it gives me an error when I try the same thing for the End Dates. This is I suspect because there are Null Values for the End Dates in the Table. (Obviously if an employee is still working they are not going to have an End Date yet) However in my initial Totaling Query I do filter out the Null records, so I don't understand why the DSum is returning an error.

Below are the tables and queries used.

tblEmployeeHistory
EmHsID (Primary Key)
EmHsStart (Date Field)
EmHsEnd (Date Field)

qryEmActive - (Using tblEmployeeHistory Data)
Year: Format([EmHsStart],"yyyy") - GroupBy
Active: [EmHsID] - Count

qryEmActive_RT - (Using qryEmActive Data)
Year
Active
ART: DSum("Active","qryEmActive","Year<=" &[Year]) This formula works just fine.

qryEmCleared - (Using tblEmployeeHistory Data)
Year: Format([EmHsEnd],"yyyy") - GroupBy
Cleared: [EmHsID] - Count
[EmHsEnd]Is Not Null - Where

qryEmCleared_RT - (Using qryEmCleared Data)
Year
Cleared
CRT: DSum("Cleared","qryEmCleared","Year<=" &[Year])

This final query produces nothing but the #Error even though I have filtered out any null values in the previous query.

Answer:

I suggested a couple of things, prior to asking the database to be sent to me. The first couple of things i suggested trying included:

It could be how you have the field formatted i.e. right click on the field and select properties, under the 'general' tab see if the format property is different between the two queries.

Or it could be that the field name 'Year' is causing a problem as it is a reserved word. Note: Using reserved words in your database may cause unexpected problems, so best to steer clear.

Neither of the above solved the problem. I therefore reviewed the database and discovered that the problem wass that once a date is formatted, it becomes text, thus you need to add some talking marks into your query.

So in qryEmCleared_RT for CRT, change it to:

CRT: DSum("Employees","qryEmCleared_CO","CYear<='" & [CYear] & "'")

Notice the extra talking marks.

This is a useful thing to remember if you are dealing with dates and you have formatted them. The change of a date field to a text field by formatting will also have consequences when you try to sort the field as the data will now be sorted by text rather than dates.

Tip Two: ID field showing instead of linked text value

The following problem often occurs when tables/forms etc use look-up tables.

This was the problem:

I have created a labels report in Access 2007 based on a query but for some reason the "Journal Title" field keeps returning the original ID# from my original table, instead of the text from the report which is what I want displayed.

How can I get the labels to display the same text (Journal Title) which is shown on the "Find duplicates for RECEIVED..." query?

Answer:

The most likely problem is that the field you are using on the report, is a lookup field (i.e. drop down box), that displays the Journal Title, thus it is linked to the underlying table and this is done by the ID field. Thus why the ID field is displaying.

You can solve this problem, by adding the table that has this data in it. Then adding the 'real' journal title, to your query and then add this field to your report and delete the ID field.

Tip Three: Keyboard shortcuts causing problems

I created a database of equipment. The name of equipment is defined as "TEXT" with length of 100. When I entered the name "ULLS-A(E)", it was converted to: "ULLS-A€". I want to disable this conversion. Do you know how disable this feature?

Answer:

Unfortunately I could not create the same error as was displaying above, so at first I was a bit stuck to what the problem may be. Then I had a thought....It may be a shortcut key .....so therefore I had a bit of a dig around in Access and found the solution it was because the (e) was set up to change to €.

To fix this:

Go to Access Options (Windows Ball upper left hand corner - Access 2007) and choose Access Options from the drop down list.

Then select Proofing and Auto Correct Options, highlight the (e) and delete it. Or untick Replace text as you type.

If you also have this problem, see if this suggestion works for you.

Cheers