Solved

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

Posted on 2013-02-06
10
1,913 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

735 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