Link to home
Start Free TrialLog in
Avatar of GeneBat
GeneBatFlag for United States of America

asked on

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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 & "#"
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.
With date fields, you have to wrap them with the # sign.

    CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship]  = #" & txtStartDate.Value & "#"
Avatar of GeneBat

ASKER

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
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
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.
Avatar of GeneBat

ASKER

Okay let me test this functionally...
Avatar of GeneBat

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GeneBat

ASKER

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