Results 1 to 5 of 5
  1. #1
    Join Date
    May 2005
    Posts
    150

    Unanswered: Difference between query and VB SQL.. same but diff

    ok this makes no sense to me...

    when I 'create a query in design view' I get the results that I am looking for...

    I then took the SQL statement and used it in VB.

    When I use the created query it returns 6 results

    the vb query returns 1 result

    see code to see that everything seems right...

    Code:
    Dim rstContact As ADODB.Recordset
    Dim sqlContact As String
    Dim PrvContact As String
    Dim PrvEMail As String
    Dim strPrv As String
    Dim strPrvInfo As String
    
    Set rstContact = New ADODB.Recordset
    
    sqlContact = "SELECT Nz([FirstName],'') & ' ' & Nz([LastName],'') As CName, Contacts.emailaddress"
    sqlContact = sqlContact & " FROM (Contacts LEFT JOIN CustomerList ON Contacts.CompanyID = CustomerList.CustomerID) LEFT JOIN Billing ON CustomerList.Address = Billing.Address"
    sqlContact = sqlContact & " WHERE (((Billing.BillToID) =" & Me.txtID & ")) Or (((CustomerList.Company) Like '" & Me.txtCoName & "*'))"
    sqlContact = sqlContact & " GROUP BY Nz([FirstName],'') & ' ' & Nz([LastName],''), Contacts.emailaddress;"
    
    With rstContact
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockBatchOptimistic
    .Open sqlContact, CurrentProject.Connection
    
    If .BOF = False Then
    .MoveFirst
    End If
    
    Do Until .EOF = True
    
    MsgBox Nz(.Fields("CName").Value, "")
    PrvContact = Nz(.Fields("CName").Value, "") '& " " & Nz(.Fields("LastName").Value, "")
    PrvEMail = Nz(.Fields("emailaddress").Value, "")
    
    strPrv = "Contact Name: " & PrvContact & " ; E-Mail: " & PrvEMail
    
    strPrvInfo = strPrvInfo & vbCrLf & vbCrLf & strPrv
    
    .MoveNext
    Loop
    
    .Close
    End With
    
    Set rstContact = Nothing
    
    Me.txtContact = strPrvInfo

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    When this happen to me I

    Debug.print sqlContact

    Copy it from the the Immediate window Ctrl+G

    Then Paste That in my Query builder
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    May 2005
    Posts
    150
    nothing happens with that

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try changing the wildcard from * to %.
    Paul

  5. #5
    Join Date
    May 2005
    Posts
    150
    perfect... you the meng...

    thank you

Posting Permissions

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