Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Qury (Access XP)

    Could some kind person please help.

    I have managed to write a delete query which does exactly what I would like, but I cannot work out how to phrase it in VBA

    Could anyone help please.

    Even a pointer in the right directtion would help. I'm sure I can get the syntax eventually if given a decent nudge in the right direction

    Thanks

    Code is below

    Colin




    DELETE tblBankAccount.*, tblBankAccount.DateInput, Left([Description],5) AS Expr1, Left([Description],7) AS Exp2, Left([Description],3) AS Exp3
    FROM tblBankAccount
    WHERE (((tblBankAccount.DateInput)=Date()) AND ((Left([Description],5))="Mthly")) OR (((tblBankAccount.DateInput)=Date()) AND ((Left([Description],7))="Ann S/O")) OR (((tblBankAccount.DateInput)=LastofPrevMonth(Date( ))) AND ((Left([Description],3))="Int"));

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL Qury (Access XP)

    You could set up the SQL in a text string and use the .execute or RunSql command to run it.

    Setup the string variable:
    strSQL = "DELETE tblBankAccount.*, tblBankAccount.DateInput, Left([Description],5) AS Expr1, Left([Description],7) AS Exp2, Left([Description],3) AS Exp3
    FROM tblBankAccount
    WHERE (((tblBankAccount.DateInput)=Date()) AND ((Left([Description],5))='Mthly')) OR (((tblBankAccount.DateInput)=Date()) AND ((Left([Description],7))='Ann S/O')) OR (((tblBankAccount.DateInput)=LastofPrevMonth(Date( ))) AND ((Left([Description],3))='Int'));"

    Be sure to enclose the Mthly with single quotes instead of double quotes.

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Qury (Access XP)

    Thanks Pat

    Works just fine

    I never even thought of its as simple as that.

    Colin

Posting Permissions

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