Query works in Access, but not VB .NET


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Query works in Access, but not VB .NET

  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Query works in Access, but not VB .NET

    Hello,

    I'm a relatively new programmer, and I'm attempting to duplicate an Access query in to VB .NET. I expected this to be a simple copy/paste from Access, but that has not been the case. When I attempt to run the following query from Access, I get a ton of records. From VB .NET in a AccessDataSource control, I get nothing. Here's the query:

    <query removed>

    And here's the code (which has worked fine with other queries):

    <query removed>

    Any ideas would be greatly appreciated! I've burned 99% of my brain on this today. Thanks!
    Last edited by Brad Jones; 10-02-2012 at 10:35 AM.

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,262
    Quote Originally Posted by corygibbons
    "HAVING ((([dbo_dbStats Count Calls Monthly].Expr1)="06/2007"));"
    Oops you missed a couple of imbeded quotes: "06/2007" should be ""06/2007""

  3. #3
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,262
    Ok, I see the code now. You caught the quote problem there. Never mind!

  4. #4
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Is that Expr1 field a Date field or a text field in your table? If its a date field then you will want to wrap the value with the "#" chars.

    "HAVING ((([dbo_dbStats Count Calls Monthly].Expr1)=#06/2007#));"

    Or such.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  5. #5
    Join Date
    Jul 2007
    Posts
    3
    Hi Rob,

    Thanks for trying. It is a text field, but I tried the # characters just in case, and still received no data.

    What's strange is, when I change the INNER JOIN to a LEFT JOIN, I get partial data back, but not entirely what I'm looking for.

    If anyone else has any other ideas they would be much appreciated.

    Thanks again.

  6. #6
    Join Date
    Jul 2007
    Posts
    3
    Welp, looks like this was a lot easier than I thought!

    Found a 'like string*' statement in the dbo_dbStats Count Optouts Monthly, replaced with 'like string%' and worked like a charm.

    Thanks again for the help.

  7. #7
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    I didnt see that in the query but now that you posted i see there were some nested query calls to other queries where you obviously found the issue.

    The issue why it works in Access and not in your code is because within Access the * is the wild card character while when using ADO the % sign is the wild card character.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

Similar Threads

  1. Replies: 0
    Last Post: 01-07-2003, 02:20 PM
  2. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 12:22 PM
  3. Coding Access in VB
    By Scott in forum VB Classic
    Replies: 1
    Last Post: 05-12-2001, 04:17 PM
  4. Russell Jones
    By Bill Seddon in forum .NET
    Replies: 85
    Last Post: 02-01-2001, 02:49 AM
  5. Replies: 3
    Last Post: 05-12-2000, 04:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center