Solved

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

Posted on 2013-02-06
10
1,962 Views
Last Modified: 2013-02-06
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
0
Comment
Question by:GeneBat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38860150
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 38860157
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38860159
With date fields, you have to wrap them with the # sign.

    CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship]  = #" & txtStartDate.Value & "#"
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:GeneBat
ID: 38860314
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 38860348
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38860359
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
 

Author Comment

by:GeneBat
ID: 38860588
Okay let me test this functionally...
0
 

Author Comment

by:GeneBat
ID: 38860687
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38860728
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
 

Author Closing Comment

by:GeneBat
ID: 38860773
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question