Calculate a column in a query

[FormerJarHead]

Hi All,

I think I have created a very unique problem for myself. Since I do not know how to write code, I was hoping to do my calculations through queries but I've come to the conclusion...I don't think it's possible.

In one of my forms (Form 1) I am getting information from a combo box and filling a field called [Replacing] which gives me an inmates name. I am also filling in another field (Date)on the same form using the same combo box. To get that field to populate with the info, I'm using [Replacing].[Column](2). (2) being the column with the date. This works well.

This form gets its data from a query that includes data from another table. In this query I have create an expression in a column: Completion DateateAdd("d",+179,[AdmitDate]). This gives me the date an inmate will be released from custody after they complete the 180 day program. There is another column with an expression in this query: Date:[Completion Date]-[Referral Date]. This gives me the number of days an inmate will need to wait to get into the program.



My problem is this: The calculations work. They just don't work correctly. For example: If Inmate Jones gets replaced on 10/4/12 by inmate Smith, I want to be able to calculate on Inmate Jones release date. Instead, it pulls in inmate Whites data. Inmate White is the first in the datasheet view. And it just goes down the list.

I've rambled enough. If this makes any sense and you have any words of wisdom, I will certainly welcome them. If you need to see this db, I can send it along as well.

Thank you for any help.

[June7]

I don't understand why you have to track that 'this inmate replaces that inmate'. One inmate leaves, one inmate comes in. Why are these two events associated?

If you want to calculate the earliest date a new inmate can enter the program (when a vacancy will open up), then that is easy enough, but it is irrelevant to this determination who is departing on that earliest date, the who would be incidental information.

frmClient is a very loooong form. You might consider using tab control for more compact organization of controls.

[FormerJarHead]

Thanks June7 for responding. The program will only be able to house 32 inmates at any one time. We have over 1500 inmates in custody but not all will qualify for the program. What I was trying to accomplish between the 2 forms is to be able to show what the waiting time between an inmate already in the program(frmClient) and an inmate waiting to get into the program(frmWaitList) will be. I need to be able to show on the WaitList form the amount of time an inmate needs to wait based on the inmate he/she will be replacing. So in some way I need to perform a calculate where the "Projected Completion" date on frm Client subtracts the referral date on the frmWaitList and make sure that it is subtracting from the correct inmate and not just subtracting inmate 1, inmate 2 etc...

I agree with your reccomendation also about using tab control. In fact, when I originally created this db that is how the form was designed. The Program Manager reminded me that cops will be inputing a lot of the info and I needed to make it more dummy friendly. We're both seeing her design...

Hi All,

I think I have created a very unique problem for myself. Since I do not know how to write code, I was hoping to do my calculations through queries but I've come to the conclusion...I don't think it's possible.

In one of my forms (Form 1) I am getting information from a combo box and filling a field called [Replacing] which gives me an inmates name. I am also filling in another field (Date)on the same form using the same combo box. To get that field to populate with the info, I'm using [Replacing].[Column](2). (2) being the column with the date. This works well.

This form gets its data from a query that includes data from another table. In this query I have create an expression in a column: Completion DateateAdd("d",+179,[AdmitDate]). This gives me the date an inmate will be released from custody after they complete the 180 day program. There is another column with an expression in this query: Date:[Completion Date]-[Referral Date]. This gives me the number of days an inmate will need to wait to get into the program.

My problem is this: The calculations work. They just don't work correctly. For example: If Inmate Jones gets replaced on 10/4/12 by inmate Smith, I want to be able to calculate on Inmate Jones release date. Instead, it pulls in inmate Whites data. Inmate White is the first in the datasheet view. And it just goes down the list.

I've rambled enough. If this makes any sense and you have any words of wisdom, I will certainly welcome them. If you need to see this db, I can send it along as well.

Thank you for any help.

[rpeare]

Can you post a prior version of access I'd like to take a look as well

[FormerJarHead]

Can you post a prior version of access I'd like to take a look as well

Not quite sure what you're asking me to do...I only have 2010

[rpeare]

You can convert a database to a prior version, just click on the windows symbol in the upper left, hover over 'save as' and different versions you can convert to should appear in a list.

[FormerJarHead]

Copy. Unfortunately with this being a County Computer, they lock me out of a lot of the options. I did however find the answer to my problem. I needed to write a small sting of code Me.WaitTime = DateDiff("d", ReferralDate, Rep). Works as it should. I want to thank all who took the time to look and offer assistance...Stay Safe