Filtering a subform with a SQL statement using a text box.

[Anacronos]

I have a form which have a subform that is filtered by two things one is a Combobox which works fine; The other one is a text box in which short date is typed by the user, I have a mask in the text box so short date is forced but when entered and executed it returns no records... the subforms goes blank so I know is executed but still no matches the field that saves the date is short Date and the default value is Date() the field in the second table where the records are copied to is also short date the records are copied from one table to another one with a add query/consult? which also has 2 text boxes that works just beautifully ;( I´m hell of a new.

This is the code of the button

Private Sub cmdFiltrarpordia_Click()
Dim SQL As String



SQL = "SELECT TablaTemporal.Contacto, TablaTemporal.Total, TablaTemporal.Anticipo, TablaTemporal.Id, TablaTemporal.Nombre, TablaTemporal.Id_dia, TablaTemporal.Resta, TablaTemporal.Creado " _
& "FROM TablaTemporal " _
& "Where [Creado] = #" & Me.txtFecha & "# " _
& "ORDER BY Id_dia ASC"
Me.txtTot = DSum("[Total]", "[TablaTemporal]", "Creado = #" & Forms!Cort![txtFecha] & "# ")
Me.SbfrCorte.Form.RecordSource = SQL
End Sub

Also the txtTot with Dsum doesn´t work but I guess is the same issue.

Thanks in advance.

[ssanfu]

Troubleshooting time.....

What is Forms!Cort![txtFecha] when the code executes?
Is the SQL string properly formed?

Private Sub cmdFiltrarpordia_Click()
    Dim SQL As String

    SQL = "SELECT TablaTemporal.Contacto, TablaTemporal.Total, TablaTemporal.Anticipo, TablaTemporal.Id, TablaTemporal.Nombre, TablaTemporal.Id_dia, TablaTemporal.Resta, TablaTemporal.Creado " _
          & "FROM TablaTemporal " _
          & "Where [Creado] = #" & Me.txtFecha & "# " _
          & "ORDER BY Id_dia ASC"

    '----debugging----------------------------------------
    Debug.Print SQL

    MsgBox "the value for txtFecha is  " & Forms!Cort![txtFecha]
    '----debugging----------------------------------------


    Me.txtTot = DSum("[Total]", "[TablaTemporal]", "Creado = #" & Forms!Cort![txtFecha] & "# ")
    Me.SbfrCorte.Form.RecordSource = SQL
End Sub

Set a breakpoint at this line
Me.txtTot = DSum("[Total]", "[TablaTemporal]", "Creado = #" & Forms!Cort![txtFecha] & "# ")
so you can see what is happening

[Anacronos]

What is Forms!Cort![txtFecha] when the code executes? Where Creado = #12/02/2016# ORDER BY Id_dia ASC
Is the SQL string properly formed? I don´t understand this one xD

[ssanfu]

Did you execute the code with the debugging modifications?

What did the message box display?

Is the SQL string properly formed? I don´t understand this one xD

Set the breakpoint.
Execute the code
What is in the Immediate Window? Copy that line and post it here.

[Anacronos]

This is the SQL in the immediate window
SELECT TablaTemporal.Contacto, TablaTemporal.Total, TablaTemporal.Anticipo, TablaTemporal.Id, TablaTemporal.Nombre, TablaTemporal.Id_dia, TablaTemporal.Resta, TablaTemporal.Creado FROM TablaTemporal Where Creado = #12/02/2016# ORDER BY Id_dia ASC

The msgbox says 12/02/2016.

[ssanfu]

OK, two thing here. The form problem is separate from the DSum() issue.

You say the combo box filtering is working. Is the code for that different that the code for the text box?


The DSum should work. Is the date "12/02/2016" "mm/dd/yyyy" or "dd/mm/2016"?
If it is "dd/mm/yyyy", see http://www.allenbrowne.com/ser-36.html

[Anacronos]

The format was the problem, I used the link http://www.allenbrowne.com/ser-36.html it provided with some formating code.
Private Sub cmdFiltrarpordia_Click()
Dim SQL As String

SQL = "SELECT TablaTemporal.Contacto, TablaTemporal.Total, TablaTemporal.Anticipo, TablaTemporal.Id, TablaTemporal.Nombre, TablaTemporal.Id_dia, TablaTemporal.Resta, TablaTemporal.Creado " _
& "FROM TablaTemporal " _
& "Where TablaTemporal.Creado = #" & Format(Me.txtFecha, "mm\/dd\/yyyy") & "# " _
& "ORDER BY Id_dia ASC"
Me.txtTot = DSum("[Total]", "[TablaTemporal]", "Creado = #" & Format(Forms!Cort![txtFecha], "mm\/dd\/yyyy") & "# ")

Me.SbfrCorte.Form.RecordSource = SQL
End Sub

So the problem was that since I´m outside of EUA ( which I´ve never been) the format I use dd/mm/yyyy it´s correctly shown in the tables and if the forms but not in vba the prof provided by the link is with the queries where you can type a date as criteria in a date field and see it as you writed but when changed to sql it changes to mm/dd/yyyy.



Thank you so much :3, ssanfu .