Results 1 to 5 of 5

Thread: compare dates

  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Unanswered: compare dates

    Hi there
    Can some one help me with this issue. I have 2 text fields which user selects the date from calender control. Now i want to retreive data from table which falls in the range of these 2 text fields
    here is the code i have written. When i run this form it returns the resultset with no rows.(EMPTY resultset)

    sdate = CDate(Forms![ResolvedIncidentsbySevierity]![Text3])
    edate = CDate(Forms![ResolvedIncidentsbySevierity]![Text5])

    sql = "select IncidentsResolved.Severity,count(IncidentsResolved .Severity) from IncidentsResolved WHERE ((IncidentsResolved.[Arrival Time] >= " & sdate & ") AND (IncidentsResolved.[Arrival Time] <= " & edate & ")) GROUP BY IncidentsResolved.Severity"

    Set MyDB = DBEngine.Workspaces(0).OpenDatabase("D:\Reports\db 1.mdb")
    Set MySet = MyDB.OpenRecordset(sql, DB_OPEN_DYNASET)

    If (MySet.EOF = False And MySet.BOF = False) Then
    MySet.MoveFirst
    level1.Value = MySet.Fields(1)
    MySet.MoveNext
    level2.Value = MySet.Fields(1)
    MySet.MoveNext
    level3.Value = MySet.Fields(1)
    MySet.MoveNext
    ' level4.Value = MySet.Fields(1)
    Else
    MsgBox ("No data found")
    End If

    MySet.Close
    Set MyDB = Nothing


    I have also tried by replacing the above sql stmt to and still no luck

    sql = "select IncidentsResolved.Severity,count(IncidentsResolved .Severity) from IncidentsResolved WHERE ((IncidentsResolved.[Arrival Time] >= #sdate#) AND (IncidentsResolved.[Arrival Time] <= #edate#)) GROUP BY IncidentsResolved.Severity"

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Maybe you could try combining your two sqls ie.

    sql = "select IncidentsResolved.Severity,count(IncidentsResolved .Severity) from IncidentsResolved WHERE ((IncidentsResolved.[Arrival Time] >= #" & sdate & "#) AND (IncidentsResolved.[Arrival Time] <= #" & edate & "#)) GROUP BY IncidentsResolved.Severity"

    ??

    The other issue which could be a problem depends on you computer Local Setting (ie date format). If it isn't mm/dd/yy then, in VBA, you need to make it this way ie

    sql = "select IncidentsResolved.Severity,count(IncidentsResolved .Severity) from IncidentsResolved WHERE ((IncidentsResolved.[Arrival Time] >= #" & Format(sdate,"mm/dd/yy") & "#) AND (IncidentsResolved.[Arrival Time] <= #" & Format(edate,"mm/dd/yy") & "#)) GROUP BY IncidentsResolved.Severity"

    ps you ca also use
    IncidentsResolved.[Arrival Time] BETWEEN #" & Format(sdate,"mm/dd/yy") & "# AND #" & Format(edate,"mm/dd/yy") & "#"


    ??


    MTB

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just a quick something to note:

    This
    Code:
    If (MySet.EOF = False And MySet.BOF = False) Then
    MySet.MoveFirst
    level1.Value = MySet.Fields(1)
    MySet.MoveNext
    level2.Value = MySet.Fields(1)
    MySet.MoveNext
    level3.Value = MySet.Fields(1)
    MySet.MoveNext
    ' level4.Value = MySet.Fields(1)
    Else
    MsgBox ("No data found")
    End If
    Will throw an error if the EOF is reached before 4 records.

    You need to iterate through the recordset testing for eof on each pass instead of assuming you will always have enough records to fill your textboxes
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2008
    Posts
    4
    thanks mike. Its all working fine now.
    thanks george. I have used do while loop to fix that. This is what i have written and its all working good now

    If (MySet.EOF = False And MySet.BOF = False) Then
    MySet.MoveFirst
    Do While MySet.EOF = False
    If (MySet.Fields(0) = "Level 1") Then
    level1.Value = MySet.Fields(1)
    End If
    If (MySet.Fields(0) = "Level 2") Then
    level2.Value = MySet.Fields(1)
    End If
    If (MySet.Fields(0) = "Level 3") Then
    level3.Value = MySet.Fields(1)
    End If
    If (MySet.Fields(0) = "Level 4") Then
    level4.Value = MySet.Fields(1)
    End If
    MySet.MoveNext
    Loop
    Else
    MsgBox ("No data found")
    End If

    MySet.Close
    Set MyDB = Nothing

    If (level1.Value = "") Then
    level1.Value = 0
    End If

    If (level2.Value = "") Then
    level2.Value = 0
    End If

    If (level3.Value = "") Then
    level3.Value = 0
    End If

    If (level4.Value = "") Then
    level4.Value = 0
    End If

  5. #5
    Join Date
    Apr 2008
    Posts
    4

    how to add data from select query in to a MS ACCESS form

    Hi there
    I have got an other query. I am trying to display the bunch of data retreived from SELECT query in the form. I tried using the grid control to display and iam getting the runtime error 438 Object doesnt support this property or method.
    Code i have written is

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim SQL As String

    Set cnn = CurrentProject.Connection
    SQL = "SELECT [weekly data].[Case ID], [weekly data].[SLA Violated], [weekly data].[End User Department], [weekly data].[Affected End User Name], [weekly data].Severity, [weekly data].[Case Category], [weekly data].Status, [weekly data].[Arrival Time], [weekly data].[Resolved Time], [weekly data].[Time Open], [weekly data].[Business App Affected], [weekly data].Fix, [weekly data].Summary, [weekly data].Description, [weekly data].[Customer Satisfaction Survey], [weekly data].[Root Cause], [weekly data].[Sent to Clarify], [weekly data].[Clarify ID], [weekly data].[Configuration Item], [weekly data].[Date of First Transfer], [weekly data].[Latest Status Change to 3-Hold], [weekly data].[Work Log] FROM [weekly data]" ' WHERE [weekly data].[SLA Violated]= " & yes & " "

    Set rst = New ADODB.Recordset

    rst.CursorLocation = adUseClient
    rst.Open SQL, cnn, adOpenStatic, adLockBatchOptimistic

    Set Grid4.DataSource = rst

    Can some one give me a suggestion of using this better way. I also need the "SLA Violation" field should be editable (ie i should have an functionality of chenging from yes to no or viceversa and provide a text field to enter the valid reason and store back in the database)

    Much appreciate if you can tell me the code what to include to get this done.

Posting Permissions

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