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...
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;"
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.Open sqlContact, CurrentProject.Connection
If .BOF = False Then
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
Set rstContact = Nothing
Me.txtContact = strPrvInfo
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.