Date Range in query

[Gavx]

My subform is based on a query and lists transactions. In this query I have a field called txtTransDate whose criteria is;
Between
[Forms]![fmAllocation]![txtTransactionDate]+5
And
[Forms]![fmAllocation]![txtTransactionDate]-5

The subform child field is TransactionDate as is the master field.

I would like the subform to show a range of transaction dates +/- 5 but it only shows the date that matches the main form.
Where am I going wrong?

thanks

 

[CJ_London]

two things.

1. you need to format the date to the US style of mm/dd/yyyy if your standard format is different

2. if frmAllocation is a subform then you need to refer to the main form, then the subform control name, then the form object and finally the control - If you used the expression builder, you would discover this for yourself

so something like

Between
format([Forms]!mainformname!subformcontrolname!form![txtTransactionDate]+5,"mm/dd/yyyy")
And
format([Forms]!mainformname!subformcontrolname!form![txtTransactionDate]-5,"mm/dd/yyyy")

 

[arnelgp]

Remove the master/child link fields from the subform.
Add code to the AfterUpdate event of txtTransactionDate to Requery the subform:

Private sub txtTransationDate_AfterUpdate()
Me.subformname.requery
End sub

Add also code to the mainform's current event.

private sub form_current()
Call txtTransaction_AfterUpdate
End sub

 

[Gavx]

Thanks to you both.

arnelgp, you nailed it.