Using SQL in VBA (1 Viewer)

[Hecronis]

Hey, I have a list box on a form that I would like to ba able to sort either by the person's name, receipt number, or by transaction date. I had found another forum post that suggested the person change the row source using vba when a button is pressed. I tried this method and it worked until I got the query the way I wanted. before I was able to fit it all in one line in VBA but now it's too big.

Here's what I have:

Private Sub Date_Sort_Click()
Rent_Payments.RowSource = "SELECT QryRent_LastPaymentDate.Receipt_Number, QryRent_LastPaymentDate.Payment_Date, QryRent_LastPaymentDate.Last_Name, QryRent_LastPaymentDate.First_Name, QryRent_LastPaymentDate.Payment_RentTransaction, TblData_PaymentReason.Payment_Reason, QryRent_LastPaymentDate.ID_RentTransaction, QryRent_LastPaymentDate.ID_Number1, QryRent_LastPaymentDate.Receipt_Number1, QryRent_LastPaymentDate.Start_Date, QryRent_LastPaymentDate.End_Date FROM QryRent_LastPaymentDate INNER JOIN TblData_PaymentReason ON QryRent_LastPaymentDate.Reason = TblData_PaymentReason.ID_PaymentReason" & _
                        "WHERE (((QryRent_LastPaymentDate.Receipt_Number1) Like [Forms]![FrmRent_Receipts]![Receipt_Number] & " * " Or (QryRent_LastPaymentDate.Receipt_Number1) = 1) And ((QryRent_LastPaymentDate.Start_Date) >= [Forms]![FrmRent_Receipts]![Start_Date] Or (QryRent_LastPaymentDate.Start_Date) = 1) And ((QryRent_LastPaymentDate.End_Date) <= [Forms]![FrmRent_Receipts]![End_Date] Or (QryRent_LastPaymentDate.End_Date) = 1))" & _
                        "ORDER BY QryRent_LastPaymentDate.Payment_Date"
Me.Rent_Payments.Requery
End Sub

Private Sub Name_Sort_Click()
Rent_Payments.RowSource = "SELECT QryRent_LastPaymentDate.Receipt_Number, QryRent_LastPaymentDate.Payment_Date, QryRent_LastPaymentDate.Last_Name, QryRent_LastPaymentDate.First_Name, QryRent_LastPaymentDate.Payment_RentTransaction, TblData_PaymentReason.Payment_Reason, QryRent_LastPaymentDate.ID_RentTransaction, QryRent_LastPaymentDate.ID_Number1, QryRent_LastPaymentDate.Receipt_Number1, QryRent_LastPaymentDate.Start_Date, QryRent_LastPaymentDate.End_Date FROM QryRent_LastPaymentDate INNER JOIN TblData_PaymentReason ON QryRent_LastPaymentDate.Reason = TblData_PaymentReason.ID_PaymentReason" & _
                        "WHERE (((QryRent_LastPaymentDate.Receipt_Number1) Like [Forms]![FrmRent_Receipts]![Receipt_Number] & " * " Or (QryRent_LastPaymentDate.Receipt_Number1) = 1) And ((QryRent_LastPaymentDate.Start_Date) >= [Forms]![FrmRent_Receipts]![Start_Date] Or (QryRent_LastPaymentDate.Start_Date) = 1) And ((QryRent_LastPaymentDate.End_Date) <= [Forms]![FrmRent_Receipts]![End_Date] Or (QryRent_LastPaymentDate.End_Date) = 1))" & _
                        "ORDER BY QryRent_LastPaymentDate.Last_Name"

Me.Rent_Payments.Requery
End Sub

Private Sub Receipt_Sort_Click()
Rent_Payments.RowSource = "SELECT QryRent_LastPaymentDate.Receipt_Number, QryRent_LastPaymentDate.Payment_Date, QryRent_LastPaymentDate.Last_Name, QryRent_LastPaymentDate.First_Name, QryRent_LastPaymentDate.Payment_RentTransaction, TblData_PaymentReason.Payment_Reason, QryRent_LastPaymentDate.ID_RentTransaction, QryRent_LastPaymentDate.ID_Number1, QryRent_LastPaymentDate.Receipt_Number1, QryRent_LastPaymentDate.Start_Date, QryRent_LastPaymentDate.End_Date FROM QryRent_LastPaymentDate INNER JOIN TblData_PaymentReason ON QryRent_LastPaymentDate.Reason = TblData_PaymentReason.ID_PaymentReason" & _
                        "WHERE (((QryRent_LastPaymentDate.Receipt_Number1) Like [Forms]![FrmRent_Receipts]![Receipt_Number] & " * " Or (QryRent_LastPaymentDate.Receipt_Number1) = 1) And ((QryRent_LastPaymentDate.Start_Date) >= [Forms]![FrmRent_Receipts]![Start_Date] Or (QryRent_LastPaymentDate.Start_Date) = 1) And ((QryRent_LastPaymentDate.End_Date) <= [Forms]![FrmRent_Receipts]![End_Date] Or (QryRent_LastPaymentDate.End_Date) = 1))" & _
                        "ORDER BY QryRent_LastPaymentDate.Receipt_Number"
Me.Rent_Payments.Requery
End Sub

I honestly dont know if this is the best way to go about doing what I want. I'm open to suggestions.

 

[JHB]

..before I was able to fit it all in one line in VBA but now it's too big.
..

And what is the problem with that?

 

[Hecronis]

I can't get it to work now? I have limited knowledge about SQL and even less about how to use it in VBA.

 

[JHB]

Okay!
What is the name of the form in which you try to set the RowSource?
Else post your database with some sample data.

 

[Hecronis]

Lol I would like to just send the database but i wouldn't know how to trim it down. The name of the form that the list box is on? It's called FrmRent_Receipt

 

[JHB]

Lol I would like to just send the database but i wouldn't know how to trim it down.

If it is to big do a Compact/Repair and then zip it. If it is the data, then just take some of it out and replace it with fake data, do it on a copy of the database.

Info:
If you query for a date in MS-Access it must be US-format = mm/dd/yy.
Remember to make a space at the end when you SQL-String start on a new line.
This is wrong:

TblData_PaymentReason.ID_PaymentReason" & _

This is correct:

TblData_PaymentReason.ID_PaymentReason " & _

Try the below code, I couldn't test is because of missing data, so maybe some of the parentheses are wrong set.

Private Sub Date_Sort_Click()
  Me.Rent_Payments.RowSource = "SELECT QryRent_LastPaymentDate.Receipt_Number, QryRent_LastPaymentDate.Payment_Date, QryRent_LastPaymentDate.Last_Name, QryRent_LastPaymentDate.First_Name, QryRent_LastPaymentDate.Payment_RentTransaction, TblData_PaymentReason.Payment_Reason, QryRent_LastPaymentDate.ID_RentTransaction, QryRent_LastPaymentDate.ID_Number1, QryRent_LastPaymentDate.Receipt_Number1, QryRent_LastPaymentDate.Start_Date, QryRent_LastPaymentDate.End_Date FROM QryRent_LastPaymentDate INNER JOIN TblData_PaymentReason ON QryRent_LastPaymentDate.Reason = TblData_PaymentReason.ID_PaymentReason " & _
                        "WHERE (((QryRent_LastPaymentDate.Receipt_Number1) Like " & Me.[Receipt_Number] & " * ' Or (QryRent_LastPaymentDate.Receipt_Number1) = 1) And ((QryRent_LastPaymentDate.Start_Date) >= #" & Format(Me.[Start_Date], "mm/dd/yyyy") & "# Or (QryRent_LastPaymentDate.Start_Date) = 1) And ((QryRent_LastPaymentDate.End_Date) <= #" & Format(Me.[End_Date], "mm/dd/yyyy") & "# Or (QryRent_LastPaymentDate.End_Date) = 1)) " & _
                        "ORDER BY QryRent_LastPaymentDate.Payment_Date"
  Me.Rent_Payments.Requery
End Sub

 

[Hecronis]

I tried the code and it didn't seem to work. Mind if I pm the db to you? How do I attach the it?

 

[arnelgp]

you can simplify your code by making Constants and assigning the correct constant to the recordsource of the combo:

Const ComboSource As String = "SELECT QryRent_LastPaymentDate.Receipt_Number, QryRent_LastPaymentDate.Payment_Date, QryRent_LastPaymentDate.Last_Name, QryRent_LastPaymentDate.First_Name, QryRent_LastPaymentDate.Payment_RentTransaction, TblData_PaymentReason.Payment_Reason, QryRent_LastPaymentDate.ID_RentTransaction, QryRent_LastPaymentDate.ID_Number1, QryRent_LastPaymentDate.Receipt_Number1, QryRent_LastPaymentDate.Start_Date, QryRent_LastPaymentDate.End_Date FROM QryRent_LastPaymentDate INNER JOIN TblData_PaymentReason ON QryRent_LastPaymentDate.Reason = TblData_PaymentReason.ID_PaymentReason" & _
                        "WHERE (((QryRent_LastPaymentDate.Receipt_Number1) Like [Forms]![FrmRent_Receipts]![Receipt_Number] & " * " Or (QryRent_LastPaymentDate.Receipt_Number1) = 1) And ((QryRent_LastPaymentDate.Start_Date) >= [Forms]![FrmRent_Receipts]![Start_Date] Or (QryRent_LastPaymentDate.Start_Date) = 1) And ((QryRent_LastPaymentDate.End_Date) <= [Forms]![FrmRent_Receipts]![End_Date] Or (QryRent_LastPaymentDate.End_Date) = 1))"
Const OrderByDate As String = "QryRent_LastPaymentDate.Payment_Date"
Const OrderByLastName As String = "QryRent_LastPaymentDate.Last_Name"
Const OrderByReceipt As String = "QryRent_LastPaymentDate.Receipt_Number"

Private Sub Date_Sort_Click()
Call ChangeOrder(OrderByDate)
End Sub

Private Sub Name_Sort_Click()
Call ChangeOrder(OrderByLastName)
End Sub

Private Sub Receipt_Sort_Click()
Call ChangeOrder(OrderByReceipt)
End Sub


Private Sub ChangeOrder(ByVal strOrder As String)
Dim varSelected As Variant
' check and save if there is an item selected on the combo
If [Rent_Payments].ListIndex <> -1 Then varSelected = [Rent_Payment]
' reset rowsource
[Rent_Payments].RowSource = ""
' change order
[Rent_Payments].RowSource = ComboSource & " Order By " & strOrder
' go back to previous selection if there is any
If Len(varSelected & "") <> 0 Then [Rent_Payments] = [Rent_Payments].ItemData(varSelected)
End Sub

 

[frozbie]

Hecronis, JHB and arnelgp have made good suggestions.
To these I would add:
In order for you to understand what is going wrong, it would be helpful if you can see the SQL that is actually being run.

'// add a string variable to hold the SQL
dim strSQL as string
'// assign the SQL into this variable
strSQL = "SELECT QryRent_LastPaymentDate.Receipt_Number, QryRent_LastPaymentDate.Payment_Date, QryRent_LastPaymentDate.Last_Name, QryRent_LastPaymentDate.First_Name, QryRent_LastPaymentDate.Payment_RentTransaction, TblData_PaymentReason.Payment_Reason, QryRent_LastPaymentDate.ID_RentTransaction, QryRent_LastPaymentDate.ID_Number1, QryRent_LastPaymentDate.Receipt_Number1, QryRent_LastPaymentDate.Start_Date, QryRent_LastPaymentDate.End_Date  FROM QryRent_LastPaymentDate INNER JOIN TblData_PaymentReason ON QryRent_LastPaymentDate.Reason = TblData_PaymentReason.ID_PaymentReason " & _
                        "WHERE (((QryRent_LastPaymentDate.Receipt_Number1) Like [Forms]![FrmRent_Receipts]![Receipt_Number] & " * " Or (QryRent_LastPaymentDate.Receipt_Number1) = 1) And ((QryRent_LastPaymentDate.Start_Date) >= [Forms]![FrmRent_Receipts]![Start_Date] Or (QryRent_LastPaymentDate.Start_Date) = 1) And ((QryRent_LastPaymentDate.End_Date) <= [Forms]![FrmRent_Receipts]![End_Date] Or (QryRent_LastPaymentDate.End_Date) = 1)) " & _
                        "ORDER BY QryRent_LastPaymentDate.Payment_Date"
debug.print strSQL 
Rent_Payments.RowSource = strSQL

The debug.print will print out the SQL code to your immediate window and allow you to see what your process is actually trying to run. This may help you spot mistakes in the SQL. You can also copy the compiled SQL from the immediate window and run it directly in a new access query which may give you a more helpful error message if still not working.

(and as JHB said, you really need to watch out for not including spaces at the end of a row! Or alternatively, add: & vbcrlf before the underscore to force code onto a new line when being displayed/run. This makes it easier to view when debugging.)

Mark

 

[isladogs]

Further to all the previous comments, I prefer putting the space at the START of each line as its easier to see in code (so harder to forget).
I also suggest breaking it into shorter lines & indenting as shown below.
Obviously it's a matter of personal preference ....

'// add a string variable to hold the SQL
dim strSQL as string
'// assign the SQL into this variable
strSQL = "SELECT QryRent_LastPaymentDate.Receipt_Number, QryRent_LastPaymentDate.Payment_Date," &  _ 
		" QryRent_LastPaymentDate.Last_Name, QryRent_LastPaymentDate.First_Name," & _ 
		" QryRent_LastPaymentDate.Payment_RentTransaction, TblData_PaymentReason.Payment_Reason, " & _ 
		" QryRent_LastPaymentDate.ID_RentTransaction, QryRent_LastPaymentDate.ID_Number1," & _ 
		" QryRent_LastPaymentDate.Receipt_Number1, QryRent_LastPaymentDate.Start_Date," & _ 
		" QryRent_LastPaymentDate.End_Date" & _ 
	 " FROM QryRent_LastPaymentDate INNER JOIN TblData_PaymentReason" & _ 
		" ON QryRent_LastPaymentDate.Reason = TblData_PaymentReason.ID_PaymentReason " & _
       	 " WHERE (((QryRent_LastPaymentDate.Receipt_Number1) Like [Forms]![FrmRent_Receipts]![Receipt_Number] & " * "" & _
		" Or (QryRent_LastPaymentDate.Receipt_Number1) = 1)" & _
		" And ((QryRent_LastPaymentDate.Start_Date) >= [Forms]![FrmRent_Receipts]![Start_Date]" & _
		" Or (QryRent_LastPaymentDate.Start_Date) = 1)" & _
		" And ((QryRent_LastPaymentDate.End_Date) <= [Forms]![FrmRent_Receipts]![End_Date]" & _
		" Or (QryRent_LastPaymentDate.End_Date) = 1)) " & _
         " ORDER BY QryRent_LastPaymentDate.Payment_Date"

debug.print strSQL 

Rent_Payments.RowSource = strSQL

Also you may find my SQL2VBA converter useful
https://www.access-programmers.co.uk/forums/showthread.php?t=293372

 

[Mark_]

Ridders,
I've done similar, but I prefer to break each command and field out onto its own line. Makes it far easier to spot where you forgot something OR have fields transposed.

 

[isladogs]

Do you mean each line starting with
Strsql = strsql & "....."

Its another good method.
I prefer not to use that so I can more easily swop VBA back to SQL to test something as a query if needed.

As I said before, its personal preference in the end

 

[Hecronis]

I couldn't get the debug to work (I'm still pretty green on this stuff). I get a runtime error says type mismatch?

 

[Mark_]

Rather than using

debug.print strSQL

try

MsgBox strSQL

OR make your form much wider, add a text box (Txt_SQL) and put in

Me.Txt_SQL = strSQL

I've used the later because I could then cut and paste exactly what I was passing. Most depends on your personal preference.

 

[Cronk]

Hecronis

Can you post the sql that you're currently using that gives the run time error

I notice that code being posted in some of the responses looks problematic

eg

 ...Like [Forms]![FrmRent_Receipts]![Receipt_Number] & [COLOR="red"][B]" * ""[/B][/COLOR] &...

Note also that

Like Receipt_Number like " & " *"

and

Like Receipt_Number like " & "*"

may give different results because of the leading space