Print records within and including range of dates

  • Thread starter Thread starter DJ Notion via AccessMonster.com
  • Start date Start date
D

DJ Notion via AccessMonster.com

This question involves the following:

Tables: Print_Shipments_by_Date, UPS_SHIPPING_EB (linked)
Forms: Print_Shipments_by_Date, UPSshippingEB
Reports: Print_Shipments_by_Date

The linked table has a field called CollectionDate that is 14 digits
yyyymmddhhmmss. On the UPSshippingEB form I have an unbound text box with
the following as the control source: =Mid([CollectionDate],5,4) & "2008".
This makes the CollectionDate look like 00/00/0000. On that same form I have
a command button to Print Shipments by Date. When clicking, that opens up
the Print_Shipments_by_Date form where there are two fields, one to input a
start date and a second to input an end date. I want to be able to enter any
start and end date and print a report for the shipments with a collection
date within and including the date range entered on the form.

Before I show my code below, I have to point out that I use this very same
form with another database and it works beautifully. However, it is working
with an unbound text box that has =Date() in it.

I haven't added the code to check the collection date. That is what I need
help with. Any help is appreciated! Thanks.

Private Sub Print_OK_Click()

On Error GoTo Err_OK_Click

Dim stDocName As String
Dim stLinkCriteria As String

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


-Jonathan
 
I think the Mid function on CollectionDate may be a problem. I'm afraid that
it may turn your data into a string even though it looks like a date.
Is CollectionDate actually a Text Field or is it a Date/Time field?
The usual way to make a Date/Time field look like a date is simply to format
it or, if you want to adapt the date, (and if CollectionDate is a Date
field)

=DateSerial(2008,Month(CollectionDate),Day(CollectionDate)

I've made a form that filters between 2 dates. It has 2 text boxes
(txtFromDate & txtToDate) and a command button. The field it filters is a
date field called WkDate
The code I use looks like this:
Dim stDocName As String
Dim MyFrom As Long
Dim MyTo As Long
Dim Crit As String

stDocName = "RptFullDetails"
'name of my report
If IsNull(Me.txtFromDate) Then
'open report unfiltered
DoCmd.OpenReport stDocName, acPreview
Exit Sub
End If
If IsNull(Me.txtToDate) Then
'open report filtered from the date in txtFromDate
MyFrom = Format(Me.txtFromDate, "0")
Crit = "[WkDate]>=" & MyFrom
DoCmd.OpenReport stDocName, acPreview, , Crit

Else
'there is data in both boxes
MyFrom = Format(Me.txtFromDate, "0")
MyTo = Format(Me.txtToDate, "0")
Crit = "[WkDate]Between " & MyFrom & " AND " & MyTo
DoCmd.OpenReport stDocName, acPreview, , Crit
End If
Evi

DJ Notion via AccessMonster.com said:
This question involves the following:

Tables: Print_Shipments_by_Date, UPS_SHIPPING_EB (linked)
Forms: Print_Shipments_by_Date, UPSshippingEB
Reports: Print_Shipments_by_Date

The linked table has a field called CollectionDate that is 14 digits
yyyymmddhhmmss. On the UPSshippingEB form I have an unbound text box with
the following as the control source: =Mid([CollectionDate],5,4) & "2008".
This makes the CollectionDate look like 00/00/0000. On that same form I have
a command button to Print Shipments by Date. When clicking, that opens up
the Print_Shipments_by_Date form where there are two fields, one to input a
start date and a second to input an end date. I want to be able to enter any
start and end date and print a report for the shipments with a collection
date within and including the date range entered on the form.

Before I show my code below, I have to point out that I use this very same
form with another database and it works beautifully. However, it is working
with an unbound text box that has =Date() in it.

I haven't added the code to check the collection date. That is what I need
help with. Any help is appreciated! Thanks.

Private Sub Print_OK_Click()

On Error GoTo Err_OK_Click

Dim stDocName As String
Dim stLinkCriteria As String

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


-Jonathan
Click to expand...
 
CollectionDate is in a linked table in the following order: year month day
hours minutes seconds so yyyymmddhhmmss. It isn't a standard Date/Time field
as the year is first and it is a text field in the linked table. There is no
way to change the linked table to make it a Date/Time field and then adapt it.
Adapting it would create the same problems I am having right now with using
the Mid function. I just need to know how to reference CollectionDate in my
code. Every way I have tried hasn't worked and results in a blank record
being printed. How would you reference it? As I mentioned earlier, I don't
feel I need to change anything in my code, just add to it. Any further help
is appreciated! Thanks.

I think the Mid function on CollectionDate may be a problem. I'm afraid that
it may turn your data into a string even though it looks like a date.
Is CollectionDate actually a Text Field or is it a Date/Time field?
The usual way to make a Date/Time field look like a date is simply to format
it or, if you want to adapt the date, (and if CollectionDate is a Date
field)

=DateSerial(2008,Month(CollectionDate),Day(CollectionDate)

I've made a form that filters between 2 dates. It has 2 text boxes
(txtFromDate & txtToDate) and a command button. The field it filters is a
date field called WkDate
The code I use looks like this:
Dim stDocName As String
Dim MyFrom As Long
Dim MyTo As Long
Dim Crit As String

stDocName = "RptFullDetails"
'name of my report
If IsNull(Me.txtFromDate) Then
'open report unfiltered
DoCmd.OpenReport stDocName, acPreview
Exit Sub
End If
If IsNull(Me.txtToDate) Then
'open report filtered from the date in txtFromDate
MyFrom = Format(Me.txtFromDate, "0")
Crit = "[WkDate]>=" & MyFrom
DoCmd.OpenReport stDocName, acPreview, , Crit

Else
'there is data in both boxes
MyFrom = Format(Me.txtFromDate, "0")
MyTo = Format(Me.txtToDate, "0")
Crit = "[WkDate]Between " & MyFrom & " AND " & MyTo
DoCmd.OpenReport stDocName, acPreview, , Crit
End If
Evi
This question involves the following:
Click to expand...
[quoted text clipped - 80 lines]
Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
What about adding a field in your query to turn your string into a proper
Date/Time using a combination of DateSerial, CLng (to convert strings to
numbers) and DateValue?


Evi

DJ Notion via AccessMonster.com said:
CollectionDate is in a linked table in the following order: year month day
hours minutes seconds so yyyymmddhhmmss. It isn't a standard Date/Time field
as the year is first and it is a text field in the linked table. There is no
way to change the linked table to make it a Date/Time field and then adapt it.
Adapting it would create the same problems I am having right now with using
the Mid function. I just need to know how to reference CollectionDate in my
code. Every way I have tried hasn't worked and results in a blank record
being printed. How would you reference it? As I mentioned earlier, I don't
feel I need to change anything in my code, just add to it. Any further help
is appreciated! Thanks.

I think the Mid function on CollectionDate may be a problem. I'm afraid that
it may turn your data into a string even though it looks like a date.
Is CollectionDate actually a Text Field or is it a Date/Time field?
The usual way to make a Date/Time field look like a date is simply to format
it or, if you want to adapt the date, (and if CollectionDate is a Date
field)

=DateSerial(2008,Month(CollectionDate),Day(CollectionDate)

I've made a form that filters between 2 dates. It has 2 text boxes
(txtFromDate & txtToDate) and a command button. The field it filters is a
date field called WkDate
The code I use looks like this:
Dim stDocName As String
Dim MyFrom As Long
Dim MyTo As Long
Dim Crit As String

stDocName = "RptFullDetails"
'name of my report
If IsNull(Me.txtFromDate) Then
'open report unfiltered
DoCmd.OpenReport stDocName, acPreview
Exit Sub
End If
If IsNull(Me.txtToDate) Then
'open report filtered from the date in txtFromDate
MyFrom = Format(Me.txtFromDate, "0")
Crit = "[WkDate]>=" & MyFrom
DoCmd.OpenReport stDocName, acPreview, , Crit

Else
'there is data in both boxes
MyFrom = Format(Me.txtFromDate, "0")
MyTo = Format(Me.txtToDate, "0")
Crit = "[WkDate]Between " & MyFrom & " AND " & MyTo
DoCmd.OpenReport stDocName, acPreview, , Crit
End If
Evi
This question involves the following:
Click to expand...
[quoted text clipped - 80 lines]
Message posted via AccessMonster.com
Click to expand...
Click to expand...
Click to expand...
 
I get Data type mismatch in criteria expression when I use DateSerial after
converting it from a string to numbers. Any help with the actual SELECT
statement you are suggesting would be appreciated. Thanks.

What about adding a field in your query to turn your string into a proper
Date/Time using a combination of DateSerial, CLng (to convert strings to
numbers) and DateValue?

Evi
CollectionDate is in a linked table in the following order: year month day
hours minutes seconds so yyyymmddhhmmss. It isn't a standard Date/Time field
Click to expand...
[quoted text clipped - 57 lines]
Click to expand...
 
Does anyone have any suggestions on how I can get this to work? Any help is
appreciated. Thanks.

DJ said:
I get Data type mismatch in criteria expression when I use DateSerial after
converting it from a string to numbers. Any help with the actual SELECT
statement you are suggesting would be appreciated. Thanks.
What about adding a field in your query to turn your string into a proper
Date/Time using a combination of DateSerial, CLng (to convert strings to
Click to expand...
[quoted text clipped - 7 lines]
Click to expand...
 
Back
Top