How do I reference a Calculated Field?

[Zerdan]

I have a form that is a search form to filter results and open another form to display the results. Things work fine when I'm comparing entered values to columns in my table, but now I'm trying to add a little more functionality and running into trouble.



On my form that is displaying the data, I have a calculated field (a number). I want to add a search criteria that says... If calculated field is > "entered_value" Then ...

What I can't figure out is how to reference the calculated field in Visual Basic. When I did dates I just referenced the column, but since this calculated field doesn't have a column (and from what I have read I should not create a column to store it), I can't figure out how to reference it.

[jzwp11]

What values are used to perform the calculation? From where do they come?

[Zerdan]

The values used to perform the calculation are dates. I have the form calculating the difference between two dates that are stored in the database (and then converting that difference to seconds). So the calculation for the field looks like = [Date1] - [Date2] * 60 * 60 * 24

Date1 and Date2 are columns in my table stored as DateTime.

What's puzzling me now is that my form displays the calculation just fine, but I've been playing with this since I posted and I have gotten to the point now where it says "Implicit conversion from data type datetime to int is not allowed." I'm assuming the error is because Visual Basic isn't liking the way I did that calculation.

Any suggestions?

[alansidman]

In VBA, you can refer to the calculated control in your form by using:

me.textboxname.value

this will tell access to take the value that is in the textbox that contains the calculation for the form record that is open.

Alan

[Zerdan]

this will tell access to take the value that is in the textbox that contains the calculation for the form record that is open.

I tried this code, but doesn't seem to work. I'm assuming because my form isn't open yet. I'm trying to use this search feature I'm building to help filter the results of the form to be displayed.

[jzwp11]

First, I would recommend that you use the datediff() function to find the difference. Using the s interval in the datediff function will convert to seconds for you:

datediff("s",date1field, date2field)

You can reference the control that holds the difference in VBA code by just using the name of the control in your code (me.controlname)

Access actually stores dates as a number with the integer portion representing the number of days since 12/31/1899 and the decimal portion indicating fractions of a day (ie. the time)

[Zerdan]

Problem solved. Thanks! This is my first day working in Visual Basic, sorry for the stupid questions. I come from Ruby where these calculations are so much simpler

[jzwp11]

You're welcome; good luck on your project.