Problems Converting 97 Query into 2003 format

Hi All,

I have a database in Access 97 which works fine but our company is
converting from NT4 to XP Pro and in the process changing Office 97 for
Office 2003. I have tried to convert the 97 database into Access 2003 but
the Autoexec macro falls over on the query below. I have tried running it
separayely and the computer just hangs.

I realise there is a lot of code below, but I'm hoping somebody may know
what they are looking for...

Any help would be massively appreciated.

Thanks in Advance,

John


INSERT INTO Vendor_Preformance_Policing_Table ( TYPE, IPT, CUSTOMER_2,
MaxOfGR_Temp, [PO CREATE DATE], [ORDER REQUEST], [OR LINE], GR_Date,
Supply_Source, [QTY REQD], [QTY O/S], Proj_No, [PART NUMBER], [PO NO], [PO
LINE], Release_No, BUYER, STATUS, REQDT, AQUISITION, PROM_Date, CONT_Date,
Cost_Stat, Cost_Price, Price_code, NSN, PRI, Sell_PRICE, [Key], FC_DATE,
Keyword )
SELECT MMS_Vendor_Roll.TYPE, MMS_Vendor_Roll.IPT,
MMS_Vendor_Roll.CUSTOMER_2, Max(MMS_Vendor_Roll.GR_Temp) AS MaxOfGR_Temp,
MMS_Vendor_Roll.[PO CREATE DATE],
IIf([Details_Table]![IPT]="Tornado2",Mid([Details_Table]![COL_Link],9,10),[M
MS_Vendor_Roll]![ORDER REQUEST]) AS Expr2, MMS_Vendor_Roll.[OR LINE],
IIf([MaxOfGR_Temp]=Date(),"",[MaxOfGR_Temp]) AS GR_Date,
MMS_Vendor_Roll.VENDOR, Sum(MMS_Vendor_Roll.[QTY REQD]) AS [SumOfQTY REQD],
Sum(MMS_Vendor_Roll.[QTY O/S]) AS [SumOfQTY O/S],
First(MMS_Vendor_Roll.Proj_No) AS FirstOfProj_No, MMS_Vendor_Roll.[PART
NUMBER], MMS_Vendor_Roll.[PO NO], MMS_Vendor_Roll.[PO LINE],
Max(MMS_Vendor_Roll.[OR SCHED]) AS [MaxOfOR SCHED],
First(MMS_Vendor_Roll.BUYER) AS FirstOfBUYER, Min(MMS_Vendor_Roll.STATUS) AS
MinOfSTATUS, MMS_Vendor_Roll.[REQD DATE], MMS_Vendor_Roll.AQUISITION,
Max(MMS_Vendor_Roll.[PROM DATE]) AS [MaxOfPROM DATE], MMS_Vendor_Roll.[CONT
DATE], Max(MMS_Vendor_Roll.[PRICE TYPE]) AS [MaxOfPRICE TYPE],
Max(MMS_Vendor_Roll.[TOTAL STG PRICE]) AS [MaxOfTOTAL STG PRICE],
Max(Details_Table.Price_code) AS MaxOfPrice_code, Max(Details_Table.nsn) AS
MaxOfnsn, Max(Details_Table.PRI) AS MaxOfPRI,
Max(IIf(Left([MMS_Vendor_Roll]![Proj_No],3)="39V" Or
Left([MMS_Vendor_Roll]![Proj_No],3)="39N",0,[Details_Table]![Sell_PRICE]))
AS Sell_PRICE, [ORDER REQUEST] & [OR LINE] AS Expr1,
Max(Details_Table.FC_DATE) AS MaxOfFC_DATE,
COOP_Purchase_Orders_Table.Keyword
FROM (MMS_Vendor_Roll LEFT JOIN Details_Table ON (MMS_Vendor_Roll.Link =
Details_Table.MMS_Link) AND (MMS_Vendor_Roll.Link_Seq = Details_Table.Seq))
LEFT JOIN COOP_Purchase_Orders_Table ON Details_Table.PO_Number =
COOP_Purchase_Orders_Table.PO_Number
GROUP BY MMS_Vendor_Roll.TYPE, MMS_Vendor_Roll.IPT,
MMS_Vendor_Roll.CUSTOMER_2, MMS_Vendor_Roll.[PO CREATE DATE],
IIf([Details_Table]![IPT]="Tornado2",Mid([Details_Table]![COL_Link],9,10),[M
MS_Vendor_Roll]![ORDER REQUEST]), MMS_Vendor_Roll.[OR LINE],
MMS_Vendor_Roll.VENDOR, MMS_Vendor_Roll.[PART NUMBER], MMS_Vendor_Roll.[PO
NO], MMS_Vendor_Roll.[PO LINE], MMS_Vendor_Roll.[REQD DATE],
MMS_Vendor_Roll.AQUISITION, MMS_Vendor_Roll.[CONT DATE], [ORDER REQUEST] &
[OR LINE], COOP_Purchase_Orders_Table.Keyword
HAVING (((MMS_Vendor_Roll.IPT)<>"civil"));

re: Problems Converting 97 Query into 2003 format

John Ortt wrote:
[color=blue]
> Hi All,
>
> I have a database in Access 97 which works fine but our company is
> converting from NT4 to XP Pro and in the process changing Office 97 for
> Office 2003. I have tried to convert the 97 database into Access 2003 but
> the Autoexec macro falls over on the query below. I have tried running it
> separayely and the computer just hangs.
>
> I realise there is a lot of code below, but I'm hoping somebody may know
> what they are looking for...[/color]

I don't see any code at all, just an SQL statement.

Are you sure this is what the conversion fails on?

Did you compile the mdb in A97 first? Usually this will tell you were
problems may be in code (real code, not SQL statements) that can be
corrected.

I haven't used macros in many years, but can you remove the line that
has this query and successfully convert then? If so, have you tried
simply constructing the query right from scratch in A2003?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Problems Converting 97 Query into 2003 format

Sorry Tim,

I didn't explain correctly. The Autoexec is a macro and it simply
references the SQL query I had attached.

I tried running the query on it's own in both 97 and 2003 and the 2003
version hangs while the 97 one works (albeit fairly slowly).

I did try creating the entire query again but it didn't work.

I guess I will just have to try deleting sections of the query to try to
narrow down the failure point.

Thanks anyway Tim.



"Tim Marshall" <TIMMY!@PurplePandaChasers.Moertherium> wrote in message
news:cuiak7$31a$1@coranto.ucs.mun.ca...[color=blue]
> John Ortt wrote:
>[color=green]
> > Hi All,
> >
> > I have a database in Access 97 which works fine but our company is
> > converting from NT4 to XP Pro and in the process changing Office 97 for
> > Office 2003. I have tried to convert the 97 database into Access 2003[/color][/color]
but[color=blue][color=green]
> > the Autoexec macro falls over on the query below. I have tried running[/color][/color]
it[color=blue][color=green]
> > separayely and the computer just hangs.
> >
> > I realise there is a lot of code below, but I'm hoping somebody may know
> > what they are looking for...[/color]
>
> I don't see any code at all, just an SQL statement.
>
> Are you sure this is what the conversion fails on?
>
> Did you compile the mdb in A97 first? Usually this will tell you were
> problems may be in code (real code, not SQL statements) that can be
> corrected.
>
> I haven't used macros in many years, but can you remove the line that
> has this query and successfully convert then? If so, have you tried
> simply constructing the query right from scratch in A2003?
> --
> Tim http://www.ucs.mun.ca/~tmarshal/
> ^o<
> /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
> /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me[/color]