Report Date Range showing all dates

[thunderbolt1164]

I have been trying to build a database for my business for the last 4 months off and on. I am down to doing up the reports and am stuck again.
I have set up DATE FORM and added a text box to the report. When I click on the Report it brings up the Date Form, but when i click OK it still lists all dates instead of the ones between.
I am a new member but have been using the sites Forum to help me so far and would like to thank you all for saving me from going mad.

 

[RuralGuy]

How are you getting the limiting dates to your report?

 

[thunderbolt1164]

By a Report Date Range form with [BeginDate] and [EndDate]

 

[RuralGuy]

But how is the report looking at your "Report Date Range form"?

 

[thunderbolt1164]

Sorry RG I don't understand what you are asking( i have been rebuilding computers for years,just recently got in to the database building,self taught) but hope this helps.

It is the guide line I used to build it.

1. A) Build your form with 2 unbound text boxes set to enter short date only
B) Attack a button called Perview.(On Click [Event Procedure])

Option Compare Database


Private Sub Form_Open(Cancel As Integer)
Me.Caption = Me.OpenArgs
End Sub
Private Sub Preview_Click()
If IsNull([BeginDate]) Or IsNull([EndDate]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "BeginDate"
Else
If [BeginDate] > [EndDate] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "BeginDate"
Else
Me.Visible = False
End If
End If
End Sub


2. A) Build you Text Box,(in header of report)
="Transcations Between: "&[Forms]![Report Date Range]!BeginDate &" and " &[Forms]![Report Date Range]!EndDate
B) Click Code

Option Compare Database


Private Sub Report_Close()
DoCmd.Close acForm, "Report Date Range"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Incoming Details"
If Not IsLoaded("Report Date Range") Then
Cancel = True
End If
End Sub

 

[RuralGuy]

That is actually backwards from the way it is usually done and will only supply dates for your report header but not the RecordSet. Are you trying to make a Generic Date From/To form? I would put a Print and Preview button on your DateForm and open your reports from there. Then you would call the DateForm first and if you have several reports then put a ComboBox on the DateForm to select the report you want. This allows you to use the WhereCondition and the OpenArgs arguments to tailor the report.

 

[thunderbolt1164]

Sorry so long in getting back but here is what I have done:

Made new Date Range Form

1- ComboBox which which gets the Name of the Report from a Tables List (Name of ComboBox is cboReportName)
2- Date Entry Boxes BeginDate and EndDate Set to short date format
1- Botton Command labled 'PreVeiw' attached to the [Event Procedure] is:

Private Sub Preview_Click()

Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = cboReportName
strField = "Date"

If IsNull(Me.BeginDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.EndDate, conDateFormat)
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.BeginDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.BeginDate, conDateFormat)

_
& " And " & Format(Me.EndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

But when I run it, it errors by giving the value of the combobox and not the report name.

the error I get is:

Run-Time error '2103':

The report name '1' you entered in either the property sheet or marco is misspelled or refers to a report that doesn't exist.

I know the '1' is the value of the report name in the combo box but don't know how it ifx it

Do i call up the name by having it go to tables in the strReport= ?

 

[thunderbolt1164]

Sorry the '_' under strWhere is not in [Event Procedure], must be a cut and paste mistake.

 

[RuralGuy]

Date is a reserved word in Access so please do not use it for a field name.
http://www.allenbrowne.com/AppIssueBadWord.html
And while we are at it how about posting the SQL for the RowSource of the cboReportName ComboBox?

 

[thunderbolt1164]

i Read about the date reserve while going though some of the other forms here and have changed all Date fields to MyDate

I think that this what you were asking or the SQL:

SELECT [Dateform].[DateFormID], [Dateform].[DateFormReportName] FROM Dateform;

 

[RuralGuy]

Your ComboBox should not have a ControlSource (unbound) so set the Bound Column to 1 on the Data tab. It is currently set to 0 right?

Edit: My Bad! Set the Bound Column to 2 for it is now set at 1, correct?

 

[thunderbolt1164]

Correct, set it to 2

 

[RuralGuy]

The form should now work properly. Give it a try.

 

[RuralGuy]

BTW, if you wrap your code posts in code tags (the # button) then it preserves the formatting and is a lot easier to read.

Private Sub Preview_Click()

   Dim strReport As String   'Name of report to open.
   Dim strField As String    'Name of your date field.
   Dim strWhere As String    'Where condition for OpenReport.
   Const conDateFormat = "\#mm\/dd\/yyyy\#"

   strReport = cboReportName
   strField = "[MyDate]"

   If IsNull(Me.BeginDate) Then
      If Not IsNull(Me.EndDate) Then   'End date, but no start.
         strWhere = strField & " <= " & Format(Me.EndDate, conDateFormat)
      End If
   Else
      If IsNull(Me.EndDate) Then   'Start date, but no End.
         strWhere = strField & " >= " & Format(Me.BeginDate, conDateFormat)
      Else   'Both start and end dates.
         strWhere = strField & " Between " & Format(Me.BeginDate, conDateFormat) & _
                    " AND " & Format(Me.EndDate, conDateFormat)
      End If
   End If

   ' Debug.Print strWhere 'For debugging purposes only.
   DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

 

[thunderbolt1164]

Now it is only picking up 2 out 6 ID's

 

[thunderbolt1164]

i am using a form with a subform linked by ID ...does that help?

It is picking up all items in ID#1 and ID#2 but that is all
though I put in dates that are past ID#2

 

[thunderbolt1164]

I think I may have found my problem

ID#1 [Incoming Style]=Drop-Off
ID#2 [Incoming Style]=Pick-Up
But
ID#3[Incoming Style]=Drop-Off again

So i am going to try to build a report and group Incoming styles.

If you are wondering i am trying to build a shipping and receiving database.

 

[RuralGuy]

It sounds like you are off and running. Good.

 

[thunderbolt1164]

it seems that the Date Range works if you put dates in that are larger then I want (from the biginning to the end), then it lists everything the way I think it shoud be.

but if I do a search for a single day, let's say 06/03/08 to 06/03/08 then all fields are blank and i get no error message.

 

[thunderbolt1164]

think I fix that problem seems to be working good

Now my partner has pointed out something strange.

When you scan through the forms the Autonumbered ID's are not in order

Example: Recordset #4(the last of the list) is actaully Incoming ID #2

Is there a way to Syncornize the Recordset and the Autonumbering of the form ID?