Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Built-in Functions _ Help With Simple From Control Source

Posted by: Steven Conner Aug 30 2019, 02:44 AM

Hi Anybody ?

I seek help from anyone who is good at building formulae from expression builder in Form.

I have an problem whereby I wish to have a FORM to show me only the minimum quantity from the records of rows.

Example, I have 7 items that is 108 ( already calculated from queries) , I would like the FORM to only show me the values of "108" which defines the item in the fields.
I would like to know how to get the QTY = 7 as 108 has 7 item that has the same 108.

I do not want to see other value from 623 to 2058 as they are not the minimum value.

From the " Control Source " I wish to build and expression but keep returning #Name?.

Does anyone knows how I can solve. I try to use IFF , DCOUNT ,etc....with no success.

Attached is the picture for you understanding.

Thank you for looking.
Best Regards
Steven Conner

 

Posted by: June7 Aug 30 2019, 03:17 AM

ControlSource is the wrong property to use.

You need to apply filter criteria to form if you want to restrict records.

This will be difficult on form. Why do you need on form? Usually this type of filtering would be for output to a report.



Posted by: arnelgp Aug 30 2019, 05:01 AM

create another Query (qryMinCycles) based on the query where you get 108 available cycles:

select [part number I], min([available cycles] As MinCycle, count("*") As CountMinCycle
From firstQueryName
group by [part number I], [available cycles]
order by [part number I], [available cycles];

on Qty textbox on your subform:

=dlookup("CountMinCycle", "newQueryNameHere", "[part number I] = '" & [partnumber I] & "'")

on qty on Main form:

=DLookup("MinCycle", "newQueryNameHere", "[part number I] = '" & [partnumber I] & "'")

Posted by: Steven Conner Aug 30 2019, 08:56 AM

Thank You June7 and arnelgp,

This is for arnelgp

From your SQL statement , where does MinCycles and MinCycle come from or you mean MinAvialableCycles?

I intent to name my new query name as Final.

The fields name list I intent to use are Available Cycles, Part Number 1, Serial Number 1, etc....

Can you help me with the new SQL statement?

I am sorry if I ask dumb questions as I am very new to Access , SQL, VBA and these new terms which I am having a hard times trying to learnt.

Thank You
S Conner

Have a nice weekend......

Posted by: GroverParkGeorge Aug 30 2019, 09:18 AM

As is often the case, trying to extrapolate information from screenshots can be a bit difficult.

Can you provide some sample data to work on? Thanks.

Posted by: arnelgp Aug 30 2019, 12:35 PM

if you can post the SQL string of the query where you get 108,
then I, or somebody will create the query you need.

Posted by: Steven Conner Aug 30 2019, 02:17 PM

Thanks to all...

I will post the SQL statement of how I get
108 from the source data.

Kindly note that there are always 52 rows of data from the
queries and everyone month, when a new set of raw data is added, the
queries will recalculate and it only sort ascending from lowest to highest.

Example next data may get maybe minimum value of 56 whereby 6 rows have 56 out of 52 rows. or minimal 250 whereby qty is 18 rows has 250 minimum.

So I have gotten the queries format/ calculation all correct but I need
to made a report that shows only the lowest qty only. I do not need to show the other values even though the queries calculated and sort them out.

I will post the SQL for how I got 108 on Monday as now my office is closed.

Thanks to all for the help.
Cheers and have a nice weekend
S Conner.

Posted by: Steven Conner Sep 1 2019, 09:32 PM

Hi All

Here is my SQL statement from where I got my 108.

SELECT TOP 52 Sorted.[Available Cycles], Sorted.[Serial Number1], Sorted.[Reg], Sorted.[Component Description], Sorted.Position, Sorted.[Part Number], Sorted.[Serial Number], Sorted.[Date Installed], Sorted.[Part Description], Sorted.[Part Number1], Sorted.Position1, Sorted.TSN, Sorted.CSN, Sorted.[LL)], Sorted.Project

FROM Sorted

GROUP BY Sorted.[Available Cycles], Sorted.[Serial Number1], Sorted.[Reg], Sorted.[Component Description], Sorted.Position, Sorted.[Part Number], Sorted.[Serial Number], Sorted.[Date Installed], Sorted.[Part Description], Sorted.[Part Number1], Sorted.Position1, Sorted.TSN, Sorted.CSN, Sorted.[LL], Sorted.Project

HAVING (((Sorted.[Reg])="CSS") AND ((Sorted.[Component Description])="ENGINE") AND ((Sorted.Position)="1") AND ((Sorted.[Part Description]) In ("PIPE","CUTTER")))

ORDER BY Sorted.[Available Cycles], Sorted.[Serial Number1];

I hope it is not to complicated.

Thank you for viewing.
S Conner.

Best Regards

Posted by: arnelgp Sep 2 2019, 02:06 AM

same thing.

on Qty textbox on your main form:

=dCount("1", "queryNameFrom108", "[serial number1] = '" & [serial number1] & "' and [part number] ='" & [part number] & "'")

Posted by: Steven Conner Sep 2 2019, 03:50 AM

Thanks arnelgp

But I think I need the SQL for my queries name which you ask for few days ago.

Here is my SQL statement from where I got my 108. Below is the SQL form where I got my 108 , but there are sorted from min to max, and 108 happens to have 108 ( QTY 7 )
I need it qty 7 to be reflected in my report.

SELECT TOP 52 Sorted.[Available Cycles], Sorted.[Serial Number1], Sorted.[Reg], Sorted.[Component Description], Sorted.Position, Sorted.[Part Number], Sorted.[Serial Number], Sorted.[Date Installed], Sorted.[Part Description], Sorted.[Part Number1], Sorted.Position1, Sorted.TSN, Sorted.CSN, Sorted.[LL)], Sorted.Project

FROM Sorted

GROUP BY Sorted.[Available Cycles], Sorted.[Serial Number1], Sorted.[Reg], Sorted.[Component Description], Sorted.Position, Sorted.[Part Number], Sorted.[Serial Number], Sorted.[Date Installed], Sorted.[Part Description], Sorted.[Part Number1], Sorted.Position1, Sorted.TSN, Sorted.CSN, Sorted.[LL], Sorted.Project

HAVING (((Sorted.[Reg])="CSS") AND ((Sorted.[Component Description])="ENGINE") AND ((Sorted.Position)="1") AND ((Sorted.[Part Description]) In ("PIPE","CUTTER")))

ORDER BY Sorted.[Available Cycles], Sorted.[Serial Number1];


I hope it is not too complicated to ask , however i got some idea about your formulae , but I think I do not have the new queries "Hose"

Thanks you for your help however small it is.

Best Regards,
Steven Conner