Problems Filtering Subform

[carlp69]

I have a main form frm_Main and on the main form i have a subform sub_Main.

On the sub_main i have set the onload to execute the following -

DoCmd.OpenForm "sub_LeaveApproval", , , "[startdate] = #18/11/2003#"

When i execute the sub_main form as a standalone form the filter works fine but when the sub_main is within the frm_main the filter does not work.

Any Ideas


carlp69

 

[KenReay]

Hi

On the main form, put a control say txtStartDate

In the form laod event of main form, put txtStartDate = 18/11/2003

In the subform CONTROL properties set the master/child link properties to Master: txtStartDate ; Child: StartDate

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[carlp69]

Ken

Works for single date but what if i need to go between a range of dates?

carlp69

 

[KenReay]

Hi

That was not what you asked for

For a range of dates I would approach it differently

On the main form put text box controls for the from date and to date,

Make the recordsource of the sub form a query with a criteria of between txtStartDate and txtEndDate (the controls mentioned above)

in the after update event of both text box controls put a staement MySubFormControl.Requery, where MySubFormControl is the anem of your subform control

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[carlp69]

Ken

Sorry for the misleading request.

How do you create the record source query to include the txtStartdate and txtEnddate, I have try editing the query with the query builder but says the 'txtStartDate in expresion is not part of the query' I have tried to just type in the query but this wont work either.

Thanks again

carlp69

 

[carlp69]

Ignore the above, I have the statement

SELECT EmployeeID, Department, Surname, Firstname FROM qry_Leave
WHERE Startdate > '[forms]![frm_leaveApproval]![txt_startdate]'

I get the error 'Syntax error converting datetime from character string'

I have tried to use the 'Convert' but am unsure of the format.(this is a .adp project) will keep trying!.

 

[KenReay]

hi

It is a date fiedl so you do not want ' around it

If this string is from the query designer you just need:

SELECT EmployeeID, Department, Surname, Firstname FROM qry_Leave
WHERE Startdate > [forms]![frm_leaveApproval]![txt_startdate]

if you are building it in code you need

"SELECT EmployeeID, Department, Surname, Firstname FROM qry_Leave
WHERE Startdate > #" & Format([forms]![frm_leaveApproval]![txt_startdate],"mm/dd/yy&quot & "#"

Note the use of the format statement to force US style mm/dd/yy date format, this is expected in Jet SQL, no matter what the locale settings of the PC



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[carlp69]

Tried taking the ' off but now get error 'Error in WHERE clause near '!'.Unable to parse query text."'

Statement now reads -

SELECT EmployeeID, Department, Surname, FirstName, Startdate
FROM qry_Leave
WHERE Startdate > [Forms]![frm_leaveApproval]![txt_startdate]

Any Ideas

carlp69

 

[KenReay]

Hi

Is this string from the query designer or from code?

have you taken note of my comments in the last post re this?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[carlp69]

This was taken from query designer.

I don't think i've missed anything from your previous posting.

cheers


carlp69

 

[KenReay]

Hi

Are you absolutely sure you have correct form and control names, easier way to be sure is to use the builder button to set the criteria

there is nothing wrong in principle with what you are doing, but I am usure of the ADP Project aspect which you mentioned a few posts back, are you connection to an mdb or a msde database?, perhaps the syntax is slightly different for what will then by an SQL Server SQl query

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[carlp69]

Ken

Have cracked it!

In SQL projects (.adp) replace the # around the date with ' (single quotes).

The # around the date is for the MS Jet engine and not SQL

Thanks for your previous help with this matter.

Carlp69

 

[thewizz]

on the prob at the begining ....
got 2 forms main_frm with a sub_frm (actualy many but same problem/diff. Frm.)

this is actualy for parts of computers (in subfrm)my source for the subform is a query et the sub_frm has a fitler saved (& is applyed on oppenning) the thing is that when the subform is opened within the main(pc desc.+detail in sub_frms) it those not filter at all !
any hint? btw the query is kinda complicated (3 tbl) & i dont whant to have to do a seperate query for all forms with a where clause
is there a way ?

tnks
wizz

 

[KenReay]

Hi

I answered this in my first post in this thread, if you have a straight match ir field1 = field2, then use the master/child properties of the sub form control, if you have a between situation then you will haev to use a WHERE clause

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK