Simple Query to Retrieve a Date for a Formula

[dgutsche]

Hi there

I have a problem that I need to wrap my head around to make some of my forms work. I have a form (see first picture below) that the user will enter the values for [Cylinder No.] and [Date Broken] into.





The field [Age] is to be calculated using the difference between the [Date Broken] and a value I want to have returned by a simple query. That query should take the value [Cylinder No.] which corresponds to the [CylinderName] field in a table called [Cylinders]. It should return the [Date Made] value for that unique cylinder to the query which is then passed into my formula for the field [Age], the formula being:

=DateDiff([d],[Date Retrieval]![Date Made],[Date Broken])

When I created the query I obviously am not passing the [Cylinder No.] value in correctly so I am unsure what I was supposed to put into the criteria field for this to work. If anyone can explain what I am doing wrong that would be great as I have to repeat a similar query on a few forms.

[burrina]

If your asking it to prompt you, then put it [CylinderName] under criteria. Unclear really of your question. You said CylinderName is really CylinderNo and needs a value.
Is it okay for CylinderName to be null? Why is Age missing from your criteria?
Please explain further.

[dgutsche]

Thanks Dave!

So in this case I don't want a prompt for the [Cylinder Name] I want instead for it to use the user supplied value in the field [Cylinder No.] in the form. Since I want the query to be based on that value (it's unique and I want it to use it to find the correct date in the other table) I don't think I can let it stay null. [Age] is not part of my criteria because the purpose of the query is to return the date so an age can be calculated.

Dustin

[burrina]

Aah, Okay! Then use something like this for your criteria.

[Forms]![YourFormNameHere]![CylinderNo]

HTH

[dgutsche]

Alright once I get back on track I think this will help. That naming format strikes me as somewhat familiar to something I did last I touched this database a couple weeks ago.

Thanks again!