help show list in report based on if condition through form

[eddii]

I have two tables imtemaster and calibrationcertificate i have joined based on imtenumber field of both tables

SQL VIEW

SELECT DISTINCTROW IMTEMASTER.IMTENUMBER, CALIBRATIONCERTIFICATE.IMTENUMBER, CALIBRATIONCERTIFICATE.CALDATE, CALIBRATIONCERTIFICATE.STATUSCER,
FROM IMTEMASTER LEFT JOIN CALIBRATIONCERTIFICATE ON IMTEMASTER.IMTENUMBER = CALIBRATIONCERTIFICATE.IMTENUMBER;

now i have a form in that i have a combo box " yearsel " in reportform if i select the year it should not show the list of in which
if field "caldate" year is less than the "yearsel" and "statuscer" contains "scrap" it should not show in the list. it should show other data

HOW DO I MATCH ONLY YEAR 2000,2001,2002.... ETC
TO THE CALDATE FIELD YEAR AND TAKE THE REPORT


how it can be done can any one help

 

[Trevor G]

You need the source of the report (your query) to get the field criteria to look at the form and the combo to use the selected date or greater than.

In your data the date field shows the dates in full, I would add a column and use a formula to show the year only and then you can use the drop down on the form to show the data in the report.

Revised SQL code for the query is shown below:

SELECT DISTINCTROW IMTEMASTER.[New No], IMTEMASTER.[Reg Date], IMTEMASTER.IMTENAME, IMTEMASTER.IMTENUMBER, IMTEMASTER.SHOP, IMTEMASTER.PLANT, IMTEMASTER.REMARKS, IMTEMASTER.DUMMY4, CALIBRATIONCERTIFICATE.ID, CALIBRATIONCERTIFICATE.IMTENUMBER, CALIBRATIONCERTIFICATE.CALDATE, Right([CALDATE],4) AS Expr1, CALIBRATIONCERTIFICATE.STATUSCER, CALIBRATIONCERTIFICATE.startdate, CALIBRATIONCERTIFICATE.enddate, IIf([STATUSCER]="ok",1,0) AS OK_Count, IIf([STATUSCER]="ng",1,0) AS ng_Count, IIf([STATUSCER]="pending",1,0) AS pending_Count, IIf([STATUSCER]="BREAK DOWN",1,0) AS [BREAK DOWN_Count], IIf([STATUSCER]="MISSING",1,0) AS MISSING_Count, IMTEMASTER.monthofcal, IIf(IsNull([statuscer]),1,0) AS blank_count, IIf([STATUSCER]="scrap",1,0) AS scrap_Count, IMTEMASTER.damar, IMTEMASTER.nuber, Month([mfgdate]) AS Mymonth, CALIBRATIONCERTIFICATE.NEXTCALDUEDATE, CALIBRATIONCERTIFICATE.REPNO, IMTEMASTER.mfgdate, Year([nextcalduedate]) AS Myyear
FROM IMTEMASTER LEFT JOIN CALIBRATIONCERTIFICATE ON IMTEMASTER.IMTENUMBER = CALIBRATIONCERTIFICATE.IMTENUMBER
GROUP BY IMTEMASTER.[New No], IMTEMASTER.[Reg Date], IMTEMASTER.IMTENAME, IMTEMASTER.IMTENUMBER, IMTEMASTER.SHOP, IMTEMASTER.PLANT, IMTEMASTER.REMARKS, IMTEMASTER.DUMMY4, CALIBRATIONCERTIFICATE.ID, CALIBRATIONCERTIFICATE.IMTENUMBER, CALIBRATIONCERTIFICATE.CALDATE, Right([CALDATE],4), CALIBRATIONCERTIFICATE.STATUSCER, CALIBRATIONCERTIFICATE.startdate, CALIBRATIONCERTIFICATE.enddate, IIf([STATUSCER]="ok",1,0), IIf([STATUSCER]="ng",1,0), IIf([STATUSCER]="pending",1,0), IIf([STATUSCER]="BREAK DOWN",1,0), IIf([STATUSCER]="MISSING",1,0), IMTEMASTER.monthofcal, IIf(IsNull([statuscer]),1,0), IIf([STATUSCER]="scrap",1,0), IMTEMASTER.damar, IMTEMASTER.nuber, Month([mfgdate]), CALIBRATIONCERTIFICATE.NEXTCALDUEDATE, CALIBRATIONCERTIFICATE.REPNO, IMTEMASTER.mfgdate, Year([nextcalduedate])
HAVING (((Right([CALDATE],4))>[Forms]![REPORTFORM]![mfgdate]));

 

[eddii]

thanks for the reply

its showing the data as per the query but i have one more problem that is

the date should be the selected from form " mfgdate "

and "STATUSCER" field should not contain scrap as field value

like if date is >>[Forms]![REPORTFORM]![mfgdate] and statuscer should not be equal to "scrap"

how do i put this condition

can you tell me

 

[Trevor G]

In the query you would place in <>Scrap in the filed criteria.

Here is the revised SQL which you can copy and replace into your query.

SELECT DISTINCTROW IMTEMASTER.[New No], IMTEMASTER.[Reg Date], IMTEMASTER.IMTENAME, IMTEMASTER.IMTENUMBER, IMTEMASTER.SHOP, IMTEMASTER.PLANT, IMTEMASTER.REMARKS, IMTEMASTER.DUMMY4, CALIBRATIONCERTIFICATE.ID, CALIBRATIONCERTIFICATE.IMTENUMBER, CALIBRATIONCERTIFICATE.CALDATE, Right([CALDATE],4) AS Expr1, CALIBRATIONCERTIFICATE.STATUSCER, CALIBRATIONCERTIFICATE.startdate, CALIBRATIONCERTIFICATE.enddate, IIf([STATUSCER]="ok",1,0) AS OK_Count, IIf([STATUSCER]="ng",1,0) AS ng_Count, IIf([STATUSCER]="pending",1,0) AS pending_Count, IIf([STATUSCER]="BREAK DOWN",1,0) AS [BREAK DOWN_Count], IIf([STATUSCER]="MISSING",1,0) AS MISSING_Count, IMTEMASTER.monthofcal, IIf(IsNull([statuscer]),1,0) AS blank_count, IIf([STATUSCER]="scrap",1,0) AS scrap_Count, IMTEMASTER.damar, IMTEMASTER.nuber, Month([mfgdate]) AS Mymonth, CALIBRATIONCERTIFICATE.NEXTCALDUEDATE, CALIBRATIONCERTIFICATE.REPNO, IMTEMASTER.mfgdate, Year([nextcalduedate]) AS Myyear
FROM IMTEMASTER LEFT JOIN CALIBRATIONCERTIFICATE ON IMTEMASTER.IMTENUMBER = CALIBRATIONCERTIFICATE.IMTENUMBER
GROUP BY IMTEMASTER.[New No], IMTEMASTER.[Reg Date], IMTEMASTER.IMTENAME, IMTEMASTER.IMTENUMBER, IMTEMASTER.SHOP, IMTEMASTER.PLANT, IMTEMASTER.REMARKS, IMTEMASTER.DUMMY4, CALIBRATIONCERTIFICATE.ID, CALIBRATIONCERTIFICATE.IMTENUMBER, CALIBRATIONCERTIFICATE.CALDATE, Right([CALDATE],4), CALIBRATIONCERTIFICATE.STATUSCER, CALIBRATIONCERTIFICATE.startdate, CALIBRATIONCERTIFICATE.enddate, IIf([STATUSCER]="ok",1,0), IIf([STATUSCER]="ng",1,0), IIf([STATUSCER]="pending",1,0), IIf([STATUSCER]="BREAK DOWN",1,0), IIf([STATUSCER]="MISSING",1,0), IMTEMASTER.monthofcal, IIf(IsNull([statuscer]),1,0), IIf([STATUSCER]="scrap",1,0), IMTEMASTER.damar, IMTEMASTER.nuber, Month([mfgdate]), CALIBRATIONCERTIFICATE.NEXTCALDUEDATE, CALIBRATIONCERTIFICATE.REPNO, IMTEMASTER.mfgdate, Year([nextcalduedate])
HAVING (((Right([CALDATE],4))>[Forms]![REPORTFORM]![mfgdate]) AND ((CALIBRATIONCERTIFICATE.STATUSCER)<>"Scrap"));

 

[eddii]

Trevor G thanks for the reply

thanks for the reply the problem is

if the mfgdate =2009 and statuscer = scrap

then it should come in list

when we enter 2009 since it shows the list where it is "OK" or "NOT GOOD" or "scrap"

but the same should not come when the mfgdate = 2010

because it would have been scraped by that year

a small change in query
>[Forms]![REPORTFORM]![mfgdate])

it should be <=[Forms]![REPORTFORM]![mfgdate])

SELECT DISTINCTROW IMTEMASTER.[New No], IMTEMASTER.[Reg Date], IMTEMASTER.IMTENAME, IMTEMASTER.IMTENUMBER, IMTEMASTER.SHOP, IMTEMASTER.PLANT, IMTEMASTER.REMARKS, IMTEMASTER.DUMMY4, CALIBRATIONCERTIFICATE.ID, CALIBRATIONCERTIFICATE.IMTENUMBER, CALIBRATIONCERTIFICATE.CALDATE, Right([CALDATE],4) AS Expr1, CALIBRATIONCERTIFICATE.STATUSCER, CALIBRATIONCERTIFICATE.startdate, CALIBRATIONCERTIFICATE.enddate, IIf([STATUSCER]="ok",1,0) AS OK_Count, IIf([STATUSCER]="ng",1,0) AS ng_Count, IIf([STATUSCER]="pending",1,0) AS pending_Count, IIf([STATUSCER]="BREAK DOWN",1,0) AS [BREAK DOWN_Count], IIf([STATUSCER]="MISSING",1,0) AS MISSING_Count, IMTEMASTER.monthofcal, IIf(IsNull([statuscer]),1,0) AS blank_count, IIf([STATUSCER]="scrap",1,0) AS scrap_Count, IMTEMASTER.damar, IMTEMASTER.nuber, Month([mfgdate]) AS Mymonth, CALIBRATIONCERTIFICATE.NEXTCALDUEDATE, CALIBRATIONCERTIFICATE.REPNO, IMTEMASTER.mfgdate, Year([nextcalduedate]) AS Myyear
FROM IMTEMASTER LEFT JOIN CALIBRATIONCERTIFICATE ON IMTEMASTER.IMTENUMBER = CALIBRATIONCERTIFICATE.IMTENUMBER
GROUP BY IMTEMASTER.[New No], IMTEMASTER.[Reg Date], IMTEMASTER.IMTENAME, IMTEMASTER.IMTENUMBER, IMTEMASTER.SHOP, IMTEMASTER.PLANT, IMTEMASTER.REMARKS, IMTEMASTER.DUMMY4, CALIBRATIONCERTIFICATE.ID, CALIBRATIONCERTIFICATE.IMTENUMBER, CALIBRATIONCERTIFICATE.CALDATE, Right([CALDATE],4), CALIBRATIONCERTIFICATE.STATUSCER, CALIBRATIONCERTIFICATE.startdate, CALIBRATIONCERTIFICATE.enddate, IIf([STATUSCER]="ok",1,0), IIf([STATUSCER]="ng",1,0), IIf([STATUSCER]="pending",1,0), IIf([STATUSCER]="BREAK DOWN",1,0), IIf([STATUSCER]="MISSING",1,0), IMTEMASTER.monthofcal, IIf(IsNull([statuscer]),1,0), IIf([STATUSCER]="scrap",1,0), IMTEMASTER.damar, IMTEMASTER.nuber, Month([mfgdate]), CALIBRATIONCERTIFICATE.NEXTCALDUEDATE, CALIBRATIONCERTIFICATE.REPNO, IMTEMASTER.mfgdate, Year([nextcalduedate])
HAVING (((Right([CALDATE],4))<=[Forms]![REPORTFORM]![mfgdate]) AND ((CALIBRATIONCERTIFICATE.STATUSCER)<>"Scrap"));

bit confusing i think

 

[Trevor G]

Add a text box onto your form and get the query to look at the contents so you can get the source of the report to check both the combo box and text box that would work easy enough.

So it isn't that confusing.

 

[eddii]

thanks for the reply you are right

thanks for the reply you are right but i cannot put the text box to take the data because the user does not know this so he has to only select through year

that means if the user is selecting year 2010
what ever are scraped before 2010 should not appear in list
but items scraped in 2010 should appear in list
that is the problem
if it is possible to do in a query its ok

or else we need to use vba code

if else condition
1st condition )
if caldate = mfgdate and statuscer = scrap then show in list

2nd condition ) and if caldate > mfgdate and statuscer = scrap then dont show in list

this should be then code iam not able to merge both conditions