Working with dates in vba...

TomJamieson

Registered User.
Local time
Today, 16:29
Joined
Jun 8, 2006
Messages
50
Hi.

I hope somebody can help me with this... I'm not a VBA expert, just self taught in things I need to know.

I have a form with several combo boxes, and a subform below. Every combo box represents a field on my main table, allowing people to search by chosen criteria. I've written code that goes through the comboboxes and adds the details in any used ones into a filter, which is applied to a table. All results then appear in the subform.

Unfortunately, I've gotten really stuck on the date field. Ideally, I want them to be able to choose two dates, so they can search all jobs that are due out between those two dates. But I don't know how to work with dates in VBA at all. I tried > and < but it didn't work. Can anybody help?

This is my code so far:

Private Sub btnSearch_Click()
Dim strFilter As String
Dim varItem As Variant

strFilter = ""

If Not IsNull(Me!cboOpen) Then strFilter = strFilter & "[Job Open]=" & Chr(34) & Me!cboOpen & Chr(34) & " AND "
If Not IsNull(Me!cboStatus) Then strFilter = strFilter & "[QC Status]=" & Chr(34) & Me!cboStatus & Chr(34) & " AND "
If Not IsNull(Me!cboRef) Then strFilter = strFilter & "[Customer]=" & Chr(34) & Me!cboRef & Chr(34) & " AND "


If Not IsNull(Me!txtDate) Then strFilter = strFilter & "[Out date]>" & Me!txtDate & " AND "

If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)

If strFilter = "" Then
Me!subJobs.Form.FilterOn = False
Else
Me!subJobs.Form.FilterOn = True
Me!subJobs.Form.Filter = strFilter
End If
End Sub
 
Forgive my noobness, but can you point out where I need the #s? Or any other mistakes. Am trying to do the same thing the OP is doing, searching a report by date range.



Code:
Private Sub cmdApply_Filter_Click()
Dim strDate As String
Dim strFilter As String


'Date Boxes

If IsNull(Me!txtFromDate.Value) Then
    strDate = "Like '*'"
    Else: strDate = "Between " & Me!txtFromDate.Value & " AND " & Me!txtToDate.Value
End If

strFilter = "[Date_Opened] " & strDate

'Turn on Filter

With Reports![rptClientsOpen]
    .Filter = strFilter
    .FilterOn = True
End With




End Sub
 
"Between #" & Me!txtFromDate.Value & "# AND #" & Me!txtToDate.Value &"#"
End If

strFilter = "[Date_Opened] = #" & strDate & "#"
 
Code:
Else: strDate = "Between #" & Me!txtFromDate & "# AND #" & Me!txtToDate & "#"
Note: The .Value is the default property of a control and need not be specified.
 
You guys are a lifesaver! Thanks for the help, getting an error though:

Syntax Error (missing operator) in query expression '([Date_Opened] = #Between #06/25/2006# AND #07/01/2006##)

Here is code with additions:

Code:
Private Sub cmdApply_Filter_Click()
Dim strDate As String
Dim strFilter As String


'Date Boxes

If IsNull(Me!txtFromDate.Value) Then
    strDate = "Like '*'"
    Else: strDate = "Between #" & Me!txtFromDate.Value & "# AND #" & Me!txtToDate.Value & "#"
End If

strFilter = "[Date_Opened] = #" & strDate & "#"

'Turn on Filter

With Reports![rptClientsOpen]
    .Filter = strFilter
    .FilterOn = True
End With




End Sub
 
Last edited:
Replace strFilter = "[Date_Opened] = #" & strDate & "#"
with strFilter = "[Date_Opened] = " & strDate
 
Yep, I tried that solution but heres what I got:

Syntax Error (missing operator) in query expression '([Date_Opened] = Between #06/25/2006# AND #07/01/2006#)

It took the #s off the ends, but Im still getting a similar error. I can't quite figure whats going on here.
 
Remove the 2nd "=" sign, sorry. strFilter = "[Date_Opened] " & strDate
 
Hey, no need to apologize, I'd be lost without this forum:)

I hate to continue the thread, but I'm still getting zero records back when filtering with this code. I have no idea why this would be, as the code looks fine to me. However, this is my first time filtering by date so maybe I've got something wrong that I'm not seeing.

Any hints?
 
Are you trying to modify the results of an existing open report? You need to run the report again. How are you opening the report? Put your filter criteria in the WhereConfition of the OpenReport command and it will filter the report.
 
Like this?

Code:
DoCmd.OpenReport "rptClientsOpen", acViewPreview, , "Reports!rptClientsOpen!Date_Opened Between #' & Me!txtFromDate & '# AND #' & Me!txtToDate & '#'"
 
OxDavis said:
Just for future readers/searchers, I found this, works like a charm:

Linkage

Good find.

Still not working for me though :(

The code runs with no errors, but the results in the subform never work correctly. The subform is pulling from a table, is it possible there is some problem with the date setting on the table not corresponding to the date setting on the form? They are both set to short date format, dd/mm/yyyy.
It's very annoying. If I enter a date in the from date, it gives me all records with a date. If I enter it one in the to date or both boxes, it gives me no records at all.

Anyone got any ideas why?
 
Had this problem as well. Took me about 30 min and a lot of hair pulling to figure out. Check and make sure that the fields you are referencing in the table are set to "Date/Time" format in Table Design view. I thought just having the input mask would be enough. Mine were set to "Text" and a quick swap of the data type made the whole thing work no problems. Let me know if it works

-Ox
 
OxDavis said:
Had this problem as well. Took me about 30 min and a lot of hair pulling to figure out. Check and make sure that the fields you are referencing in the table are set to "Date/Time" format in Table Design view. I thought just having the input mask would be enough. Mine were set to "Text" and a quick swap of the data type made the whole thing work no problems. Let me know if it works

-Ox

Unfortunately the field in the table is already set to date/time format, which means it's something else. Damn.
 
Please re-post the code as it stands now.
 
Code:
Private Sub btnSearch_Click()
Dim strFilter As String
Dim varItem As Variant

strFilter = ""

If Not IsNull(Me!cboOpen) Then strFilter = strFilter & "[Job Open]=" & Chr(34) & Me!cboOpen & Chr(34) & " AND "
If Not IsNull(Me!cboStatus) Then strFilter = strFilter & "[QC Status]=" & Chr(34) & Me!cboStatus & Chr(34) & " AND "
If Not IsNull(Me!cboRef) Then strFilter = strFilter & "[Customer]=" & Chr(34) & Me!cboRef & Chr(34) & " AND "

'***It is the following section that doesn't work. It doesn't error, it either gives me no records or all the records in the results

If IsNull(Me.txtFromDate) Then
        If Not IsNull(Me.txtToDate) Then   'End date, but no start.
            strFilter = strFilter & "[Out Date]" & " <= " & Format(Me.txtToDate, conDateFormat)
        End If
    Else
        If IsNull(Me.txtToDate) Then       'Start date, but no End.
            strFilter = strFilter & "[Out Date]" & " >= " & Format(Me.txtFromDate, conDateFormat)
        Else                                'Both start and end dates.
            strFilter = "[Out Date]" & " Between " & Format(Me.txtFromDate, conDateFormat) _
                & " And " & Format(Me.txtToDate, conDateFormat)
        End If
    End If

'***end of code that doesn't work

If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)

If strFilter = "" Then
   Me!subJobs.Form.FilterOn = False
Else
   Me!subJobs.Form.FilterOn = True
   Me!subJobs.Form.Filter = strFilter
End If
End Sub
 

Users who are viewing this thread

Back
Top Bottom