Dates in a report 1

[hollyann]

I need to create a report for our finance office and they need the data in a specific format. The report is done biweekly for services provided and the total cost. The catch is that for a two week period a client could have multiple dates of service and they want the data in this format:
11/2 - 16

The 11/2 is November 2nd (the first date of service) and the 16th is the last date). Many services can occur between those dates but they want the first and last date in that format. All clients would have different dates of service and need to be on the same report.
Mary 11/4 - 19 $600.00
Ellen 11/3 - 18 $400.00

Any assistance you can provide would be greatly appreciated.

 

[Muttley]

Assuming you have two fields in your database, first date and last date, use the Day and Month functions to generate a new text field as follows:

Day([mytable]![First Date]) & "/" & Month([mytable]![First Date]) & " - " & Day([tblDatePeriod]![Last Date])

This would give you the format as you suggest but gets a little confusing where the period goes over into the next month. If so, you can amend the code so that the Month of the [Last Date] field is incorporated.

Hope this helps

Muttley

 

[hollyann]

No the field is just one- service date. After a caseworker works with the clients it is entered in the database for billing purposes. This is a new report they have requested-
So all they have needed to enter was Client #- Date of Service-Hours- and Travel

 

[Muttley]

OK, here's one you could try. Create a form with textbox controls of first and last dates (maybe default last date to a 2 week period in advance of first date?). Then use these controls as input to a query on the date field of your table. This will ouput all of the jobs within a given (two week) period.

The use the Min and Max functions to evaluate the highest and lowest values in the date range (and hence the earliest and latest jobs in that date range). Using similar syntax as my earlier response, you should be able to generate in the correct format - something like:

=Day(Min([myQuery]![myDate])) & "/" & Month(Min([myQuery]![myDate])) & " - " & Day(Max([myQuery]![myDate]))

Again, hope this helps

Muttley

 

[hollyann]

Thank you- I will try that