Line numbers that are days of the month

[Barry]

I am trying to mimic a paper report (expense report) where the "line numbers"
represent the day of the month. I currently have a single table to collect
the data which includes ExpenseID, ExpenseDate, Travel, Phone, Gas etc. This
has been a long standing report and I don't expect additional categories of
expense to be added. My problem lies in one; numbering the lines with the
number of the days of the month (I understand line numbering, just not when
it may change as to number of days of the month) and listing the expenses on
the same line as the correct number for the day. I have achieved some
success by having the line number extract the date part of the ExpenseDate
however, you must add a blank record so that no numbers are skipped. Any
help with this problem is greatly appreciated.

Barry

 

[AuldMannie]

1 Create table â€tblMonthDays†– 1 Column (other than Key) Integer DayOfMonth
– populate with 1-31
2 Query to Extract all Expenses for month in question – “qryMonthExpensesâ€
extra field(s)
DayNo - Day number of the expense
LastDay - Last Day Number for Month Year in question (May want to Save
this on a hidden field on Form Used to control Report Preparation)
3 Query for Report - "qryMExpRep"
Use "tblMonthDays" and "qryMonthExpenses"
Link DayOfMonth to DayNo - Link should be all Records from "tblMonthDays"
and only those Matching from "qryMonthExpenses"
Criteria for selection will be DayOfMonth
<=LastDay
Note a single Record for each day not in "qryMonthExpenses" with all
fields from
"qryMonthExpenses" in "qryMExpRep" as Nulls. Use something along the
lines -
Exp:Iff(isnull(Expenses),"None Recorded",Expenses)
to populate a readily printable against "missing days"
Expenses being e.g. the field name for the expense incurred.

File = Table
Field = Column
Record = Row
(If Used)

hope this is understandable.

Cheers
AuldMannie

 

[Barry]

Dear AuldMannie:

I believe that I understand. I've made a practice run at it and it seems to
be working very well. I did however change the text statement in the IIF to
placing 0 in the fields so that the totals at the bottom would work. I still
have a question on how to get the LastDay field. I saw a function written in
a microsoft knowledge base article but not sure if this would be the best
method and I'm not sure how to call it from the query. Thanks again.

Barry

 

[krissco]

Dear AuldMannie:

I believe that I understand. I've made a practice run at it and it seems to
be working very well. I did however change the text statement in the IIF to
placing 0 in the fields so that the totals at the bottom would work. I still
have a question on how to get the LastDay field. I saw a function written in
a microsoft knowledge base article but not sure if this would be the best
method and I'm not sure how to call it from the query. Thanks again.

Barry

I'd use a custom function. Place this code in a standard module and
include the column LastDayOfMonth(ExpenseDate) in your query.

Public Function LastDayOfMonth(dt As Date) As Integer
LastDayOfMonth = Day(DateAdd("d", -1, DateSerial(Year(dt), Month(dt) +
1, 1)))
End Function

There may be better methods of coming up w/ this figure - this is just
one I cooked up (at least it is compact!).

-Kris

 

[AuldMannie]

Convoluted but will Work.

Firstly I am assuming that you are using a form to get e.g. Month, Year for
the report via drop down lists (table Lookups best - according to my users).
That's how I would do it. Form Name say "Expenses Report". So for that -
Get a date 1st of Month Year entered.
Use DateAdd to add one month to the date you just got.
Next use DateAdd to add -1 (Minus one) day to the date.
Get the Day via the DatePart Function - this will be the last day of your
Month Year.
This why I said use a form - on that form you have an unbound Text field
called say LastDay
(I try to avoid spaces in a name). Last Day=Day you just got. Set it when
the User Clicks on a Print/Display Report button on your form in the Click
Event for the button.
(Can be used to Display Report on screen or Print immediately)
In Criteria in your Query for DayOfMonth have
<=[Forms]![Expenses Report].[LastDay]
the [] brackets are needed if you have any Spaces in a name but better
to get into habit of using them at all times.

OK?

Cheers
AuldMannie

 

[AuldMannie]

Hey Kris,

Thanks for that one - just looked up DateSerial - won't have to concatonate
strings and use CDate any more. That is a treat.

sincerely
Auld Mannie

 

[krissco]

Hey Kris,

Thanks for that one - just looked up DateSerial - won't have to concatonate
strings and use CDate any more. That is a treat.

sincerely
Auld Mannie

You are most welcome.

I can't tell you how many times I have found a function that already
existed and said "I wish I knew about that one {Insert # Here} months
ago!"

-Kris

 

[Barry]

Wow! Great stuff guys! I understand much better now and just have to decide
how I would like to implement a solution. The other answer I looked at was
from Microsoft KB (http://support.microsoft.com/kb/210448). This shows a
method to use the DateDiff expression in a query. Just a matter of logistics
at this point. Thanks to you both for all of your help as it has been
instrumental in my completing this project.

Barry

Convoluted but will Work.

Firstly I am assuming that you are using a form to get e.g. Month, Year for
the report via drop down lists (table Lookups best - according to my users).
That's how I would do it. Form Name say "Expenses Report". So for that -
Get a date 1st of Month Year entered.
Use DateAdd to add one month to the date you just got.
Next use DateAdd to add -1 (Minus one) day to the date.
Get the Day via the DatePart Function - this will be the last day of your
Month Year.
This why I said use a form - on that form you have an unbound Text field
called say LastDay
(I try to avoid spaces in a name). Last Day=Day you just got. Set it when
the User Clicks on a Print/Display Report button on your form in the Click
Event for the button.
(Can be used to Display Report on screen or Print immediately)
In Criteria in your Query for DayOfMonth have
<=[Forms]![Expenses Report].[LastDay]
the [] brackets are needed if you have any Spaces in a name but better
to get into habit of using them at all times.

OK?

Cheers
AuldMannie

Dear AuldMannie:

I believe that I understand. I've made a practice run at it and it seems to
be working very well. I did however change the text statement in the IIF to
placing 0 in the fields so that the totals at the bottom would work. I still
have a question on how to get the LastDay field. I saw a function written in
a microsoft knowledge base article but not sure if this would be the best
method and I'm not sure how to call it from the query. Thanks again.

Barry