Filter in a query 1

[logopolis]

I have set up a query with a table as its source data. This query then linked to a from for entering data into the table. The table holds information about children including their Date of birth. I would like to be able to filter out certain children when they reach a certain age. This filter age may change. I have therefore set up a table with a single field in which you can enter that filter age. I have been able to set the querry with both tables so that it only shows the records of children below the age set in that table (that was relatively easy) but as soon as you enter this second table into the querry the ability to enter new records is lost.

Anyone have any ideas? (can this be done in the table instead?)

many thanks

John

 

[cmmrfrds]

Do you want to add records that don't meet the join criteria between the 2 tables. If so, then left join the main table to the filter table. Do you have the primary key included in the Select Statement? As a last resort, you could make it a dynaset with inconsistent update, but in this case that is not necessary. Also, check that you allow additions on the Form.

 

[scriverb]

Use a Function to retrieve the table value and and call it from the Criteria row in your query:

Public Function RetrieveAge() as Integer
RetrieveAge = DLookUp("[i]datefieldname[/i]", "[i]tablename[/i]")
End Function

Now in your query use this Function call to retrieve your stored date value:

Select A.* from [I]yourtablename[/i] as A 
WHERE Int((DateDiff('d',A.[DOB],Date())/365.25)) <= RetrieveAge();

This is an example but your code would have to be updated with the WHERE portion calling the RetrieveDate().

Post back if you have any further questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[logopolis]

Thanks Bob for the code

I can see the logic of how this works but not enitrely sure where the code needs to go in the query, is this in a new field? Sorry probably being very dim. if it helps the age field in the child's record in already calculated from the date of birth and i have used the nz function to fill in any missing ages with a 0.

John

 

[scriverb]

There is a problem with that design. If you have an Age field that has a numerical integer that represents the child's age it is only as accurate as the last time you ran an update to the field. Each day the value may change based upon the DOB. What I have given you is a query that doesn't require you to update the AGE field in the table. Always use the DOB and the current date and the calculation provided in the query and you never have to change anything. Is accepted database design to always use a calculated value when possibile rather than a hard coded or saved calculated value. By using the lowest level of data value you can always be accurate with the calculated value at any time.

The function is just fine as it reads your table and retrieves the value that you are looking for as the Max age for selection.

Just update your query with the WHERE portion of the query provided updated with your table and field name:

WHERE Int((DateDiff('d',[[i]yourtablename[/i].[[i]yourDOBfield[/i]],Date())/365.25)) <= RetrieveAge();

Hopefully, this will make sense to you now. If not just post back with more questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[logopolis]

Sorry I think we asre talking at cross purposes. I use the following expression Age: DateDiff("yyyy",[DateofBirth],Now())+Int(Format(Now(),"mmdd")<Format([DateofBirth],"mmdd"))in the query to calculate the age so I dont have to update it in the table, it automatically calculates the age bassed on the date of birth and the current date. Is this correct. It is this field that I have then created a new expression from to create a Nz function to fill in where I do not have the date of birth of the child.

 

[scriverb]

I question whether you calculation just posted will accurately calculate the Age based upon the DOB and NOW(). The one I provided does in fact do that. In the WHERE statement I provided in my last posting compares this calculated AGE to the value of the only record in your Age parameter table.

Here is some updated SQL to display the Calculated Age field, the Age parameter value and use Age = 0 for Null DOG's. Give it a try.

Select A.*,   
Int((DateDiff('d',NZ(A.[[i][red]yourDOBfield[/red][/i]],Date()),Date())/365.25)) as CalcAge, RetrieveAge() as ComparisonAge 
from [red][i]yourtablename[/i][/red] as A 
WHERE Int((DateDiff('d',NZ(A.[[i][red]yourDOBfield[/red][/i]],Date()),Date())/365.25)) <= RetrieveAge();

Now update this SQL with your field and table names and see if this will provide you with the records that you want.

Post back with results.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[logopolis]

Ok will give it a go, I assume that I enter the above code in the SQL view, but I guess I have to do the Retrieveage section 1st (mentioned in and earlier message) as it is reffered to in this section and it is this I am not sure where to put. (sorry for asking such dum questions)

 

[scriverb]

Never a dumb question when we are learning new stuff. Copy and paste the function into a database module. Update the datafield name and tablename.

And yes, just copy and paste into the SQL window of a new query. Make sure all of the table and field names are correctl.

Post back with your findings.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[logopolis]

That's fantastic. Thanks for your help and your patience.

John

 

[scriverb]

Great. Glad that I could help. I take it that it selected the correct records for you. Thanks for the Star.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]