Using Access 2000, the US Marshal Service reimburses our jail for federal prisoners we house at a daily date of $40. I need a database/report that will count the number of days per month times $40 showing each prisoners name and date of birth. The problem I've run into so far is if a prisoner came in say in the middle of May and left in the middle of June, I can't figure out how to count the days the prisoner was here for May and then how many days in June. The kicker is, the government won't pay for the last day the prisoner is here, so I need to subtract the last day of stay.I've tried date parameters and variants of [datepart], but no luck. Any suggestions would be appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Query Using Variable Dates 2
- Thread starter Budster13
- Start date
-
1
- #2
67peshawar294
Technical User
bud,
Do you have the report set up? If so try a textbox with;
=DateDiff("d",[indate],[outdate])-1
in the detail of the report for the total days, and if you need the amount, another text box with;
=[name of above textbox]*40
xaf294
Do you have the report set up? If so try a textbox with;
=DateDiff("d",[indate],[outdate])-1
in the detail of the report for the total days, and if you need the amount, another text box with;
=[name of above textbox]*40
xaf294
67peshawar294
Technical User
bud,
just thought, if you want the info in a query, create the query with the fields you need and in the "field" of a blank entry place the DateDiff function.
Datediff("d",[indate],[outdate])-1
and another field with the same thing only times 40.
Then you can create your report based on the query.
I usually create the query using as few fields as possible and then calculate the results I need in textboxes.
Keeps the num of queries to a minium.
xaf294
just thought, if you want the info in a query, create the query with the fields you need and in the "field" of a blank entry place the DateDiff function.
Datediff("d",[indate],[outdate])-1
and another field with the same thing only times 40.
Then you can create your report based on the query.
I usually create the query using as few fields as possible and then calculate the results I need in textboxes.
Keeps the num of queries to a minium.
xaf294
- Thread starter
- #4
xaf294,
Thanks for your quick response. We're almost there except for the second (or end)date parameter. We may still have the prisoner in custody at the end of the month or he may have been released sometime earlier in the month. He could also have been in and out of custody in a prior month and I only want to count this month. Also, since we'll have over 80 prisoners, I would have to populate the end date (or release date) field for every prisoner, every month. I was hoping there could be some variable date expression to use, but I don't find anything at Microsoft and I'm not that familiar with VBA, so yes a query would be easier for me to use.
I think I need something that will report a variable condition such as- if the prisoner is in custody at the end of the month, count the number of days in custody, else count the number of days in custody between the beginning of the month date (not necessarily the 1st day of the month, but could be) and the release date or/else end of the month. Kind of like storing a count of the dates in custody that can be calculated and going back and determining the dates the prisoner was in custody for this particular month only. It's the varible conditions that I can't figure out how to express. Can you help me further? Thanks
Thanks for your quick response. We're almost there except for the second (or end)date parameter. We may still have the prisoner in custody at the end of the month or he may have been released sometime earlier in the month. He could also have been in and out of custody in a prior month and I only want to count this month. Also, since we'll have over 80 prisoners, I would have to populate the end date (or release date) field for every prisoner, every month. I was hoping there could be some variable date expression to use, but I don't find anything at Microsoft and I'm not that familiar with VBA, so yes a query would be easier for me to use.
I think I need something that will report a variable condition such as- if the prisoner is in custody at the end of the month, count the number of days in custody, else count the number of days in custody between the beginning of the month date (not necessarily the 1st day of the month, but could be) and the release date or/else end of the month. Kind of like storing a count of the dates in custody that can be calculated and going back and determining the dates the prisoner was in custody for this particular month only. It's the varible conditions that I can't figure out how to express. Can you help me further? Thanks
-
1
- #5
Budster13: I took a stab at this one and this may be what you want.
I created a table for this example called tblPrisoners. PrisonerName text(10), StartDate date, ReleaseDate date. I then put it all combinations of dates. Startdates from 2 months ago with release dates during billing month, startdate and releasedate in billing month, etc. So, I think I have covered all circumstances.
This requires a double query. The first one I called qryCalcBilling1. Paste the following SQL in the SQL screen of a new query and name it the same.
SELECT tblPrisoners.Prisoner, tblPrisoners.StartDate, tblPrisoners.ReleaseDate, Switch(DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)>[tblPrisoners]![ReleaseDate],Null,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1)<=[tblPrisoners]![StartDate],Null,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)<[tblPrisoners]![StartDate],[tblPrisoners]![StartDate],[tblPrisoners]![StartDate]<DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)) AS BegBillingDate, Switch([tblPrisoners]![ReleaseDate]>=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1) And [tblPrisoners]![ReleaseDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),[tblPrisoners]![ReleaseDate],IsNull([tblPrisoners]![ReleaseDate]) And [tblPrisoners]![StartDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),[tblPrisoners]![StartDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1) And [tblPrisoners]![ReleaseDate]>DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1)) AS EndBillingDate
FROM tblPrisoners;
Now create another new query calling it qryCalcPrisonerBilling and paste the following SQL code into the SQL screen. Query qryCalcPrisonerBilling uses the results of
qryCalcBilling1 to create your final results. To run a billing report you run qryCalcPrisonerBilling.
SELECT qryCalcBilling1.Prisoner, qryCalcBilling1.StartDate, qryCalcBilling1.ReleaseDate, Format(DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1),"mm/yyyy"
AS BillingMonth, qryCalcBilling1.BegBillingDate, qryCalcBilling1.EndBillingDate, IIf(DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate])=0,1,DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate])) AS DaysToBill, Format(IIf(DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate])=0,1,DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate]))*40,"$#,###.00" AS BillingAmt
FROM qryCalcBilling1;
I made the assumption that you will always run the Billing query in the month following the billing month. I am using the System date as an indicator of which month to rollup the bills for. (i.e. Run date 7/3/2002: Billing Month(6/1/2002 - 6/30/2002))
Also, in the event a prisoner started on 6/30/2002 and was released the same day the calculation counts this as 1 day rather than 0. I didn't think they were going to subtract a day from a one day stay. Maybe so. Let me know if that is the case and we can adjust. You see that scenario would calculate 0 days during the DateDiff calculation so I added one if the result was 0.
Test this out thoroughly with a test table as I described and then you can modify the queries to fit the table names and field names of your tables. You might want to use MS WORD with the SQL to do a Find and Replace operation for that function as the SQL is quite complex.
Good luck and let me know if this works for you.
Bob Scriver
I created a table for this example called tblPrisoners. PrisonerName text(10), StartDate date, ReleaseDate date. I then put it all combinations of dates. Startdates from 2 months ago with release dates during billing month, startdate and releasedate in billing month, etc. So, I think I have covered all circumstances.
This requires a double query. The first one I called qryCalcBilling1. Paste the following SQL in the SQL screen of a new query and name it the same.
SELECT tblPrisoners.Prisoner, tblPrisoners.StartDate, tblPrisoners.ReleaseDate, Switch(DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)>[tblPrisoners]![ReleaseDate],Null,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1)<=[tblPrisoners]![StartDate],Null,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)<[tblPrisoners]![StartDate],[tblPrisoners]![StartDate],[tblPrisoners]![StartDate]<DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)) AS BegBillingDate, Switch([tblPrisoners]![ReleaseDate]>=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1) And [tblPrisoners]![ReleaseDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),[tblPrisoners]![ReleaseDate],IsNull([tblPrisoners]![ReleaseDate]) And [tblPrisoners]![StartDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),[tblPrisoners]![StartDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1) And [tblPrisoners]![ReleaseDate]>DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1)) AS EndBillingDate
FROM tblPrisoners;
Now create another new query calling it qryCalcPrisonerBilling and paste the following SQL code into the SQL screen. Query qryCalcPrisonerBilling uses the results of
qryCalcBilling1 to create your final results. To run a billing report you run qryCalcPrisonerBilling.
SELECT qryCalcBilling1.Prisoner, qryCalcBilling1.StartDate, qryCalcBilling1.ReleaseDate, Format(DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1),"mm/yyyy"
AS BillingMonth, qryCalcBilling1.BegBillingDate, qryCalcBilling1.EndBillingDate, IIf(DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate])=0,1,DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate])) AS DaysToBill, Format(IIf(DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate])=0,1,DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate]))*40,"$#,###.00" AS BillingAmtFROM qryCalcBilling1;
I made the assumption that you will always run the Billing query in the month following the billing month. I am using the System date as an indicator of which month to rollup the bills for. (i.e. Run date 7/3/2002: Billing Month(6/1/2002 - 6/30/2002))
Also, in the event a prisoner started on 6/30/2002 and was released the same day the calculation counts this as 1 day rather than 0. I didn't think they were going to subtract a day from a one day stay. Maybe so. Let me know if that is the case and we can adjust. You see that scenario would calculate 0 days during the DateDiff calculation so I added one if the result was 0.
Test this out thoroughly with a test table as I described and then you can modify the queries to fit the table names and field names of your tables. You might want to use MS WORD with the SQL to do a Find and Replace operation for that function as the SQL is quite complex.
Good luck and let me know if this works for you.
Bob Scriver
- Thread starter
- #6
Need a slight modification to the query "qryCalcBilling1". Add the Red equals(=) sign as seen in the snipet of SQL code below. It is at the very end of the SQL.
. . .And [tblPrisoners]![ReleaseDate]>=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1)) AS EndBillingDate
FROM tblPrisoners;
Just update your code with the equals sign in the location indicated above and it should handle all scenarios.
Bob scriver
. . .And [tblPrisoners]![ReleaseDate]>=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1)) AS EndBillingDate
FROM tblPrisoners;
Just update your code with the equals sign in the location indicated above and it should handle all scenarios.
Bob scriver
- Thread starter
- #8
Bob, we are very close now, but still a few problems. Here's how I populated the tables so you can run the same way I have. I think this covers all the possibilities, but check me out.
[PrisonerName] [StartDate] [ReleaseDate]
Jones 06/01/2002 06/25/2002
Smith 06/12/2002
Black 07/01/2002
Brown 07/01/2002 07/25/2002
Blue 06/14/2002 07/15/2002
Green 05/01/2002
White 05/02/2002 05/15/2002
The first problem was in the SQL for[qryCalcBilling1].I changed [tblPrisoners.Prisoner]to[tblPrisoners.PrisonerName].
After running the [qryCalcPrisonerBilling] with data as shown above Jones did not populate [DaysToBill] and Green shows 29 days, but he would have been here for the whole month of June, thus 30 days. Also, there's nothing for anyone in the [BegBillingDate] field.
Also, you correctly assumptioned that I would be running the report during the next month, but I may have to run an on demand report for prior months. I could change the system date to fool the computer, but is there a way to run this with a "between,and" statement? I'm including my email address in my personal profile temporarily if you would like to correspond in the future. Thanks Budster13
[PrisonerName] [StartDate] [ReleaseDate]
Jones 06/01/2002 06/25/2002
Smith 06/12/2002
Black 07/01/2002
Brown 07/01/2002 07/25/2002
Blue 06/14/2002 07/15/2002
Green 05/01/2002
White 05/02/2002 05/15/2002
The first problem was in the SQL for[qryCalcBilling1].I changed [tblPrisoners.Prisoner]to[tblPrisoners.PrisonerName].
After running the [qryCalcPrisonerBilling] with data as shown above Jones did not populate [DaysToBill] and Green shows 29 days, but he would have been here for the whole month of June, thus 30 days. Also, there's nothing for anyone in the [BegBillingDate] field.
Also, you correctly assumptioned that I would be running the report during the next month, but I may have to run an on demand report for prior months. I could change the system date to fool the computer, but is there a way to run this with a "between,and" statement? I'm including my email address in my personal profile temporarily if you would like to correspond in the future. Thanks Budster13
Okay, here is the new SQL for the first query. qryCalcBilling1
SELECT tblPrisoners.Prisoner, tblPrisoners.StartDate, tblPrisoners.ReleaseDate, Switch(DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)>[tblPrisoners]![ReleaseDate],Null,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1)<=[tblPrisoners]![StartDate],Null,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)<[tblPrisoners]![StartDate],[tblPrisoners]![StartDate],[tblPrisoners]![StartDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)) AS BegBillingDate, Switch([tblPrisoners]![ReleaseDate]>=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1) And [tblPrisoners]![ReleaseDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),[tblPrisoners]![ReleaseDate],IsNull([tblPrisoners]![ReleaseDate]) And [tblPrisoners]![StartDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),[tblPrisoners]![StartDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1) And [tblPrisoners]![ReleaseDate]>=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1)) AS EndBillingDate
FROM tblPrisoners;
There was only one equal sign change but it is easier to give the SQL to you complete than instruct where to put in a single character. Just Copy and Paste.
That fixes the Jones situation. As for Green, you said in your original post that after totaling up the days you had to subtract 1 from the days because you don't get reimbursed for the last day. Each of the days calculations perform this adjustment except the incarceration and release of prisoner on the same day. I left that at 1 for billings. If Green started on 6/1/2002 and was released on 6/30/2002 that calculations to 30 days minus the 1 gives you 29. That is what the query is billing for.
Correct??
As for the prompting for the run date. The entire set of queries uses the system date to analyze your tables. To perform an analysis with a prompted date we will have to replace all of the references to Date() in the queries and replace them with a prompt. I will give it a try over the weekend and see how it works.
Bob Scriver
SELECT tblPrisoners.Prisoner, tblPrisoners.StartDate, tblPrisoners.ReleaseDate, Switch(DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)>[tblPrisoners]![ReleaseDate],Null,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1)<=[tblPrisoners]![StartDate],Null,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)<[tblPrisoners]![StartDate],[tblPrisoners]![StartDate],[tblPrisoners]![StartDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)) AS BegBillingDate, Switch([tblPrisoners]![ReleaseDate]>=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1) And [tblPrisoners]![ReleaseDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),[tblPrisoners]![ReleaseDate],IsNull([tblPrisoners]![ReleaseDate]) And [tblPrisoners]![StartDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1),[tblPrisoners]![StartDate]<=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1) And [tblPrisoners]![ReleaseDate]>=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1),DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1)) AS EndBillingDate
FROM tblPrisoners;
There was only one equal sign change but it is easier to give the SQL to you complete than instruct where to put in a single character. Just Copy and Paste.
That fixes the Jones situation. As for Green, you said in your original post that after totaling up the days you had to subtract 1 from the days because you don't get reimbursed for the last day. Each of the days calculations perform this adjustment except the incarceration and release of prisoner on the same day. I left that at 1 for billings. If Green started on 6/1/2002 and was released on 6/30/2002 that calculations to 30 days minus the 1 gives you 29. That is what the query is billing for.
Correct??
As for the prompting for the run date. The entire set of queries uses the system date to analyze your tables. To perform an analysis with a prompted date we will have to replace all of the references to Date() in the queries and replace them with a prompt. I will give it a try over the weekend and see how it works.
Bob Scriver
- Thread starter
- #10
Bob,
I'm sorry I confused you. It's true that the USMS will not pay for the last day a prisoner is in custody/released, however, Green does not have a release date, therefore should be billed for the 30 days of June.
I have written several excel and access programs, but nothing as complex as this one. Clearly, you are quite an accomplished programmer. I hope you also teach somewhere because you obviously have the qualities necessary. Looking forward to your weekend solution-Please email me.Thanks ever so much, Budster13
I'm sorry I confused you. It's true that the USMS will not pay for the last day a prisoner is in custody/released, however, Green does not have a release date, therefore should be billed for the 30 days of June.
I have written several excel and access programs, but nothing as complex as this one. Clearly, you are quite an accomplished programmer. I hope you also teach somewhere because you obviously have the qualities necessary. Looking forward to your weekend solution-Please email me.Thanks ever so much, Budster13
Give this a try in the SQL property of qryCalcPrisonerBilling.
SELECT qryCalcBilling1.Prisoner, qryCalcBilling1.StartDate, qryCalcBilling1.ReleaseDate, Format(DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1),"mm/yyyy" AS BillingMonth, qryCalcBilling1.BegBillingDate, qryCalcBilling1.EndBillingDate, IIf(DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate])=0,1,IIf(([qryCalcBilling1]![EndBillingDate]=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1) And [qryCalcBilling1]![ReleaseDate]>DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1)) Or IsNull([qryCalcBilling1]![ReleaseDate]),DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate])+1,DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate]))) AS DaysToBill, Format([DaysToBill]*40,"$#,###.00" AS BillingAmt
FROM qryCalcBilling1;
I believe the numbers will be correct with this change. I still have to work on the prompted query for you. Will post as soon as completed.
Bob Scriver
SELECT qryCalcBilling1.Prisoner, qryCalcBilling1.StartDate, qryCalcBilling1.ReleaseDate, Format(DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1),"mm/yyyy"
AS BillingMonth, qryCalcBilling1.BegBillingDate, qryCalcBilling1.EndBillingDate, IIf(DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate])=0,1,IIf(([qryCalcBilling1]![EndBillingDate]=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1) And [qryCalcBilling1]![ReleaseDate]>DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1-1)) Or IsNull([qryCalcBilling1]![ReleaseDate]),DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate])+1,DateDiff("d",[qryCalcBilling1]![BegBillingDate],[qryCalcBilling1]![EndBillingDate]))) AS DaysToBill, Format([DaysToBill]*40,"$#,###.00" AS BillingAmtFROM qryCalcBilling1;
I believe the numbers will be correct with this change. I still have to work on the prompted query for you. Will post as soon as completed.
Bob Scriver
- Thread starter
- #12
There certainly should be a date in the BegBillingDate field if it is a billable month. The records where start date is after the ending date of the month or Release date is prior to the beginning of the billable month will have no dates in either the BegBillingDate or EndBillingDate fields. But, all others should.
If you are missing some dates that you copy of the first query got messed up. I am sending you a copy of my working database with the correct query setup. Take a look and see if it is like yours.
Bob Scriver
If you are missing some dates that you copy of the first query got messed up. I am sending you a copy of my working database with the correct query setup. Take a look and see if it is like yours.
Bob Scriver
- Thread starter
- #14
Bob,
I still don't have any data in the BegBillingDate field. Note I had to convert the db to Access 2000; did this change things? I'm sending you the same db you sent me now named Prisoner1 and a working copy of the db I developed with your tables and queries added named USMS v1.0a. Notice we also get mileage and reimbursement for personnel used to transport to federal court. I think I had that problem licked, but any suggestions would be appreciated.
I should also point out that even though I'm not seeing data in the BegBillingDate field, the queries are reporting the DaysToBill and BillingAmt correctly. So, unless there's some other reason I'm not seeing, no big deal-- the objective of the query has been met. Budster13
I still don't have any data in the BegBillingDate field. Note I had to convert the db to Access 2000; did this change things? I'm sending you the same db you sent me now named Prisoner1 and a working copy of the db I developed with your tables and queries added named USMS v1.0a. Notice we also get mileage and reimbursement for personnel used to transport to federal court. I think I had that problem licked, but any suggestions would be appreciated.
I should also point out that even though I'm not seeing data in the BegBillingDate field, the queries are reporting the DaysToBill and BillingAmt correctly. So, unless there's some other reason I'm not seeing, no big deal-- the objective of the query has been met. Budster13
I have returned a database to you with a updated date prompt process to select and run previous months. I was not able to unfortunately to open your database as it is ACCESS 2000 and I only have 97. You will have to import a number of items into your database after conversion takes place to make it work in your application.
Let me know how it works out.
Bob Scriver
Let me know how it works out.
Bob Scriver
- Thread starter
- #16
Bob,
I just got a chance to open the latest version. Absolutely great! It's everything I wanted it to do. I'll make some minor adjustments and send it back to you just for your archives. Also, thanks for explaining the DAO stuff and why the queries only run with the form. You have really helped me and you are a great teacher!![[medal]](/data/assets/smilies/medal.gif "[medal] [medal]")
![[smarty]](/data/assets/smilies/smarty.gif "[smarty] [smarty]")
Budster13
I just got a chance to open the latest version. Absolutely great! It's everything I wanted it to do. I'll make some minor adjustments and send it back to you just for your archives. Also, thanks for explaining the DAO stuff and why the queries only run with the form. You have really helped me and you are a great teacher!
Budster13- Status
Similar threads
- Locked
- Question