Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    22

    Unhappy Unanswered: A problem filtering with sql and dates :S

    Hi

    I have a question on SQL, the question is this, I have a big table on access, and that I want is to filter this table into one record and uses this record to populate two textbox.

    All seems to be right but when I run the code, send me an error msg:

    Error 3021 in ejecution time

    Either BOF or EOF is true, or the current record has been deleted requested operation requires a current record

    here's my code:

    Code:
    Dim strDate As String
    Dim strSQL As String
    
    strDate = Me.txtActualizaFechaAnterior.Value
    strSQL = "SELECT * FROM tblTarifas WHERE tblTarifas.Tarifa = 'H-M' AND tblTarifas.Zona ='Baja California Sur' AND tblTarifas.Fecha = #" & strDate & "# "
    
    Set MyConn = New ADODB.Connection
    MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\DAVIDG\Escritorio\new1\Data.mdb;"
    MyConn.Open
    
    Set rs = MyConn.Execute(strSQL)
    Debug.Print strSQL
    
    Me.txtDemandaAnterior.Value = rs.Fields("Demanda").Value
    Me.txtEnergiaAnterior.Value = rs.Fields("Punta").Value
    
    Me.txtDemanda.Value = Round(Me.txtDemandaAnterior.Value * Me.txtFAM, 2)
    Me.txtEnergia.Value = Round(Me.txtEnergiaAnterior.Value * Me.txtFAM, 3)
    
    MyConn.Close


    here's my SQL out (the registry show on that exist)

    Code:
    Select * FROM tblTarifas WHERE tblTarifas.Tarifa = 'H-M' AND tblTarifas.Zona ='Baja California Sur' AND tblTarifas.Fecha = #01/05/2003#
    and my error occurs on this line:

    Code:
    Me.txtDemandaAnterior.Value = rs.Fields("Demanda").Value
    Any suggestions or comments I'll appreciate it

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    it is a big possiblity your sql query returns no result. Therefore Access throws error when you try to put a nonexistant data into a variable.
    ghozy.

  3. #3
    Join Date
    Aug 2004
    Posts
    22

    Unhappy =(, thnks

    I have a question... how can I know if my rs don't find anything, I'm sure that the record that I try to found exist, but maybe access can't find them.

    Also I see that the problem occurs in the Date statement in SQL, when I delete the Date on SQL statement all work fine, so from this is the problem I try to only filter by date but the same problem occurs.

    my SQL

    Code:
    SELECT * FROM tblTarifas WHERE tblTarifas.Fecha =  #01/05/2003#
    too simple, but an error appears, I see that the record exist but I thing Im put some wrong on SQL.

    Hi

    David García

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    first of all check if your date field has time information in it. if there is time information in the field comparing to plain date would return no results.
    actualy #01/05/2003# = #01/05/2003 00:00:00#

    you can check your if your recordset is empty 2 ways:
    Code:
    #1
    if rs.RecordCount=0 then YourRecordsetIsEmpty
     
    #2
    if rs.EOF then YourRecordsetIsEmpty
    #2 option looks weird but if your recordset points EOF when you open, it is usualy means there is no record in the recordset.
    ghozy.

Posting Permissions

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