UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Refer To Field On One Form As Parameter For A Query That Runs A Report, Access 2010    
 
   
Brandi
post Feb 14 2018, 11:36 AM
Post#1



Posts: 1,635
Joined: 24-June 04



I have a selection form (frmSelect) where users filter employees based on Division, Department, Location, and Position.
On frmSelect, I have an unbound control named DivSelected which displays the selected items in a listbox named SelectDivision.Example: There are 10 Divisions listed here. I can select 2 of them and then field DivSelected displays 'DivisionB', 'DivisionG' exactly like that with a single quote around each Division and commas in between.

I am trying to use Forms!frmSelect.DivSelected as criteria in a query named qryCompaByDivision which prints a chart.
When I open the chart report, I get no Divisions rather than the Divisions that are listed in my field called DivSelected on frmSelect.

In my report form (frmSelectReports) where I am opening my chart report, I am able to display DivSelected from frmSelect=[Forms]![frmSelect].[DivSelected]

I am trying to use this as the criteria for the query that is the source for the chart report.

I wasn't able to refer to the original form frmSelect to grab the criteria, but can at least display the criteria on my report form.

1. Should I be able to use the values in the DivSelected field as criteria for my Chart?
2. Should I be able to refer to the original form to grab that or at least the report form itself to grab thos selected Divisions?

Thank you.
Brandi

Go to the top of the page
 
theDBguy
post Feb 14 2018, 11:50 AM
Post#2


Access Wiki and Forums Moderator
Posts: 71,564
Joined: 19-June 07
From: SunnySandyEggo


Hi Brandi,

How exactly are you trying to use the values in the form as a criteria? Were you trying to use an In() clause?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Brandi
post Feb 14 2018, 01:31 PM
Post#3



Posts: 1,635
Joined: 24-June 04



I would actually like to use and In clause. You helped me earlier to use and In clause to select values in one of my listboxes based on a selection in another dropdown box.

This is sort of similar except someone is manually selecting values in a listbox named SelectDivision for example.
And then I want to be able to use the values that they selected as the criteria for a query which prints a Chart.

When I manually typed criteria into my query In('DivisionC','DivisionM') it does pull the correct records. I just need to figure out how to get the query to add the In( and the values in my field on my report form which has a control source of =[Forms]![frmSelect].[DivSelected] So this control has the 'DivisionC', 'DivisionM' in it. This control is named DivisionCriteria

I tried referring to it in the query by putting in the criteria under Division but I get no result.

"In( " & "[DivisionCriteria]" & ")"

Thank you.
Brandi
This post has been edited by Brandi: Feb 14 2018, 01:32 PM
Go to the top of the page
 
theDBguy
post Feb 14 2018, 03:08 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,564
Joined: 19-June 07
From: SunnySandyEggo


For this case, you might be able to use a Like operator. For example, add a new column to your query as follows:


Field: Forms!frmSelect.DivSelected
Criteria: Like "*" & [Division] & "*"


Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Brandi
post Feb 14 2018, 10:14 PM
Post#5



Posts: 1,635
Joined: 24-June 04



Well I thank you for your help. I am Utterly confused!
I am attaching a sample. I'm sure I have way too many things going on and this is not the best design but I am getting errors that "fields" are not recognized and not sure what a field is anymore.

If you open the sample and select a Division from the listbox for Division, you will notice a field above it populate with the selected Division(s).
That is all good.

Now, if you click the Reports button on top right, the Reports form will open. Most of it is not currently working.
The red button for Compa Ratio By Division is the report I am trying to get to work. I am showing the selected values for Division just above the red button and those are coming from the previous form.

But when I try to run the report, the underlying query says it doesn't recognize a field called SelectDivision which I believe is the field above the red button and the field I would like to be the criteria for my Report.
If possible, I'm sure it would be cleaner to just refer to the first form for the selected Divisions, but I couldn't get that to work, so included it on the Reports form just to verify that it can actually refer to the field on the original form.

The first form (frmSelect) has a listbox named SelectDivision. It also has a field called DivSelected which displays a string of the items selected from SelectDivision.

Ultimately, I would like my Compa Ratio By Division report to use the selected Divisions as criteria.

I hope I am making sense. It's late and I am a bit frazzled.

Thank you again for the many times you have helped me.
Brandi


Attached File(s)
Attached File  MeritTest3.zip ( 684.6K )Number of downloads: 1
 
Go to the top of the page
 
Brandi
post Feb 15 2018, 10:56 AM
Post#6



Posts: 1,635
Joined: 24-June 04



After sleeping on this, I decided that qryEmployeeSelect which you helped me with earlier in this project should really be the source for my reports.
This morning I tried a few things and I think I have it working now the way I would expect. Next I'll try to get the other reports working based on qryEmployeeSelect.

Thanks a million.
Brandi
Go to the top of the page
 
theDBguy
post Feb 15 2018, 11:01 AM
Post#7


Access Wiki and Forums Moderator
Posts: 71,564
Joined: 19-June 07
From: SunnySandyEggo


Hi Brandi,

Glad to hear you found another way to work out the issue. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st February 2018 - 04:18 AM