RE: Excel & MS Query caused compile errors

Tech-Archive recommends: Fix windows errors by optimizing your registry

I'm sorry for hijacking this question but somehow I just cannot post any new
question here.

My data is in an Excel file and I'd like to fill a column within this file.
After connecting this file to MS Query, I’d like to compare Column A and D
then use the content of Cloumn B to fill column F.

There are 3 groups in my data. Group 1: Column A & Column B.
Group 2: Column C & D. Group 3: Column E and F. Column A has monthly data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it means a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and Jan
31, 1988 are included. Column B is the debt of the corresponding month. e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D are the
companies’ names and debt issuing dates respectively. In column C, there are
848 different firms. However, the starting and end dates are different in
these two columns. That is, according to column D, company A0004 does not
have any issuing dates during 1988-2000. The issuing dates for A0004 only
exist
during 2001 and 2005. There is another example: A0005’s issuing dates are
from 1989 to 2005. Column E has 848 firms although firms’ names are not
listed in column E. Each firm has a fixed format which starts with 1988 and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 …
… … A0004 01/04/2004 …
31 Dec 88 3.0045 A0004 30/09/2005 …
… … A005 31/12/1989 …
31 Dec 89 3.0482 A005 31/12/1990 …
31 Dec 01 3.1124 … … …
… … … … …
31 Dec 05 4.5711 … … 2001 3.1124
… … … … …
… … … … 2005
… … … … 1988
… … … … …
.... ... A005 31/12/2005 2001
… … …

… … 2005
… … …
In order to compare Column A and D, I have to change them to the same
format. Effectively if the the month in Column A and D are the same, I can
put the content of Column B into Column F. For instance, because 31/12/2001
corresponds to 31 Dec 01 in Column A, 3.1124 should be placed after the 2001
in column E for firm A0004.

As for the 1st and the 2nd cell in column F, the year-end-values in column B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell in
column F. Similarly, 3.0482 is placed after 1989 in column E.

I tried the first step: SELECT Time
UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in query expression 'Time UPDATE Time SET
Time = 31/01/1988 WHERE Time = 31 Jan 88'. Why?

Thank you so much!


--
Tatiana


"Joel" wrote:

You didn't specify what happened to cause the problem. did you perform any
updates to windows, Office, or other products that may of caused the cahnge.
Do yo uhave your window updates set in automatic update mode or did your MIS
department push some updates on your PC?

I suspect one of the DLL got updated.check the References in the VBA menu
tools - References and see if any of the select libraries got updated.

"Simon Shaw" wrote:

Hi,

After connecting to BusinessVision tables and returning data to Excel, my
VBA code in excel suddenly became un-compileable. Simple code syntax such as
declaring a variable was suddenly invalid. I would comment out the offending
lines to see what else failed, and stopped after about the 12th code failure.

The code compiles fine in numerous environments and runs in Excel 2000, 2003
& 2007 against Pervasive, MS Access, SQL Server, My SQL and Oracle databases.

The error occurs just after manually querying BusinessVision 7.3 with
Pervasive 10 database with Excel for the first time.

Fix Attempts include:
- Uninstalling/re-installing Excel
- removing and adding the System DSN files in the ODBC Drivers
- uninstalling BusinessVision and pervasive
- applying all updates to Excel, BusinessVision and the OS.
none worked

I then started with a clean windows server, installed excel, then installed
BusinessVision. the VBA code still compiled. I then added the System DSN
file. the VBA code still compiled fine. I then performed a manual database
query to retrieve data from the AR - Transaction tables, which was
successful. I then tried to compile the VBA code and it failed - miserably...
:(

thoughts?

Thank you!

--
Simon Shaw, CA | President | Kode101 Inc.
www.kode101.com
.

Relevant Pages

  • Excel & MS Query caused compile errors
    ... After connecting to BusinessVision tables and returning data to Excel, ... VBA code in excel suddenly became un-compileable. ... The code compiles fine in numerous environments and runs in Excel 2000, ...
    (microsoft.public.excel.programming)
  • RE: Excel & MS Query caused compile errors
    ... Do yo uhave your window updates set in automatic update mode or did your MIS ... After connecting to BusinessVision tables and returning data to Excel, ... VBA code in excel suddenly became un-compileable. ...
    (microsoft.public.excel.programming)
  • RE: ALL EXPERTS ........... WHERE ARE YOU ??
    ... that if someone sees MVP next to a name in a reply that they may assume I ... MVP or otherwise really sharp Excel person, I figure things are in good hands. ... VBA code is set aside and the file opens. ... I would like to know whether this is not possible or there is a bug in the ...
    (microsoft.public.excel.misc)
  • ActiveX Control Events dropped | lost | blocked - Excel / VBA
    ... I'm trying to track down any info on when VBA code gets halted and/or ... EVERY event fired by the ActiveX control. ... gets "sunk" by Excel VBA code. ... events ARE dropped whenever you type into a cell or the formula ...
    (microsoft.public.excel.programming)
  • Re: Excel 2007, log chart issue
    ... I manually set the scale to logarithmic, I got the error the first time, but ... I was using Excel 2007 SP1, ... Also I have found that under some circumstances with a log axis just *reading* Axes.MaximumScale in VBA can also cause the error message to occur again. ... It also fails to show all the legends correctly when a large number of data lines are plotted in the initial graph and you cannot adjust this safely without adding a huge delay in the VBA code. ...
    (microsoft.public.excel.charting)