[TumTee Tum]

Hi
I have used the Between statement in a query where the attached form provides 2 numbers. My problem now is that the form will provide a single numeric value(say 46). My query needs to use this value with a + or - fixed integer. (Say +/-8)I have tried to modify the Between statement with no success.
Can anyone help please?
Alan

[OBP]

Alan, let me be clear on what you want.
You have Search Form, on the Form you have the Starting Value (46) and you want to be able to set or select a a difference to the Finishing value say +8?
So the Start is 46 and the finish is 54?

[TumTee Tum]

The form provides an age, say 46. I want to use an age between 38 and 54.
Alan

[OBP]

But will the user Input or select the plu or minus 8 or is that going to be fixed?
If it is fixed then use
Between forms![formname]![Age] - 8 and forms![formname]![Age] +8

[TumTee Tum]

In this use the it is a fixed value so I will use your statement. What would it be if the user supplies the variation?

[OBP]

Well I would use 2 invisible fields on the form to hold a start and finish value and use VBA code to populate it.
However you could use

Between forms![formname]![Age] - forms![formname]![Step] and forms![formname]![Age] + forms![formname]![Step]

where step is the value that the user has input.

[TumTee Tum]

Thanks for that. These answers are just what I wanted.
Cheers.

[TumTee Tum]

I now have a form with an underlying query which provides data required for 2 text boxes on the form. This Form/Query works but how can I link the required data from the query to the text boxes in the form. I don't particularly want to see the query, just get the data. So far my text boxes have displayed ?Name. Any suggestions gratefully received.
Alan

[OBP]

Are the 2 text boxes on a Form showing the Uotput from the search?
if they are you can refer to the fields on the original search form by using the same
forms![Formname]![Fieldname]
that you used in the query by putting it in the Control Source of the fields on the output form.

[TumTee Tum]

The overall objective is to have a form on which there are 3 combo's, Forename, Surname and approx. age.
3 search options 1. by individual personal ident
2. To list all in household ident
3. By Forename and Surname as individual
from the same query and table
I am envisaging an Option group for selection of relevant search.
Option 1 - Form provides data for Forename, Surname and age to query on a table. Text box on form relates to a field in the query obtained from table. This data is then used to obtain data from the table relating to that ident. (individual)
Option 2 - As above but used to obtain data from table about any individual at that location
Option 3 - Form provides Forename and Surname to Query to obtain data about any individual of that name regardless of age or location.
i'm not sure yet how to achieve all this so decided to tackle it piecemeal.
Incidentally this is data gathered from census returns covering nearly80 years

[OBP]

Alan, I am surprised you didn't ask me directly about this one.
I have posted a few versions of a Search Form like you described (but more complicated)
I have also posted a VBA version that creates the Query using VBA SQL.
Here is the more complicated Search Form, if you want the VBA SQL version just let me know, note the "Option" column in the query.

By the way there is also a Form Filter version as well where the census records are on the form.

[TumTee Tum]

I've had a look at this and can see what is going on but can't see how I can relate it to my problem. I had a lot of help in the past to do this but as events have progressed the requirements have changed. I think I will have to have a form/query combination for each option. Bit untidy but should work.