Solved

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

Posted on 2013-02-06
10
2,055 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 66

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 48

Expert Comment

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

    CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship]  = #" & txtStartDate.Value & "#"
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 51

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 48

Expert Comment

by:Dale Fye
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 48

Accepted Solution

by:
Dale Fye 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

623 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