Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Those maddening String Operators (in SQL) (Office 2000 Sr1a)

    Once again I'm battling with the mysterious behaviour of concatenation of strings within an SQL statement in Access VBA.
    This doesn't work, why?
    "SELECT Transactions.Date, Transactions.ClearanceDate, Transactions.SourceAccountID, Transactions.TargetAccountID ,Transactions.[Amount AUD], Transactions.Description FROM Transactions WHERE(((Transactions.ClearanceDate)=" & ![Clearance Date] & ") AND ((Transactions.[Amount AUD])=" & ![Credit] & ") AND <font color=red>((Transactions.Description)= '" & !OtherEntity & "'" & </font color=red> "))"
    There must be some simple trick to make this function in an Openrecordset statement.
    Any clues please???

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Those maddening String Operators (in SQL) (Office 2000 Sr1a)

    Hi David,
    I can't actually see anything wrong with the syntax you've highlighted. Are you actually getting an error or just not returning any records?
    I note that you've got a date field in your criteria - in Access VBA, you'll need to enclose this between two # signs - i.e.:
    ((Transactions.ClearanceDate)=#" & ![Clearance Date] & "#)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Those maddening String Operators (in SQL) (Office 2000 Sr1a)

    Thanks Rory. I get a result - and empty set- when I know that there should be one record in the recordset returned. But I have now realized that it is not the string part but the date part that is giving trouble. I put in the # signs but it is still unreliable because of SQL defaulting to the American format whenever there is ambiguity eg 4/12/01 means April the 12th but in Australia it means the 4th December. If the date is unambiguous - such as 26/12/01 SQL reverts to the Australian format it seems so the need for American formatting is not strict. The trouble is my tables store dates in our format so is there a handy function (to save me writing one) that converts Australian to USA format for use in SQL statements?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Those maddening String Operators (in SQL) (Office 2000 Sr1a)

    Perhaps something like:
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Those maddening String Operators (in SQL) (Office 2000 Sr1a)

    Yes I think that's it but will subject it to further testing as who knows what strange exception may arise?

Posting Permissions

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