Results 1 to 7 of 7
  1. #1
    lmeser61 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    3

    Cannot figure out what I am missing in my IIF statement

    I have a table of wire gauges ranging from 10AWG up to 20+22AWG and crimp numbers associated with each gauge (some crimp numbers have multiple gauges.) What I am trying to accomplish is to evaluate the gauge based on the crimp number to see if the actual crimp height is equal to or between the lo and hi values given in the table. I want to update a form when a person enters in the actual crimp height, it evaluates it against the table standard and allows them to continue because it meets standards or stop and go no further because it doesn't meet the standard.



    Here is what I have so far:

    Select CrimpItemQry.[Crimp Number]
    From CrimpItemQry
    WHERE (IIf(Eval(,[CrimpItemQry].[Gauge] Between [CrimpItemQry].[Lo] And [CrimpItemQry].[Hi]),"OK","Stop! Please see supervisor."))

    Any help would be much appreciated.

    This is a sample of the data in the table:

    CrimpItemQry
    Crimp # Gauge AWG Standard N/A Lo Hi
    208-103 16 0.059" ± 0.002" 0.0570 0.0610
    208-103 18 0.049" ± 0.002" 0.0470 0.0510
    208-103 20 0.045" ± 0.002" 0.0430 0.0470
    208-103 20+20 0.053" ± 0.002" 0.0510 0.0550
    208-103 22 0.043" ± 0.002" 0.0410 0.0450
    208-103 22+18 0.053" ± 0.002" 0.0510 0.0550
    208-103 22+20 0.049" ± 0.002" 0.0470 0.0510
    208-103 22+22 0.047" ± 0.002" 0.0450 0.0450
    Last edited by lmeser61; 11-07-2018 at 09:04 AM. Reason: More information to go on

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the IF does not belong in the query.
    your if should be outside in code,

    Code:
    if Dcount("*","qsQuery")>0 then
        msgbox "Stop,See super"
    else
      docmd.openquery "qsQuery"
    endif

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Let's step back and think about this. I would think you would have two tables: a reference or look up table and a table of actual crimps.

    The reference/look up table would have fields
    tblWireGauges
    =============
    WireGaugeID_PK (autonumber)
    Gauge (Text)
    AWGStandard (Text)
    CrimpHeightLo (Double)
    CrimpHeightHi (Double)


    tblCrimps
    ===================
    CrimpID_PK (Autonumber)
    CrimpNum (Text)
    Gauge (Text)
    CrimpHeight (Double)


    I would write a UDF to determine if the crimp height is within tolerances.
    The form record source is a query
    Code:
    SELECT CrimpNum, Gauge, CrimpHeight, fnInTolerance(Gauge, CrimpHeight) as Tolerance
    FROM tblCrimps
    (fnInTolerance is the name of the UDF)

    I would set the form to continuous forms view. 4 text boxes would display the data.



    Do you have a dB already?

  4. #4
    lmeser61 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    3

    ssanfu

    Quote Originally Posted by ssanfu View Post
    Let's step back and think about this. I would think you would have two tables: a reference or look up table and a table of actual crimps.

    The reference/look up table would have fields
    tblWireGauges
    =============
    WireGaugeID_PK (autonumber)
    Gauge (Text)
    AWGStandard (Text)
    CrimpHeightLo (Double)
    CrimpHeightHi (Double)


    tblCrimps
    ===================
    CrimpID_PK (Autonumber)
    CrimpNum (Text)
    Gauge (Text)
    CrimpHeight (Double)


    I would write a UDF to determine if the crimp height is within tolerances.
    The form record source is a query
    Code:
    SELECT CrimpNum, Gauge, CrimpHeight, fnInTolerance(Gauge, CrimpHeight) as Tolerance
    FROM tblCrimps
    (fnInTolerance is the name of the UDF)

    I would set the form to continuous forms view. 4 text boxes would display the data.



    Do you have a dB already?
    Yes, I do already have the dB. Thank you for your help. I'm going to try what you suggested and see if that works. I'll post later to let you know.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Actually, I was asking if you have already created a dB. If you posted your dB, it would be easier to give a better solution because we could use what you already have instead of guessing what you have for table structures.


  6. #6
    lmeser61 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    3

    Eval of field value not working properly

    So things have changed.
    I have entered the following statement in the ActCrHgt textbox after update: =Eval((IIf([ActCrHgt]>=[CrHgtLo] And [ActCrHgt]<=[CrHgtHi]),[ActCrHgt]=[ActCrHgt],MsgBox("STOP! Do Not Proceed; Contact Supervisor")))

    The false statement works, however the true statement is not working. Any suggestions?

    (tried uploading Db but it kept giving a failed message.
    Last edited by lmeser61; 11-21-2018 at 11:11 AM. Reason: adding DB

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by lmeser61 View Post
    (tried uploading Db but it kept giving a failed message.
    Try doing a "Compact & Repair", then compressing (Zip) before attaching it to a post.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Can't figure out to to get what I want
    By Dave14867 in forum Forms
    Replies: 16
    Last Post: 10-26-2018, 03:11 PM
  2. Replies: 4
    Last Post: 05-26-2014, 03:57 PM
  3. Replies: 13
    Last Post: 04-17-2013, 04:17 PM
  4. Cant Figure This Out
    By tabbycat1234 in forum Forms
    Replies: 12
    Last Post: 07-27-2011, 02:19 AM
  5. Can't Figure It Out!!
    By jdohio5 in forum Database Design
    Replies: 1
    Last Post: 05-04-2006, 06:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums