Date Format to Filter on a Date in VBA Code (Access 2003)

Hello Experts -

I have vba code that works on textbox's and combo's that looks like this:

If IsNull(cboPartNme) Or Me.cboPartNme= " " Then
 CUSTOM_FILTER = CUSTOM_FILTER
Else
 If CUSTOM_FILTER = "" Then
    CUSTOM_FILTER = "[PartNme]  = """ & cboPartNme.Value & """"
 Else
    CUSTOM_FILTER = CUSTOM_FILTER & " AND [PartNme]  = """ & cboPartNme.Value & """"
     
  End If
 End If

But if I use it for a date field data type Date/Time it doesn't work?

If IsNull(txtStartDate) Or Me.txtStartDate = " " Then
 CUSTOM_FILTER = CUSTOM_FILTER
Else
 If CUSTOM_FILTER = "" Then
    CUSTOM_FILTER = "[Date Ship]  = """ & txtStartDate.Value & """"
 Else
    CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship]  = """ & txtStartDate.Value & """"
   
   
 End If
End If


What's going on here? Is my sytanx wrong or something I can't get this to work.

Thanks in advance,
GeneBat
GeneBatAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dale FyeConnect With a Mentor Commented:
No, that is not what I'm getting at.  It should look something like:
'8th filter
If IsNull(txtStartDate) Or Me.txtStartDate = " " Then
 CUSTOM_FILTER = CUSTOM_FILTER
Else
  If CUSTOM_FILTER = "" Then
    CUSTOM_FILTER = "[Date Ship]  >= #" & txtStartDate.Value & "#"
 Else
    CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship]  > = #" & txtStartDate.Value & "#"    
 End If
   
End If

'9th filter
If IsNull(txtEndDate) Or Me.txtEndDate = " " Then
 CUSTOM_FILTER = CUSTOM_FILTER
Else
  If CUSTOM_FILTER = "" Then
    CUSTOM_FILTER = "[Date Ship] <= #" & txtEndDate.Value & "#"
 Else
    CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship]  <= #" & txtEndDate.Value & "#"    
 End If

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, dates in Access VBA are delineated by pound signs # instead of the string double-quote ", as in ...

CUSTOM_FILTER = "[Date Ship]  = #" & txtStartDate.Value & "#"
0
 
Helen FeddemaCommented:
I think you need to check whether there is a valid date value in the control, and save it to a Date variable for use in the filter.  Here is some typical code:

   'Date range filter from controls
   If IsDate(Me![txtFromDate].Value) = True Then
      dteFromDate = CDate(Me![txtFromDate].Value)
   End If

   If IsDate(Me![txtToDate].Value) = True Then
      dteToDate = CDate(Me![txtToDate].Value)
   End If

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

Open in new window


The above code creates a date range filter, but you can use just a single date for a filter.  Access has gotten much stricter about exact data types in recent versions, which is why I explicitly convert the date value in the control using CDate.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Dale FyeCommented:
With date fields, you have to wrap them with the # sign.

    CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship]  = #" & txtStartDate.Value & "#"
0
 
GeneBatAuthor Commented:
Helen_Feddema I see what your doing but i have to mantain the code that I have for txtStartDate and txtEndDate.

I closer using Fyed code snippet but I'm getting an error.

see attachment.

Thanks,
GeneBat
build-filter-match-code-snippet.doc
0
 
Gustav BrockCIOCommented:
It should read:

[Date Ship]  = #9/3/2012# AND [Date Ship]  = #10/31/2012#

not:

[Date Ship]  = "9/3/2012" AND [Date Ship]  = #10/31/2012#

/gustav
0
 
Dale FyeCommented:
Two issues.

1.  You have to use that same syntax with regard to all of your date fields.

 If CUSTOM_FILTER = "" Then
    CUSTOM_FILTER = "[Date Ship]  = #" & txtStartDate.Value & "#"
 Else
    CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship]  = #" & txtStartDate.Value & "#"
   
 End If

2.  You cannot have the [Date Ship] equal to two different dates, both the Start and End dates.  You either need to change the equal signs to >= and <= or something along those lines.
0
 
GeneBatAuthor Commented:
Okay let me test this functionally...
0
 
GeneBatAuthor Commented:
I don't get what your driving at...should my code look like this?
'8th filter
If IsNull(txtStartDate) Or Me.txtStartDate = " " Then
 CUSTOM_FILTER = CUSTOM_FILTER
Else
  If CUSTOM_FILTER = "" Then
    CUSTOM_FILTER = "[Date Ship]  = #" & txtStartDate.Value & "#"
 Else
    CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship]  > = and = < #" & txtStartDate.Value & "#"    
 End If
   
End If

'9th filter
If IsNull(txtEndDate) Or Me.txtEndDate = " " Then
 CUSTOM_FILTER = CUSTOM_FILTER
Else
  If CUSTOM_FILTER = "" Then
    CUSTOM_FILTER = "[Date Ship] = #" & txtEndDate.Value & "#"
 Else
    CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship]  > = and = < #" & txtEndDate.Value & "#"    
 End If
   
End If
0
 
GeneBatAuthor Commented:
I see what you mean now; like a BETWEEN in a query but in VBA code. Nice, this works as expected.

Thanks so much.
GeneBat
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.