Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax Error (2003)

    Have copied across SQL from a query into vba. Query works fine, but in the VBA window I keep getting a sytax error for the following part of the statement:

    "" AS CurrencyValue, FAR.ALMItemNumber AS IndItemNo, FAR.ALMCleaned INTO NewTable FROM FAR WHERE (((FAR.Disposed)=0));

    Can anyone see what the problem is????? Many thanks.

    Inafix

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Syntax Error (2003)

    Can you show the full query rather than just one line?

    The SQL view of a quey always spreads it across multiple lines. The problem may be the way you join these together in VBA.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax Error (2003)

    Hi John - I have got line breaks in there and have moved them about to see if it made any difference (it didn't). At the moment it stands:

    CurrentDb.Execute "SELECT FAR.Division, '600' AS Company, FAR.FinFleet AS FixedAsset, '1' AS SubNumber, IIf([Depr To Date]<[Cost],'1','8') AS Status, Left([ALMItemCode],16) AS Name, FAR.ALMItemCode AS TextLine1, FAR.[Serial number] AS TextLine2, FAR.[BS Type] AS [Fixed Asset Type], "" AS FixedAssetGroup, Format([Acquisition Date],'ddmmyyyy') AS Acquisition, Format([Acquisition Date],'ddmmyyyy') AS [Activiation Date], '1' AS FAQuantity, Left([Serial number],14) AS SerialNo, "" AS UserDefined1, "" AS AccountingDim1, "" AS AccountingDim2, "" AS AccountingDim3, "" AS AccountingDim4, "" AS AccountingDim5, "" AS AccountingDim6, IIf([Depr Method]='S',1,IIf([Depr Method]='R',2)) AS DepreciationType, "" AS DepreciationMethod, IIf[Depreciation Rate]>0,100/[Depreciation Rate]*12,"" AS LifetimeMonths, FAR.[Depreciation Rate] AS DeprecationShare, [Cost]/100*[Restricted %] AS Stop, 'GBP' AS [Currency], '1' AS ExchangeRate," & _
    """ AS CurrencyValue, FAR.ALMItemNumber AS IndItemNo, FAR.ALMCleaned INTO MOVEX1 FROM FAR WHERE (((FAR.Disposed)=0));"

    Cheers! Ina

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Syntax Error (2003)

    I think I know what it is.

    Double quotes are used to surround the SQL statements, so you can't also embed them in the SQL.

    Change the embedded double quotes into single quotes.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •