Report Date Range showing all dates

[RuralGuy]

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

Tables are unordered rows of records that are returned in a manner that the Jet Engine feels is optomized. If you want order, then you need to use a query and specify an OrderBy sequence.

 

[RuralGuy]

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.

Here's a link that will help explain the DateTime field. http://support.microsoft.com/default.aspx/kb/q130514/ Since *all* DateTime fields have a time component, if you just set the Date part of the field then the Time part defaults to .0 or midnight. When you use the same date value in a BETWEEN statement then you are asking for records BETWEEN that day at midnight and that day at midnight. That probably means you will not find any unless they are dated at exactly midnight. Many developers just bump the EndDate by one to get everything on the EndDay.

 

[thunderbolt1164]

ok thanks for all your help

I now got the reports that the Book Keeper will need working

Now I need to know

Is there a way to add another search field to the Report Date Range to pull up just 1 supplier's information between the two dates entered...for billing purposes

The info i would need is

1 combobox to pull up Report Name (I think, but they will all use the same Report called Billing.)
1 ComboBox to pull up Supplier's Name
2 Date set Text boxes
1 Button

I can figure out the layout of the form but not the code.

 

[RuralGuy]

You just add it to your WhereCondition argument.
"(DateField Between #../../..# AND #../../..#) AND [SupplierID] = " Me.cboSupplier
Your cboSupplier should have at least two field in the RowSource: SupplierID and SupplierName
Hide the ID Column but use it for your report limiting. This time bind the cbo to the ID field so that is what is returned with Me.cboSupplier.

 

[thunderbolt1164]

ok have nad combo box called cboSupplier and bound column is set to 1.

But how do I insert:
"(DateField Between #../../..# AND #../../..#) AND [SupplierID] = " Me.cboSupplier

Into this:
#
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
#

Tried the Code # Key trick but don't think i did it right...sorry

 

[thunderbolt1164]

strField="Date" has Been changed to strField="TransactionDate"

 

[RuralGuy]

Try:

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 = "TransactionDate"
   [COLOR="Red"]strWhere = "[SupplierID] = " & Me.cboSupplier & " AND "[/COLOR]

   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

 

[RuralGuy]

BTW, if you press the "#" key, it will put {code}{/code} on your screen. The braces "}{" are actually brackets"][". That is what I meant by code tags. You can just type them in as well.

 

[thunderbolt1164]

RuralGuy:

Just thought i would let you know the database seems to be working...still finding little errors here and there but have been able to figure them out.

I would like to thank you for all the help you gave me, without your help I was just about to give up on it.

Once I know that it is working good, I may put it on one of those freeware sites for small bussinesses because i myself could not find a Freeware Shipping & Receiving program. That is why I needed this.

Again THANKS for all your help.

 

[RuralGuy]

Glad I could help. Thanks for posting back with your success.