Using dates to search query records

[Michael Adams]

I have a form that has two (2) calendar dates 1. fromdatetext, 2. todatetext. I would like to be able to search the records from query "Emp_Lst_Frm_Qu ery" and give the sum results from the dates. I would like to use the "Sub_Cmd" button to activate the search. I will be commenting out the case statement so the button only does the search for the dates.

I would like to be able to put total records in TotalTxt and then populate Incident, Request, and ChangeOrder fields with the proper sums.

The calendar controls work perfect.

Here is the code for the entire form, I am currently doing things one piece at a time. Then when things are working the way I like, I then combine them.

Option Compare Database

 Dim Originatorcmb As ComboBox
 Private Sub ocxcalendar_Click()
    Originatorcmb.Value = ocxcalendar.Value
    Originatorcmb.SetFocus
    ocxcalendar.Visible = False
    Set Originatorcmb = Nothing
End Sub
Private Sub fromdatecmb_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    
    Set Originatorcmb = fromdatecmb
    
    ocxcalendar.Visible = True
    ocxcalendar.SetFocus
    If Not IsNull(Originatorcmb) Then
        ocxcalendar.Value = Originatorcmb.Value
    Else
        ocxcalendar.Value = Date
    End If
End Sub
Private Sub todatecmb_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Set Originatorcmb = todatecmb
    
    ocxcalendar.Visible = True
    ocxcalendar.SetFocus
    If Not IsNull(Originatorcmb) Then
        ocxcalendar.Value = Originatorcmb.Value
    Else
        ocxcalendar.Value = Date
    End If
End Sub
Private Sub Sub_Cmd_Click()

    Dim typeCount As Integer

    Select Case typeCount = DCount("[workedby]", "Emp_Lst_Frm_Query", "[workedby]= '" & Me.empcmb.Value & "'")
    Case Me.empcmb.Value = "1"
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[workedby] = 'MJA'")
        Me.TotalTxt.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Incident' And [workedby] = 'MJA'")
        Me.Inc_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Request' and [workedby] = 'MJA'")
        Me.Req_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Change Order' and [workedby]= 'MJA'")
        Me.CO_Text.Value = typeCount
        Me.Repaint
    Case Me.empcmb.Value = "2"
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[workedby] = 'MOB'")
        Me.TotalTxt.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Incident' And [workedby] = 'MOB'")
        Me.Inc_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Request' and [workedby] = 'MOB'")
        Me.Req_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Change Order' and [workedby]= 'MOB'")
        Me.CO_Text.Value = typeCount
        Me.Repaint
    Case Else
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[workedby] = 'SLT'")
        Me.TotalTxt.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Incident' And [workedby] = 'SLT'")
        Me.Inc_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Request' and [workedby] = 'SLT'")
        Me.Req_Text.Value = typeCount
        Me.Repaint
        typeCount = DCount("[type]", "Emp_Lst_Frm_Query", "[Type] = 'Change Order' and [workedby]= 'SLT'")
        Me.CO_Text.Value = typeCount
        Me.Repaint
    End Select
End Sub

I would like to thank everyone in advance because this site has been a great resource.

[NeoPa]

Check out Example Filtering on a Form then, if you still have problems with it, post an explanation of where you're struggling and we can help you.

[Michael Adams]

Thanks NeoPa, that was some good information, however, I am trying to filter based on a date range input by a "from" and a "to" date, an "employee name"(only three), and the "work order type". I can post the entire code here if you like, and I can try to post a picture of the form.

In my code(I am a very "NEW" person to this) I am trying to do a few different things. I will make those posts when I get to that point in the process. Currently I am trying to filter the data according to the requirements in the above paragraph.

Would you like to see the entire code? Also, again I am new to this posting stuff, what would i use for a URL if the image to the form is on my desktop?

[NeoPa]

Michael Adams: Thanks NeoPa, that was some good information, however, I am trying to filter based on a date range input by a "from" and a "to" date, an "employee name"(only three), and the "work order type". I can post the entire code here if you like, and I can try to post a picture of the form.

That sounds very much like what the article helps with Michael. The format for date ranges (any ranges actually) is to use the Between construct. Without going into exhaustive detail (as most of the rest of it is covered in the article) date criteria in SQL would look something like :

WHERE ((...)
  AND  ([DateField] Between #1/1/2010#
                        And #12/31/2010#)
  AND  (...))

As for seeing your code, I may ask for that at some point in the process, but for now we should be concentrating on the code that it should be like.

Pictures are rarely worth the effort. I have an understanding of what I need to know. If I didn't then I'd expect you to explain more clearly rather than drop a piccy in. There's only certain things where they can actually help in my view.

Michael Adams: In my code(I am a very "NEW" person to this) I am trying to do a few different things. I will make those posts when I get to that point in the process. Currently I am trying to filter the data according to the requirements in the above paragraph.

Sounds a good approach. Too many questions (>1) in a single thread can soon get everything very complicated. Feel free to post a link in here to any new threads you post that are related.

Michael Adams: Would you like to see the entire code? Also, again I am new to this posting stuff, what would i use for a URL if the image to the form is on my desktop?

No need for your code at this time.

There is almost no way (and certainly no safe way) to publish anything from your desktop to the web. This statement isn't absolute, but I think it's true enough from your perspective. Whenever you feel the need to share such a picture then use a third-party web to host it and post a link.

[Michael Adams]

I am now getting a data type mismatch in criteria expression in this line code.

Select Case Me.cmbEmp.Value
    Case "Michael J Adams"
        mycount = DCount("[type]", "Emp_Lst_Frm_query", "[employee] = 'Michael J Adams' And [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")
        Debug.Print [CUDate], [Employee], [Type], mycount,[fromdatecmb], [todatecmb];
        Me.TotalTxt.Value = mycount
        Me.Repaint

In the debug I am getting these data returns:
[CUDate] = 5/20/2010
[Employee] = "Michael J Adams"
[Type] = "Change Order"
mycount = 0
[fromdatecmb] = 12:00:00 AM
[todatecmb] = 12:00:00 AM

I believe that is where my mismatch is. the [fromdatecmb] and the [todatecmb] in the form is not pulling in the date

[Michael Adams]

I figured it out! YEAH.

Here is the final code I came up with:

Select Case Me.cmbEmp.Value
    Case "Michael J Adams"
        mycount = DCount("[type]", "Emp_Lst_Frm_query", "[employee] = 'Michael J Adams' And [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")
        Me.TotalTxt.Value = mycount
        Me.Repaint
        mycount = DCount("[Type]", "Emp_Lst_Frm_query", "[Type] = 'incident' and [employee] = 'Michael J Adams' and [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")
        Me.Inc_Text.Value = mycount
        Me.Repaint
        mycount = DCount("[Type]", "Emp_Lst_Frm_query", "[Type] = 'Request' and [employee] = 'Michael J Adams' and [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")
        Me.Req_Text.Value = mycount
        Me.Repaint
        mycount = DCount("[Type]", "Emp_Lst_Frm_query", "[Type] = 'Change Order' and [employee] = 'Michael J Adams' and [cudate] between '" & Me.fromdatecmb.Value & "' and '" & Me.todatecmb.Value & "'")
        Me.CO_Text.Value = mycount
        Me.Repaint

[NeoPa]

Good post! We are not going to be long sorting this out if you respond as clearly and intelligently every time.

I can see in your line #3 that you are formatting your dates wrongly (See Literal DateTimes and Their Delimiters (#)). The delimiters should be hashes (#) and the date strings themselves should be formatted as m/d/yyyy.

It also appears that no dates have been selected at run-time.

This code should work, but you'd need to ensure valid dates were entered in order to test it properly :

mycount = DCount("[type]", _
                 "Emp_Lst_Frm_query", _
                 "([employee]='Michael J Adams') And " & _
                 "([cudate] Between " & Format(Me.fromdatecmb,"\#m/d/yyyy\#") & _
                              " And " & Format(Me.todatecmb,"\#m/d/yyyy\#"))

PS. I see you've been busy while I've been working on this. Good for you.

[Michael Adams]

For the Date data I am using a calendar control which is activated by a mousedown event in each datecmb box. Will I still need to format the date in the way you suggested?

[NeoPa]

Absolutely. It could have all sorts of ways of displaying otherwise. In your earlier post they seemed to display as times only, which would presumably format your SQL string in the same way. Not much use to you.