Unable to filter DateSerial

  • Thread starter Thread starter Jonathan via AccessMonster.com
  • Start date Start date
J

Jonathan via AccessMonster.com

I have a form that filters between two dates and need it to work with a
report containing a text field that I have converted to a date. The text
field is from a linked table that could not be changed.

The specifics are below.

I have a form called UPSshippingEB with an unbound text box with the control
source set to
=DateSerial(Val(Left([CollectionDate],4)),Val(Mid([CollectionDate],5,2)),Val
(Mid([CollectionDate],7,2))).

I have a report called Shipments also with an unbound text box with the
control source set to the same as above. The SQL statement for the query is
below.

SELECT UPS_SHIPPING_EB.[Company or Name], UPS_SHIPPING_EB.Attention,
UPS_SHIPPING_EB.[Address 1], UPS_SHIPPING_EB.[Address 2], UPS_SHIPPING_EB.
[Address 3], UPS_SHIPPING_EB.[City or Town], UPS_SHIPPING_EB.
[State/Province/County], UPS_SHIPPING_EB.[Postal Code], UPS_SHIPPING_EB.
[Country/Territory], UPS_SHIPPING_EB.Telephone, UPS_SHIPPING_EB.[Tracking
Number], UPS_SHIPPING_EB.Weight, UPS_SHIPPING_EB.[Reference 1],
UPS_SHIPPING_EB.[Package Type], UPS_SHIPPING_EB.[Service Type],
UPS_SHIPPING_EB.CollectionDate, UPS_SHIPPING_EB.[Total Shipment/Handling
(Published Charges)], UPS_SHIPPING_EB.[Total Shipment/Handling (Negotiated
Rates Charge)]
FROM UPS_SHIPPING_EB
WHERE (((UPS_SHIPPING_EB.CollectionDate) Between [Forms]!
[Print_Shipments_by_Date]![Print_start_date] And [Forms]!
[Print_Shipments_by_Date]![Print_end_date]))
ORDER BY UPS_SHIPPING_EB.[Company or Name];

I have another form called PrintByDate that allows the user to enter two
dates to filter between. I use it with almost all my databases and it works
perfectly, however I can't get it to work with this one. The code is below.


Private Sub Print_OK_Click()

On Error GoTo Err_OK_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.Dirty = True Then Me.Dirty = False

If IsNull(Me.Print_start_date) Or Me.Print_start_date = "" Then
MsgBox "Please enter a Start Date.", vbOKOnly, "Required Data!"
Me.Print_start_date.SetFocus
End If

If IsNull(Me.Print_end_date) Or Me.Print_end_date = "" Then
MsgBox "Please enter an End Date.", vbOKOnly, "Required Data!"
Me.Print_end_date.SetFocus
End If

If Me.Print_start_date < #1/1/2008# Then
MsgBox "Please enter a valid Start Date.", vbOKOnly, "Invalid Entry!"
Me.Print_start_date.SetFocus
End If

If Me.Print_end_date < Me.Print_start_date Then
MsgBox "The End Date cannot be before the Start Date.", vbOKOnly,
"Invalid Entry!"
Me.Print_end_date.SetFocus
End If

If Me.Print_start_date >= #1/1/2008# And Me.Print_end_date >= Me.
Print_start_date Then

stDocName = "Print_Shipments_by_Date"
DoCmd.OpenReport stDocName, , , stLinkCriteria
DoCmd.Close acForm, "Print_Shipments_by_Date", acSaveNo
DoCmd.OpenForm "UPSshippingEB", acNormal, , , , , True

End If

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub

Private Sub Print_start_date_AfterUpdate()

If Me.Print_start_date < #1/1/2008# Then
MsgBox "Please enter a valid Start Date.", vbOKOnly, "Invalid Entry!"
Me.Print_start_date.SetFocus
End If

End Sub
 
It looks like you are attempting to filter based on a stored value in your
table like
"20080226"
with values from text boxes that might be date type with values like:
#2/1/2008# and #2/29/2008#

This would not work. Either change the text entered into the text boxes to
match the value stored in the table or convert the table value to a true date
in the criteria

DateSerial(Val(Left([CollectionDate],4)), Val(Mid([CollectionDate],5,2)), Val
(Mid([CollectionDate],7,2))) Between [Forms]!
[Print_Shipments_by_Date]![Print_start_date] And [Forms]!
[Print_Shipments_by_Date]![Print_end_date]))


--
Duane Hookom
Microsoft Access MVP


Jonathan via AccessMonster.com said:
I have a form that filters between two dates and need it to work with a
report containing a text field that I have converted to a date. The text
field is from a linked table that could not be changed.

The specifics are below.

I have a form called UPSshippingEB with an unbound text box with the control
source set to
=DateSerial(Val(Left([CollectionDate],4)),Val(Mid([CollectionDate],5,2)),Val
(Mid([CollectionDate],7,2))).

I have a report called Shipments also with an unbound text box with the
control source set to the same as above. The SQL statement for the query is
below.

SELECT UPS_SHIPPING_EB.[Company or Name], UPS_SHIPPING_EB.Attention,
UPS_SHIPPING_EB.[Address 1], UPS_SHIPPING_EB.[Address 2], UPS_SHIPPING_EB.
[Address 3], UPS_SHIPPING_EB.[City or Town], UPS_SHIPPING_EB.
[State/Province/County], UPS_SHIPPING_EB.[Postal Code], UPS_SHIPPING_EB.
[Country/Territory], UPS_SHIPPING_EB.Telephone, UPS_SHIPPING_EB.[Tracking
Number], UPS_SHIPPING_EB.Weight, UPS_SHIPPING_EB.[Reference 1],
UPS_SHIPPING_EB.[Package Type], UPS_SHIPPING_EB.[Service Type],
UPS_SHIPPING_EB.CollectionDate, UPS_SHIPPING_EB.[Total Shipment/Handling
(Published Charges)], UPS_SHIPPING_EB.[Total Shipment/Handling (Negotiated
Rates Charge)]
FROM UPS_SHIPPING_EB
WHERE (((UPS_SHIPPING_EB.CollectionDate) Between [Forms]!
[Print_Shipments_by_Date]![Print_start_date] And [Forms]!
[Print_Shipments_by_Date]![Print_end_date]))
ORDER BY UPS_SHIPPING_EB.[Company or Name];

I have another form called PrintByDate that allows the user to enter two
dates to filter between. I use it with almost all my databases and it works
perfectly, however I can't get it to work with this one. The code is below.


Private Sub Print_OK_Click()

On Error GoTo Err_OK_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.Dirty = True Then Me.Dirty = False

If IsNull(Me.Print_start_date) Or Me.Print_start_date = "" Then
MsgBox "Please enter a Start Date.", vbOKOnly, "Required Data!"
Me.Print_start_date.SetFocus
End If

If IsNull(Me.Print_end_date) Or Me.Print_end_date = "" Then
MsgBox "Please enter an End Date.", vbOKOnly, "Required Data!"
Me.Print_end_date.SetFocus
End If

If Me.Print_start_date < #1/1/2008# Then
MsgBox "Please enter a valid Start Date.", vbOKOnly, "Invalid Entry!"
Me.Print_start_date.SetFocus
End If

If Me.Print_end_date < Me.Print_start_date Then
MsgBox "The End Date cannot be before the Start Date.", vbOKOnly,
"Invalid Entry!"
Me.Print_end_date.SetFocus
End If

If Me.Print_start_date >= #1/1/2008# And Me.Print_end_date >= Me.
Print_start_date Then

stDocName = "Print_Shipments_by_Date"
DoCmd.OpenReport stDocName, , , stLinkCriteria
DoCmd.Close acForm, "Print_Shipments_by_Date", acSaveNo
DoCmd.OpenForm "UPSshippingEB", acNormal, , , , , True

End If

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click

End Sub

Private Sub Print_start_date_AfterUpdate()

If Me.Print_start_date < #1/1/2008# Then
MsgBox "Please enter a valid Start Date.", vbOKOnly, "Invalid Entry!"
Me.Print_start_date.SetFocus
End If

End Sub

-----
So I need help figuring out where I went wrong here and how I can filter the
CollectionDate text field (after it is converted to a date) between the two
dates entered on the PrintByDate form. I think my mistakes are in the SQL
statement and report, not the PrintByDate form. Any help is much appreciated!
Click to expand...
 
Back
Top