Return Nothing Or Msgbox On Null SQL

[Shaunk23]

I have an SQL statement on a listbox which displays cash receipt tickets. In the event the user enters a date range where there are no records i get the Invalid use of null error.. How can i force it to either display nothing or a msgbox saying there is no records. Here is my statement..

SQL = " SELECT CashReceiptTicket.TicketID, CashReceiptTicket.BankAccount, CashReceiptTicket.TypeOfDeposit, CashReceiptTicket.TotalDeposit," & _
" CashReceiptTicket.TicketCount, CashReceiptTicket.ID FROM CashReceiptTicket" & _
" WHERE (((CashReceiptTicket.DepositDate) Between [Forms]![CRMain]![CRdaterange1] And [Forms]![CRMain]![CRdaterange2]))" & _
" ORDER BY CashReceiptTicket.TicketID DESC , CashReceiptTicket.DepositDate DESC;"

 

[pr2-eugin]

How are you assigning the value to the Listbox? Do you have a query that is opened through the DoCmd? or you executing the query using VBA?

 

[pr2-eugin]

If you are using VBA to execute the SQL query you can check if there is any information inside the RecordSet based on that you can decide which action to choose..

If Not [COLOR=Red]temp_rst1.[/COLOR]RecordCount < 0 Then
    MsgBox("NOTHING TO SEE HERE")
Else
    [COLOR=SeaGreen]'Do what you intend to do. i.e. Place the values inside the Listbox[/COLOR]
End If

EDIT-NOTE: The RED highlighted part should be the name of the recordset that you have used..

 

[Shaunk23]

I had assigned it as the listbox row source.. if need be i can put it in the vb code. Is there a way to do that without? Let me know thanks.

 

[Shaunk23]

Here is the complete Sub... Now im getting errors with it wanting parameters when the dates are filled in

Private Sub CRmainSearch_Click()

'*************************************************************************
' Purpose:'' Checks to ensure dates are entered in the date field before
' setting the listbox source on main cash receipt page to the months
' entered by the current user.
'*************************************************************************
On Error GoTo ErrorHandler

Dim CRSQL As String
Dim DB As Database
Dim Temp_RS As Recordset


If Isnull(CRdaterange1) = True Then
MsgBox " You Must Enter A Range Of Dates!", vbOKOnly, "Missing Information"
Me.CRdaterange1.SetFocus
Exit Sub
End If

If Isnull(CRdaterange2) = True Then
MsgBox " You Must Enter A Range Of Dates!", vbOKOnly, "Missing Information"
Me.CRdaterange2.SetFocus
Exit Sub
End If

CRSQL = "SELECT CashReceiptTicket.TicketID, CashReceiptTicket.BankAccount, CashReceiptTicket.TypeOfDeposit, CashReceiptTicket.TotalDeposit," & _
" CashReceiptTicket.TicketCount, CashReceiptTicket.ID FROM CashReceiptTicket" & _
" WHERE (((CashReceiptTicket.DepositDate) Between Forms![CRMain]![CRdaterange1] And [Forms]![CRMain]![CRdaterange2]))" & _
" ORDER BY CashReceiptTicket.TicketID DESC , CashReceiptTicket.DepositDate DESC;"

Set DB = CurrentDb
Set Temp_RS = DB.OpenRecordset(CRSQL)

If Temp_RS.RecordCount < 1 Then
MsgBox ("NOTHING TO SEE HERE")
Else
Me.CRMAINList.RowSource = CRSQL
Me.CRMAINList.Requery
Call UpdateCashReceiptMain
End If

ExitRoutine:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume ExitRoutine

 

[pr2-eugin]

Okay.. when enclosing values from forms to br used in queries, you have to use " & variable_Name & ".. so you should have the Query as..

CRSQL = "SELECT CashReceiptTicket.TicketID, CashReceiptTicket.BankAccount, CashReceiptTicket.TypeOfDeposit, CashReceiptTicket.TotalDeposit," & _
" CashReceiptTicket.TicketCount, CashReceiptTicket.ID FROM CashReceiptTicket" & _
" WHERE (((CashReceiptTicket.DepositDate) Between " & " & Forms![CRMain]![CRdaterange1] & " & " And " & " & [Forms]![CRMain]![CRdaterange2] & " & "))" & _
" ORDER BY CashReceiptTicket.TicketID DESC , CashReceiptTicket.DepositDate DESC;"

Try the above.. it should work.. NOT TESTED THOUGH..

 

[Shaunk23]

That doesnt seem to work. It says syntax error using the above. Mine didnt throw syntax error. still also doesnt show null

Private Sub CRmainSearch_Click()

'*************************************************************************
' Purpose:'' Checks to ensure dates are entered in the date field before
' setting the listbox source on main cash receipt page to the months
' entered by the current user.
'*************************************************************************
On Error GoTo ErrorHandler

Dim CRSQL As String
Dim DB As Database
Dim Temp_RS As Recordset



If Isnull(CRdaterange1) = True Then
MsgBox " You Must Enter A Range Of Dates!", vbOKOnly, "Missing Information"
Me.CRdaterange1.SetFocus
Exit Sub
End If

If Isnull(CRdaterange2) = True Then
MsgBox " You Must Enter A Range Of Dates!", vbOKOnly, "Missing Information"
Me.CRdaterange2.SetFocus
Exit Sub
End If


CRSQL = "SELECT CashReceiptTicket.TicketID, CashReceiptTicket.BankAccount, CashReceiptTicket.TypeOfDeposit, CashReceiptTicket.TotalDeposit," & _
" CashReceiptTicket.TicketCount, CashReceiptTicket.ID FROM CashReceiptTicket" & _
" WHERE (((CashReceiptTicket.DepositDate) Between " & " & Forms![CRMain]![CRdaterange1] & " & " And " & " & [Forms]![CRMain]![CRdaterange2] & " & "))" & _
" ORDER BY CashReceiptTicket.TicketID DESC , CashReceiptTicket.DepositDate DESC;"

Set DB = CurrentDb
Set Temp_RS = DB.OpenRecordset(CRSQL)

If Temp_RS.RecordCount < 1 Then
MsgBox ("NOTHING TO SEE HERE")
Else
Me.CRMAINList.RowSource = CRSQL
Me.CRMAINList.Requery
Call UpdateCashReceiptMain
End If

ExitRoutine:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume ExitRoutine


End Sub

 

[pr2-eugin]

Hi.. I am sorry.. Now I tested it.. try the following.. If you encounter amy errors post back..

CRSQL = "SELECT CashReceiptTicket.TicketID, CashReceiptTicket.BankAccount, CashReceiptTicket.TypeOfDeposit, CashReceiptTicket.TotalDeposit," & _
" CashReceiptTicket.TicketCount, CashReceiptTicket.ID FROM CashReceiptTicket" & _
" WHERE (((CashReceiptTicket.DepositDate) Between " & Forms![CRMain]![CRdaterange1] & " And " & [Forms]![CRMain]![CRdaterange2] & " ))" & _
" ORDER BY CashReceiptTicket.TicketID DESC , CashReceiptTicket.DepositDate DESC;"

Also check if it is Dim DB = ADO.Database also make sure the RowSource Type is "Table/Query"

 

[Shaunk23]

Perfect!! Thank you very much. Now is there a possibility of displaying the same "list" with column headers etc.. just empty? So keeping the rowsource of the list as the CRSQL - just displaying without records.. for explanation.. Tickets are entered each day depending on my companies deposits.. So if there is no Ticket on 05/31/12 The user will be entering one... If there is one already there then the second deposit has a subid that is increased by 1. Ticket id's for today would look like this

053112-1 for the first , 053112-2 for the second etc... So if there is no ticket on that day it still can show.. the user will be adding the first ticket. Make sense?

 

[pr2-eugin]

Sorry I tried reading that over and over againg but I cannot understand.. can you please rephrase it and give an example?

 

[Shaunk23]

Sure. Alright - If there is results the listbox displays the data... if there is 10 results in that date range it will show 10 results. If there is 0 it will now show a msgbox as you helped me with above. Instead of showing a message box i would like the list to show empty. Is that possible? So it would be the same as if there was results in it.. column headings etc. It just wont have any results listed.. Maybe below will explain.

Searching 05/01/2012 - 05/01/2012
Listbox would show

TICKET ID | DEPOSIT DATE | AMOUNT
050112-1 | 05/01/12 | $500.00
050112-2 | 05/01/12 | $500.00


So there was two tickets entered... Now if there was NO tickets entered on that day.. i would like it to show like this... Just blank. Make sense?

TICKET ID | DEPOSIT DATE | AMOUNT
| |
| |

 

[pr2-eugin]

Just remove the MsgBox statement and place..

Me.CRMAINList.RowSource = CRSQL

Thisnk that should work..