Changing the criteria of a query depending on a form (1 Viewer)

morrishelo

New member
Local time
Today, 19:09
Joined
Mar 13, 2018
Messages
4
I have a query that is using a form named "DateRange2" to change the criteria of one field "Dept1" in order to filter this query. Then the I designed a report that can be opened by using a command button 'cmdOpenReport' with form VBA code:

Option Compare DatabasePrivateSub Form_Open(Cancel AsInteger)
Me.Caption =Me.OpenArgs
EndSub
PrivateSub cmdOpenReport_Click()

If IsNull([SrchDateFrom])Or IsNull([SrchDateTo])Then

MsgBox "Please enter your date range.", _
vbInformation + vbOKOnly,"No Date Range!"
DoCmd.GoToControl "SrchDateFrom"
Else
If [SrchDateFrom] > [SrchDateTo] Then
MsgBox "Date 'To' Value is earlier that Date 'From', please enter logic date range.", _
vbInformation + vbOKOnly,"Illogic Date Range!"
DoCmd.GoToControl "SrchDateFrom"
Else
Me.Visible =False

EndIf
EndIf
EndSub

And My question is: How to to change the criteria of a query depending on a form? to show only the entries of the selected department; however, in case of NO department is selected, then I want the report to show all the entries!
Adding that; when I manually set the criteria to: Like [Forms]![DateRange2]![SrchDept] & "*", then it shows all the entries. But alternatively when I set the criteria to: [Forms]![DateRange2]![SrchDept], then it shows the selected department only. However, this doesn't solve my problem because I actually want to shift between the two cases by using the form named "DateRange2".
Please help me in a very simple way as I am just a beginner.

Also I was trying something like:
Expr1: IIf(IsNull([Forms]![DateRange2]![SrchDept]), [Correspondence]![Dept1] = Like ([Forms]![DateRange2]![SrchDept]) & "*",[Correspondence]![Dept1] =([Forms]![DateRange2]![SrchDept])),
but this doesn't work with me!

Please help me!
 

morrishelo

New member
Local time
Today, 19:09
Joined
Mar 13, 2018
Messages
4
I'd like to thank you all for this effective forum; I was able to find the solution after going thru similar inquiries, also I found the discussion between @whitty15 and @EMP very helpful to me. Therefore, I'd like to share the answer with you all to share the knowledge.

The query expression that works was:

Field: IIf([Forms]![DateRange2]![SrchDept]="",[Dept1] Is Null,IIf([Forms]![DateRange2]![SrchDept]<>"",[Dept1]=[Forms]![DateRange2]![SrchDept],True))

SHOW: unchecked
CRITERIA: True

Regards,
 

Minty

AWF VIP
Local time
Today, 16:09
Joined
Jul 26, 2013
Messages
10,354
Another method - In your criteria simply put

=[Forms]![DateRange2]![SrchDept] or [Forms]![DateRange2]![SrchDept] is Null
 

morrishelo

New member
Local time
Today, 19:09
Joined
Mar 13, 2018
Messages
4
Thanks @Minty for your review and your kind response, I will try this so.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:09
Joined
Sep 12, 2006
Messages
15,613
I was going to add a comment, but then I had a visit from BT, so some time has elapsed ...

I often make queries fully non-form dependent by adding a function.(often just to read a variable).

so instread of a criteria looking like forms!formname!fieldname
instead the criteria looks like functionname()

any form can then do whatever is needed to get the funvtion to evaluate correctly.

Since tempvars came along, you can do the same thing with those. I just haven't got into the habit.
 

morrishelo

New member
Local time
Today, 19:09
Joined
Mar 13, 2018
Messages
4
Thank u Dave, I think I need to learn more about tempvar
Thank u for your response.
Regards,
 

Users who are viewing this thread

Top Bottom