Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    66

    Unanswered: help show list in query based on if condition through form

    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

    how it can be done can any one help
    Attached Files Attached Files
    Last edited by eddi; 11-14-11 at 04:41. Reason: change in attachment

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    I changed your SQL to
    Code:
    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, 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
    WHERE (((IIf(DatePart("yyyy",[CALDATE])=[Forms]![REPORTFORM]![mfgdate],"",DatePart("yyyy",[CALDATE])))<>""));
    Attached Files Attached Files

  3. #3
    Join Date
    Sep 2011
    Posts
    66

    thanks for the help and reply

    thanks for the help and reply

    i have got a problem that is

    if mfgdate=caldate and field "statuscer" = scrap then it should get the list
    if mfgdate<caldate and field "statuscer" = scrap then it should not get in the list

    so if it can be done in query its good or
    else it should be done i vba code
    i am thinking how put two conditions and run it

    iam confused of it

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    This should do it
    Attached Files Attached Files

  5. #5
    Join Date
    Sep 2011
    Posts
    66

    thanks for the reply

    thanks for the reply pal
    sorry for the delay as i was out of work

    the attached database the scrap is not displayed but

    i want it to display if the condition is true

    if mfgdate=caldate and field "statuscer" = scrap then it should get the list
    if mfgdate<caldate and field "statuscer" = scrap then it should not get in the list

    i think this is not possible by a query may

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •