Displaying Current Month

[tier259]

I work as a manager for a major retailer and I am trying to track my Cash Register Closeout shortages by register. I have the entire database working fine but I need to beable to run reports that will query only the current month or specify a month that I want it to run. I am hoping for a simple expression that I can put the criteria line in the query.

 

[THWatson]

tier259
Here's a way that gives you the most flexibility.

1. Create an unbound form. Let's call it frmSelectDates.
2. On the form create two unbound text boxes. Let's call them txtStartDate and txtEndDate.
3. On the form, also create a command button to run the report.
4. In the query, in the date field, enter the following criteria.

Between Forms!frmSelectDates!txtStartDate And Forms!frmSelectDates!txtEndDate

This method will allow you to run a report for any given period of time.

Tom

 

[ZmrAbdulla]

See the Northwind sample too

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!

 

[tier259]

Tom Thanks for the information. I was wondering if there was anyway that the form or query would automatically set to the current month with the start date being the first of the month and the end date being the current day of the month and the years changing automatically. I have tried to set default values in the properties field of the forms and the form still promps for a start and end date. Thanks.

Mike

 

[THWatson]

Mike
Sure. In the form...

Set the control source for txtStartDate to
=DateSerial(Year(Date()),Month(Date()),1)

Set the control source for txtEndDate to
=Date()

You can then use the Format property, just below the control source, to display the date in whichever format you desire. The default will be Short Date (11/1/2005). If you want to show November 1, 2005, then set the Format to
mmmm dd", "yyyy

Tom

 

[AmigoMobility]

Hey Mike,

I do something similar to what your asking for except I have the current month autofill for the whole month and not just to the current day. The following is code I use behind a cmdButton allowing the user to fill the current month. To fill to the current date just change txtEnd to:
txtEnd = Date()

Dim intMonth As Integer
Dim intYear As Integer
Dim intDays As Integer
Dim dteInput As Date
Dim varStart As Variant
Dim varEnd As Variant

intMonth = Month(Date)
intYear = Year(Date)

If dteInput = 0 Then
dteInput = Date
End If

intDays = Day(DateSerial(Year(dteInput), Month(dteInput) + 1, 0))
varStart = intMonth & "/1/" & intYear
varEnd = intMonth & "/" & intDays & "/" & intYear

Me.txtStart = varStart
Me.txtEnd = varEnd

HTH,
Shane

P.S. There may be an easier way but this is how I get there from here.

 

[tier259]

Hmmmm...When I attempt to open the report through the frmSelectDates form, the report is blank. I have double checked every label that they are correct, i've set all of the formats correctly and the report will only work when I manually type in the dates when attempting to open the report directly. Any thoughts. Thanks for all your help.

Mike

 

[THWatson]

Mike
Hmmm... I'm not sure what to say. What I suggested works fine at this end on a small test that I constructed.

One thing I did think of...rather than setting the control source of the txtStartDate and txtEndDate controls to the date values I suggested, you could leave the control sources blank and put the date values in the Default Value boxes. That works too, and also makes it possible to change the dates too.

Also, if you still can't get it to work, behind the command button that runs the report, you could add...
Me.txtStartDate.Requery
Me.txtEndDate.Requery

Let me know.

Tom