Access Query via VBA
Results 1 to 4 of 4

Thread: Access Query via VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Access Query via VBA

    I need to return some access records where a field doesn't contain two specific text strings.

    I am using the following to build the query
    Code:
    strQuery = "SELECT InvDate, Agent, BoxRef, Supplier, Invoice FROM SageData WHERE (Supplier Not Like '*CNG*' AND Supplier Not Like '*Swalec*') AND InvDate = #" & Format(Range("Controls_ExcInvDate"), "m/d/yyy") & "#"
    This executes but returns nothing and there are records in the table that meet the criteria so I'm assuming the 'Not Like' part isn't correct.

    I have tried using a % instead of a * and multiple variants of using parenthesis to no avail......

    Could anyone correct this for me?

    TIA

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,127
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Access Query via VBA

    hello

    I'm pretty sure for Excel VBA querying MS Access use % wildcard

    I think the problem is the date format with THREE y - m/d/yyy
    Likely that should be FOUR, m/d/yyyy

    Anytime like this I have a problem I debug.print the SQL & review it in the immediate window. (Or you can look in the locals window.)

    If you still have a problem, please debug.print & see if that reveals an issue. If still stuck, post back with the result.

    cheers
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


  3. #3
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query via VBA

    The issue was the date format....schoolboy error!!

    Thanks

  4. #4
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,127
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Access Query via VBA

    the sort of thing I'm expert at - I do errors like that every day
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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