GeneBat
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
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
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:
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.
'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) & ";"
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 & "#"
CUSTOM_FILTER = CUSTOM_FILTER & " AND [Date Ship] = #" & txtStartDate.Value & "#"
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
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
[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.
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.
ASKER
Okay let me test this functionally...
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
'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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Thanks so much.
GeneBat
CUSTOM_FILTER = "[Date Ship] = #" & txtStartDate.Value & "#"