subform filter by value on main form

[carlp69]

I am developing on a holiday request system using SQL Project (.adp), here is my problem -

I have a main form 'frm_leaveApproval' containing a subform 'sub_LeaveApproval'. On the main form i have amongst other fields 'Department, Startdate & EndDate', on the subform i have fields 'Department, Name, Startdate & EndDate'
The link Child is 'Department' and the link Parent is Department' (I want the subform to display only info where the departments match)

Question ?
How can i filter the records on the subform to match not only the department but also the Startdate or EndDate on the subform fall within the Startdate or Endate of the Main form.

I have tried numerous thing but with little or no success.

It is driving me mad, Can anybody Help?

carlp69

 

[Nowell]

Hi,

Create a function for your sub forms record source, and set the values you want to filter by as parameters in the where clause.
Then set the input params in the sub form to tell the functions parameters where to get their data from.
like @startdate = forms!frm_leaveApproval!txtstart

hope this helps

 

[carlp69]

I have the following statement in VBA that works fine on a .MBD Database, But does not work on a .ADP Project. The error is 'Incorrect syntax near '#'

Any Ideas on the correct format for .ADP

Me.Form.RecordSource = "select * from qry_leave WHERE [startdate] = #" & Format(me.parent![Startdate], "mm/dd/yyyy&quot & "#"

Cheers

carlp69

 

[carlp69]

For future Reference

I've sorted it.

As per my last post but replace the # with '

carlp69

 

[cmmrfrds]

As you have discovered ADP uses ADO which is more ANSI compliant than Access SQL and the single quote is more typical to define literals. Also, the % replaces the * for wildcard in ADO.