Solved

Access 2003 problem with Trim and Format$ functions after moving Database to new computer with Windows 7

Posted on 2013-01-05
10
299 Views
Last Modified: 2014-12-24
Got a new computer 64 bit with Window 7 bit Home Premium.  Installed Office 2003 Professional and made sure all of the updates were applied.  Moved my database file (which was created in 97 and converted to 2000 and finally to 2003).  The current database runs on a windows XP machine with Office 2003.

On the new PC, the database will open and process correctly but whenever the code utilizes a TRIM or FORMAT$ functon it gives you and error and stops.   I don't convert things very often but I sorta recall when I converted to Office 2003 back when I had to do somethng with the MSJET40.dll program and then everything started to work correctly.  I do have a the MSJET40.dll module  version 4.0.9756.0 created 7-13-09 in 2 directories.  Windows\sysWOW64 and Windows\Winsxs\X86_microsoft-windows-M..-components-jetcore......... .  I have also tried to see if I have any missing references in my database and I can't find anything that says something is missing.  

Appreciate any help - hope this is not a question asked before but in just a different format.
0
Comment
Question by:Pat1120
  • 4
  • 4
10 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38747000
You do seem to have hit a common problem that occurs with VBA and VB6. The symptom is that the built-in string functions fail if there is anything in the References list marked as 'MISSING'.

However you say that you have checked for that.

What is the error number and message?
0
 

Author Comment

by:Pat1120
ID: 38747207
Ok I ran  the data base and it gave me a box that had the name of the funtion at the top and the message "Enter parameter Value" .

here is how I have the text boxs coded:

Name: field50
Control source: = Format$([customer setup date],"yy")&"-"&[customer number]

Name: Text85
Control source: = Trim([Customer city]&","&[customer state]&"-"&[customer zip code])

How I decided that no modules where missing is I opened the report in design view and from the tools i choose macro and then visual basic editor and then references again- this gave me a list of available references:  I only have 4 checked and nothing about anything missing.  I boxes checked are:

Visual Basic for Applications
Microsoft Access 11.0 DB object library
ole Automation
Ulility

It's been about 4 years since I done anything to my program so I wasn't very profience than and really lost now.     Thank you for helping me out
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38747223
I don't know that last one, but I think that you will need references to Microsoft DAO object library, the Microsoft Office Library, and the Microsoft ActiveX Data Objects library.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:Pat1120
ID: 38747238
I'll let you know if this works
0
 

Author Comment

by:Pat1120
ID: 38747246
Graham,

should I check all of the Microsoft activex data libraries amd microsoft Office libraries there are  quite a few to choose from?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38747521
Choose the latest (and don't forget the DAO library).
0
 

Author Comment

by:Pat1120
ID: 38747608
Graham played around adding libraries and finally got the trim function to work but never could get the Format$ function to not be an error so I just went in a changed all uses of the Format$ to just Format and every thiings seems to work and look  Ok.   Know it's not the fix I was looking for but whatever works.   If you have any other ideas let me know and I'll try them so if others this problem we might know of a fix.

Thanks for you help
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 38747686
In VBA and legacy VB Format and Format$ should both work, just as Left and Left$, Trim and Trim$, etc.  The trailing $ signals to the compiler that it is a string function. Without it, the compiler takes a trifle longer to work that out.

Type suffixes are considered to be obsolete, and are not usable at all in VB.Net or VBScript.

I understand that your code is running now, but if you still want to pursue the Format$ problem, remember that if you want to help with any error, you should provide the text of the error message, the number and the code of failing line.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now