Calculation/Formula/VBA Code if possible

[Stockwell43]

Hello,

I am working on a Title Search Database and have a question.

I need a calculation(if possible) to do the following:

On Selection Title Search, If Title Search= Harris County and Actual Receipt
minus Date Received is <3 then KPI=Yes

The fields Name are: Title Search(CBO), Actual Receipt, Date Received and KPI.

Does that make sense?

I can work around this if need be, I was just tryint to get fancy but do not
know how to code this many fields. Any help is most appreciated!

Thank you!!

 

[Beetle]

Assuming that your "Actual Receipt" and "Date Received" fields both have a
data type of date/time then you could use the DateDiff function in the On
Current event of your form. The code might look something like this;

dim varTestDate as Variant

varTestDate = DateDiff("d", [Actual Receipt], [Date Received])

If varTestDate <3 And [Title Search] = "Harris County" Then

me![KPI] = "Yes"

Else

me![KPI] = "No"

End If

HTH

 

[OldPro]

Hello,

I am working on a Title Search Database and have a question.

I need a calculation(if possible) to do the following:

On Selection Title Search, If Title Search= Harris County and Actual Receipt
minus Date Received is <3 then KPI=Yes

The fields Name are: Title Search(CBO), Actual Receipt, Date Received and KPI.

Does that make sense?

I can work around this if need be, I was just tryint to get fancy but do not
know how to code this many fields. Any help is most appreciated!

Thank you!!

Do you 1) Only want to display the result on the screen when a user
clicks on a record, 2) save the result to the table for all records
when a user clicks a command button, or 3) display a recordset with
the caculated field?

 

[Stockwell43]

Hello,

I would like to show the value on the form and also if possible, save it to
the table so I can pull reports.

 

[Stockwell43]

Hi Beetle,

Thank you for your quick response.

When I inserted the code in the Current Event on the form, it comes up with
an error message:

Run time error 2465 - Access can't find the field 'l' referred to in your
expression.

Did I do something wrong?

Thanks!!

Assuming that your "Actual Receipt" and "Date Received" fields both have a
data type of date/time then you could use the DateDiff function in the On
Current event of your form. The code might look something like this;

dim varTestDate as Variant

varTestDate = DateDiff("d", [Actual Receipt], [Date Received])

If varTestDate <3 And [Title Search] = "Harris County" Then

me![KPI] = "Yes"

Else

me![KPI] = "No"

End If

HTH

Hello,

I am working on a Title Search Database and have a question.

I need a calculation(if possible) to do the following:

On Selection Title Search, If Title Search= Harris County and Actual Receipt
minus Date Received is <3 then KPI=Yes

The fields Name are: Title Search(CBO), Actual Receipt, Date Received and KPI.

Does that make sense?

I can work around this if need be, I was just tryint to get fancy but do not
know how to code this many fields. Any help is most appreciated!

Thank you!!

 

[Stockwell43]

My apologigies, I needed to update the field names to what I have on the
form. I don't get the error message anymore but when I put in an Actual Date
of 7/12/2007 and a Date Received of 7/10/2007 and select Harris County from
the cbo, it doesn't change to yes, it just stays on no.

Assuming that your "Actual Receipt" and "Date Received" fields both have a
data type of date/time then you could use the DateDiff function in the On
Current event of your form. The code might look something like this;

dim varTestDate as Variant

varTestDate = DateDiff("d", [Actual Receipt], [Date Received])

If varTestDate <3 And [Title Search] = "Harris County" Then

me![KPI] = "Yes"

Else

me![KPI] = "No"

End If

HTH

Hello,

I am working on a Title Search Database and have a question.

I need a calculation(if possible) to do the following:

On Selection Title Search, If Title Search= Harris County and Actual Receipt
minus Date Received is <3 then KPI=Yes

The fields Name are: Title Search(CBO), Actual Receipt, Date Received and KPI.

Does that make sense?

I can work around this if need be, I was just tryint to get fancy but do not
know how to code this many fields. Any help is most appreciated!

Thank you!!

 

[Beetle]

Try switching the order of those two controls inside the DateDiff function.
In other words, if it is currently DateDiff("d", [Actual Receipt], [Date
Received]) then make it DateDiff("d", [Date Received], [Actual Receipt]). It
might be calculating the wrong way.

HTH

My apologigies, I needed to update the field names to what I have on the
form. I don't get the error message anymore but when I put in an Actual Date
of 7/12/2007 and a Date Received of 7/10/2007 and select Harris County from
the cbo, it doesn't change to yes, it just stays on no.

Assuming that your "Actual Receipt" and "Date Received" fields both have a
data type of date/time then you could use the DateDiff function in the On
Current event of your form. The code might look something like this;

dim varTestDate as Variant

varTestDate = DateDiff("d", [Actual Receipt], [Date Received])

If varTestDate <3 And [Title Search] = "Harris County" Then

me![KPI] = "Yes"

Else

me![KPI] = "No"

End If

HTH

Hello,

I am working on a Title Search Database and have a question.

I need a calculation(if possible) to do the following:

On Selection Title Search, If Title Search= Harris County and Actual Receipt
minus Date Received is <3 then KPI=Yes

The fields Name are: Title Search(CBO), Actual Receipt, Date Received and KPI.

Does that make sense?

I can work around this if need be, I was just tryint to get fancy but do not
know how to code this many fields. Any help is most appreciated!

Thank you!!

 

[Stockwell43]

Hurrah! Works like a gem. This will help me out tremendously!!

Thank you so much for your help Beetle!!

Try switching the order of those two controls inside the DateDiff function.
In other words, if it is currently DateDiff("d", [Actual Receipt], [Date
Received]) then make it DateDiff("d", [Date Received], [Actual Receipt]). It
might be calculating the wrong way.

HTH

My apologigies, I needed to update the field names to what I have on the
form. I don't get the error message anymore but when I put in an Actual Date
of 7/12/2007 and a Date Received of 7/10/2007 and select Harris County from
the cbo, it doesn't change to yes, it just stays on no.

Assuming that your "Actual Receipt" and "Date Received" fields both have a
data type of date/time then you could use the DateDiff function in the On
Current event of your form. The code might look something like this;

dim varTestDate as Variant

varTestDate = DateDiff("d", [Actual Receipt], [Date Received])

If varTestDate <3 And [Title Search] = "Harris County" Then

me![KPI] = "Yes"

Else

me![KPI] = "No"

End If

HTH

:

Hello,

I am working on a Title Search Database and have a question.

I need a calculation(if possible) to do the following:

On Selection Title Search, If Title Search= Harris County and Actual Receipt
minus Date Received is <3 then KPI=Yes

The fields Name are: Title Search(CBO), Actual Receipt, Date Received and KPI.

Does that make sense?

I can work around this if need be, I was just tryint to get fancy but do not
know how to code this many fields. Any help is most appreciated!

Thank you!!

 

[OldPro]

My apologigies, I needed to update the field names to what I have on the
form. I don't get the error message anymore but when I put in an Actual Date
of 7/12/2007 and a Date Received of 7/10/2007 and select Harris County from
the cbo, it doesn't change to yes, it just stays on no.

Assuming that your "Actual Receipt" and "Date Received" fields both have a
data type of date/time then you could use the DateDiff function in the On
Current event of your form. The code might look something like this;

dim varTestDate as Variant

varTestDate = DateDiff("d", [Actual Receipt], [Date Received])

If varTestDate <3 And [Title Search] = "Harris County" Then

me![KPI] = "Yes"

Else

me![KPI] = "No"

End If

"Stockwell43" wrote:

- Show quoted text -

Put the code in the click event of the combobox.