[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Correct syntax for report

I have a report with a solution from EE..... which is working great (Thanks!)

In the working report I have a text box with the control source as:

=[CountOfFrameModel]/DCount("FrameModel","tmain","frameLine='" & [FrameLine] & "'and tMain.FrameOWF = False and tMain.Status <> 'Cancelled'")


I would also like to add anothe criteria based on a form selection but can't find the syntax.... The form is [Form]![fReportSelect]![LocSelect]

The only catch is that there are 3 locations possible and "ALL"

So while I would like to add the "locseelct" to the text box's control source but also keep as it is above when "All" is selected from the locselect on the form.

Is this possible?  If I could get the syntax correct I thought perhaps of using an IF statement in the control box.
0
thandel
Asked:
thandel
  • 3
1 Solution
 
mbizupCommented:
Try this:

=[CountOfFrameModel]/DCount("FrameModel","tmain","frameLine='" & [FrameLine] & "'and tMain.FrameOWF = False and tMain.Status <> 'Cancelled' AND locseelct LIKE '" & iif("" & [Form]![fReportSelect]![LocSelect] = "All", "*", "" & [Form]![fReportSelect]![LocSelect]) & "'")

Open in new window

0
 
mbizupCommented:
Hmm... I copied the field name "locseelct" directly from your original post.

It looks like a typo to me, though.  If you have issues with what I posted, try using "locselect" instead of "locseelct"

=[CountOfFrameModel]/DCount("FrameModel","tmain","frameLine='" & [FrameLine] & "'and tMain.FrameOWF = False and tMain.Status <> 'Cancelled' AND locselect LIKE '" & iif("" & [Form]![fReportSelect]![LocSelect] = "All", "*", "" & [Form]![fReportSelect]![LocSelect]) & "'")

Open in new window

0
 
thandelAuthor Commented:
Thanks minor correction and added tMain.office but

=[CountOfFrameModel]/DCount("FrameModel","tmain","frameLine='" & [FrameLine] & "'and tMain.FrameOWF = False and tMain.Status <> 'Cancelled' AND  tMain.Office LIKE '" & IIf("" & Form!fReportSelect!LocSelect="All","*","" & Form!fReportSelect!LocSelect) & "'")

Is prompting for "Form".  I think Form!fReportSelect!LocSelect needs to be [Form]![fReportSelect]![LocSelect]
0
 
mbizupCommented:
Ooops ... actually it needs to be Forms!   (with an s):


=[CountOfFrameModel]/DCount("FrameModel","tmain","frameLine='" & [FrameLine] & "'and tMain.FrameOWF = False and tMain.Status <> 'Cancelled' AND  tMain.Office LIKE '" & IIf("" & Forms!fReportSelect!LocSelect="All","*","" & Forms!fReportSelect!LocSelect) & "'")

Open in new window

0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now