Posts tagged error

Linked Table Query Returning Incorrect Results

Aug18
2010 Leave a Comment Written by admin

We had this annoying problem: a simple query in Access to a linked table (using ODBC and MS SQL server) returned incorrect results, though the same query returned correct results in MS SQL server. By incorrect I mean the field values were different, so rather than returning AZ, the query returned BZ (some other value in that field).

We thought that it was a table size issue, may be the driver could not handle huge data. However, the problem was with the “unique record identifier” while linking the table, as JM reported here. I did not have this problem any longer when I did not select any fields for the unique record identifier while linking the table.

Posted in Access - Tagged , , ,

The search key was not found in any record in Access

Sep15
2009 23 Comments Written by admin

Well, I got this error message today while I was trying to import a text file:

“The search key was not found in any record”

After some research, I found that either it was a bug caused by Microsoft Jet 4.0 Service Pack or it was the size of the database (should be not greater than 2GB). It turned out to be the later one. I fixed this problem by compacting and resizing the database. In Access 2007, you click on the Office Button (Top Left Corner), then Manage > Compact and Repair Database.

Posted in Access, Error messages - Tagged ,

Access to the VB project is not trusted

Apr24
2009 2 Comments Written by admin

I was trying to enable an add-in for Excel 2007, and I kept getting this error:

Access to the VB project is not trusted

You can turn this message off by going to Developer tab in the ribbon and then clicking on the Macro security button, and checking the check box for “Trust access to the VBA project object model.”

A word of caution: be wary of the add-ins/projects that need VBA access. Don’t allow any project/add-in with VBA access, unless you know its exact purpose or the author of that project.

Posted in Error messages - Tagged ,

Access Export to Excel (2007)

Mar02
2009 13 Comments Written by admin

When I tried to export a table/query from Access 2007 to Excel 2007, I was getting this annoying error:

You selected more records than can be copied onto the Clipboard at one time. Divide the records into two or more groups, and then copy and paste one group at a time. The maximum number of records you can paste at one time is approximately 65,000.

Access 2007 Export to Excel Error

Access 2007 Export to Excel Error

I was unable to understand the reason, because I thought Excel 2007 did not limit number of rows to 65,536. It turns out however that it is the clipboard limitation.

Solution: If you check off the “export data with formatting and layout” option, Access exports successfully.

You can export successfully using Transferspreadsheet (VBA) option too.

Here is the KB from MS.

Solution Update:Thanks to Brett, it looks like that only if you export table/query using right click > Export and have the checkbox cleared, then Access will export succesfully. That is what I always do, and it works for me. I have never tried External data option for exporting.

Posted in Access, Error messages - Tagged ,

PMT Worksheet Function

Mar06
2005 Leave a Comment Written by admin

PMT function has various uses; if you go to Excel help you?ll see its uses. I am using it to calculate monthly payment I have to do pay off credit card debt. If you also want to do that please follow these steps:

1. Type this A1=Card Name, B1=Balance, C1=Years, D1= Interest rate, E1=Monthly Payment, F1=Total payment, G1=Interest paid.
2. In the Card Name field you should enter the Card?s name you want to calculate monthly payments for, Balance is enter your credit card balance, Years you want to pay your debt off, current interest rate of your credit card. Monthly payment, total payment, and interest paid would be calculated using our formulas. Also, change the format of the interest rate field to percentage by Right click>Format Cells>Number>Percentage
3. In cell E2 enter this formula
=IF(ISERROR(PMT(D2/12,C2*12,B2)),0,PMT(D2/12,C2*12,B2))
Explanation:
PMT(D2/12,C2*12,B2)
D2, interest rate, is divided by 12 to get monthly interest rate
C2, number of years, is multiplied by 12 to get number of months
B2, balance on your credit card
Now, PMT function will calculate exact monthly payments you?d need to pay off your debt.
ISERROR(?.)
Explanation:
ISERROR function will check if the expression given produces an error and it will return TRUE or FALSE.
Finally, an IF is used to put a zero as result of the formula if it produces any error.
4. In cell F2 enter this formula
=E2*C2*12
This will give us the total payment done over the years.
5. In cell G2 enter this formula
=F2+B2
This will give us the total interest paid to pay off the debt.

This sheet should look like this PMT Worksheet Function

Note: the result of our formulas will generate red numbers in brackets; this is the way of Excel to show us negative numbers. If you have Office XP or 2003 you can view a training session here.

Posted in Uncategorized - Tagged , ,