More line number questions

[Barry]

As in the previous post, I have constructed the queries, referenced the
LastDayInMonth field however I am still struggling with how to report only
the records for the FirstDayMonth entered. If I use any criteria in the
query on the actual ExpenseDate either matching the month or using between
two dates, the query only returns the actual number of records and none of
the records where the dates are empty. So if there are 10 actual days with
data then only those 10 show regardless of the number of days in the month.
I hope that I am stating the problem clearly. The only solution I have come
up with is to keep only one month of data in the table at a time and
"archive" the other data to be retreived later if necessary. Then a solution
for copying records around and temp tables and all that will be necessary.
Any ideas?
Thanks again,

Barry

 

[AuldMannie]

Did you remember to change the link to select all 31 days in the month?
(tblMonthDays)
The link is usually to select only the records that the link matches in both
files.
Suggest you don't have only one month in the table - what if the
user/customer wants a report
for the quarter, or quarterly for last year etc., etc.

Cheers
AuldMannie

 

[Barry]

Dear AuldMannie:
Yes indeed I did change the link in the query to a Left Join. The problem
is that there's no way to select and match a start and/or end date to a field
in the query and maintain all the records regardless of the join type. Some
of the records are, if you'll pardon the expression, fake. They have no
ExpenseDate so, when you try to match or extract a particular month, quarter
or whatever, those "fake" records get left in the dust. What I have done at
this time is to copy the structure of the tblExpense to tblReport, run 2
queries, one delete (to empty tblReport) and the other to append records that
match the date criteria from a selection form. Then I use the original
queries with the tblReport and all is fine. If the data is from Feb. 2007 I
get 28 lines. If the data is from May 2007 I get 31 lines etc. I also used
conditional formatting in the report so if the field contains a valu greater
than 0 the number are red. Just a little easier on the eyes. Please let me
know if you have any other suggestions. This has been great fun and a
tremendous learning experience.
Thanks,
Barry

 

[AuldMannie]

Dear Barry

Just curiosity (it killed the cat), but why the "fake" records? Do you need
them for something?
However information "underload" means I canny really help much more.
I'm enjoying myself too.

Cheers
AuldMannie

 

[Barry]

AuldMannie:
When we linked the original 2 queries and added all the records from
tblMonthDays with records 1 through 31 and linked that to the tblExpenses we
got 31 records. Not all of the records are necessarily associated with an
ExpenseDate. Those records I am calling fake. They are there as filler so
that the report will have 31 (or as many days are in the month) lines to
mimic the old paper report, which was the original intent. Funny how you can
open a can of worms with what would appear to be a simple problem. Thanks
again for the help. I wish I knew some way to send this to you so you could
see how everything turned out. Late here on EST, will check in the AM.
Thanks again.

Barry

 

[AuldMannie]

AuldMannie:
When we linked the original 2 queries and added all the records from
tblMonthDays with records 1 through 31 and linked that to the tblExpenses we
got 31 records. Not all of the records are necessarily associated with an
ExpenseDate. Those records I am calling fake. They are there as filler so
that the report will have 31 (or as many days are in the month) lines to
mimic the old paper report, which was the original intent. Funny how you can
open a can of worms with what would appear to be a simple problem. Thanks
again for the help. I wish I knew some way to send this to you so you could
see how everything turned out. Late here on EST, will check in the AM.
Thanks again.

Barry

Barry

If you want, you can contact me on renniegordon hotmail.com
missed out the @ - don't want lots more "junk" mail
by people just clicking on link.
I'll get back with an e-mail address where you can send
anything to, and I can respond accordingly.
I had problems historically with hotmail
where it did not allow me to send .mdb files.

Hope you had a good rest.

Cheers
Auldmannie