Selecting records for a recordset using Datediff

[MarionD]

Hi All,

I Need some help with the Syntax of a "relatively" simple Statement... I just cannot get it to work!

I have a table with Family_Name and Birthdate and tblxx_Familie_ID:

I am trying to create a recordset that selects all records that belong to a certaim Family and where the Person is under 216 months old (under 18)

Set ExternKinder = db.OpenRecordset("Select * from tbl_ExterneGeschwister where tblxx_familie_id=" & Familie!ID & " and " & "Datediff('m',[Geboren_am],[me![Buchungsdatum]) <= 216)

'Buchungsdatum is a filed on a form that I start the procedure from...

Would really appreciate some help if anyone has time...
Thanks

Marion

 

[pr2-eugin]

Always use a Debug.Print to test your SQL berated by VBA.

 

[MarionD]

Thanks for the answer Paul...
It doesn't select the records.. says it Needs another Parameter, but I can't figure out which one!

 

[pr2-eugin]

Show what the Debug has generated, also try copying it to a regular Query builder, normally it will prompt which parameter is needed or what error is with the statement.

 

[JHB]

..
Set ExternKinder = db.OpenRecordset("Select * from tbl_ExterneGeschwister where tblxx_familie_id=" & Familie!ID & " and " & "Datediff('m',[Geboren_am],[me![Buchungsdatum]) <= 216)

You've a [ to much, and missing a " in the end, + then value of "Buchungsdatum" need to be in the string:

Set ExternKinder = db.OpenRecordset("Select * from tbl_ExterneGeschwister where tblxx_familie_id=" & Familie!ID & " and " & "Datediff('m',[Geboren_am]," & DateValue(me![Buchungsdatum]) & ") <= 216)"

 

[MarionD]

Thanks so much for the reply... unfortunately still not working.. I have done a Little Workaround
Set ExternKinder = db.OpenRecordset("SELECT * from tbl_ExterneGeschwister where tblxx_familie_id=" & Familie!ID)
If ExternKinder.RecordCount > 0 Then ExternKinder.MoveFirst Else GoTo 1100
Do Until ExternKinder.EOF
If DateDiff("m", ExternKinder![Geborenam], Me.Buchungsdatum) > 217 Then
ExternKinder.Delete
End If
ExternKinder.MoveNext
Loop
By deleting the records out the record set.... but it would be so much nicer to select the correct records.

Will Keep trying.. thank again

 

[jdraw]

Where exactly are you trying to run this vba? If it is not on a Form/Form event then Me may be a problem.

If we knew your table structures, it would be easier to help.
This is a table tbl_ExterneGeschwister ,
but what is this tblxx_familie_id

GOto 1100 is a little suspicious also.