Solved Using IIF and Between and Or conditions in Access query (1 Viewer)

[AnilBagga]

I am using the following expression in an Update query. The form referred to as a command button to the run the Query. However the criteria does not work. There are no syntax errors but it does not work

IIF([Forms]![frmUncoatedRateChange]![txtItem]="Paperbags",17,
IIF([Forms]![frmUncoatedRateChange]![txtItem]="Normalbags",(between 11 and 16) or 20,
IIF([Forms]![frmUncoatedRateChange]![txtItem]="Fabric", (between 1 and 10) or 18 or 19
)))

I have never used nested IIF statements in a query. Will appreciate some help

 

[arnelgp]

split you code and put to variable, since this is a VBA in your command button:

Dim criteria As String
Dim updateQuery As String
UpdateQuery = "Update [yourTable] Set [yourField] = blah"
Select Case [Forms]![frmUncoatedrateChange]![txtItem]
   Case "Paperbags"
      criteria = " WHERE [yourField] = 17"
   Case "Normalbags"
      criteria = " WHERE ([yourField] Between 11 And 16) OR ([yourfield] = 20)"
   Case "Fabric"
      criteria = " WHERE ([yourField] Between 1 And 10) OR ([yourField] Between 18 And 19)"
End Select
If Len(criteria) Then
    updateQuery = UpdateQuery & criteria
    Currentdb.Execute updateQuery
End If

 

[AnilBagga]

split you code and put to variable, since this is a VBA in your command button:

Dim criteria As String
Dim updateQuery As String
UpdateQuery = "Update [yourTable] Set [yourField] = blah"
Select Case [Forms]![frmUncoatedrateChange]![txtItem]
   Case "Paperbags"
      criteria = " WHERE [yourField] = 17"
   Case "Normalbags"
      criteria = " WHERE ([yourField] Between 11 And 16) OR ([yourfield] = 20)"
   Case "Fabric"
      criteria = " WHERE ([yourField] Between 1 And 10) OR ([yourField] Between 18 And 19)"
End Select
If Len(criteria) Then
    updateQuery = UpdateQuery & criteria
    Currentdb.Execute updateQuery
End If

Arnel,

This is the code for the command button. Correct? In this statement, UpdateQuery = "Update [yourTable] Set [yourField] = blah" what is [yourtable]. This is not used anywhere else . The form is unbound and query name is "qryConvUpdate"

What will be the criteria in the Query ?

I wanted to use the IIF criteria (stated before) for PricingGroupID, instead of Between 11 And 16 Or (tblItemMasterPricingSpecs.PricingGroupID)=20))

UPDATE (((tblCustRFQDtls LEFT JOIN tblCustRFQHdr ON tblCustRFQDtls.RFQHdrID = tblCustRFQHdr.RFQID) LEFT JOIN tblItemMasterPricingSpecs ON tblCustRFQDtls.FGCode = tblItemMasterPricingSpecs.FGCode) LEFT JOIN qryCustGSMID ON tblCustRFQDtls.RFQDtlsID = qryCustGSMID.RFQDtlsID) LEFT JOIN tblCustFabricConvRates ON (qryCustGSMID.EndCustomerCode = tblCustFabricConvRates.CustID) AND (qryCustGSMID.UncoatedGSMID = tblCustFabricConvRates.GSMID) SET tblCustRFQDtls.CorrectionItemVal = [Forms]![frmUncoatedRateChange]![txtTargetValue]-[tblCustRFQHdr].[CorrectionHdr]-[tblCustFabricConvRates].[UncoatedRate]
WHERE (((tblCustRFQHdr.EndCustomerCode)=[Forms]![frmUncoatedRateChange]![CboEndCustCode]) AND ((tblItemMasterPricingSpecs.PricingGroupID) Between 11 And 16 Or (tblItemMasterPricingSpecs.PricingGroupID)=20));

 

[arnelgp]

Dim criteria As String
Dim updateQuery As String
UpdateQuery = _
"UPDATE (((tblCustRFQDtls LEFT JOIN tblCustRFQHdr ON tblCustRFQDtls.RFQHdrID = tblCustRFQHdr.RFQID) " & _
"LEFT JOIN tblItemMasterPricingSpecs ON tblCustRFQDtls.FGCode = tblItemMasterPricingSpecs.FGCode) " & _
"LEFT JOIN qryCustGSMID ON tblCustRFQDtls.RFQDtlsID = qryCustGSMID.RFQDtlsID) " & _
"LEFT JOIN tblCustFabricConvRates " & _
"ON (qryCustGSMID.EndCustomerCode = tblCustFabricConvRates.CustID) AND " & _
"(qryCustGSMID.UncoatedGSMID = tblCustFabricConvRates.GSMID) " & _
"SET tblCustRFQDtls.CorrectionItemVal = [Forms]![frmUncoatedRateChange]![txtTargetValue]-[tblCustRFQHdr].[CorrectionHdr]-[tblCustFabricConvRates].[UncoatedRate] " & _
"WHERE ((tblCustRFQHdr.EndCustomerCode)=[Forms]![frmUncoatedRateChange]![CboEndCustCode])"
Select Case [Forms]![frmUncoatedrateChange]![txtItem]
   Case "Paperbags"
      criteria = " And ((tblItemMasterPricingSpecs.PricingGroupID) = 17);"
   Case "Normalbags"
      criteria = " And ((tblItemMasterPricingSpecs.PricingGroupID)  Between 11 And 16) OR ((tblItemMasterPricingSpecs.PricingGroupID = 20)"
   Case "Fabric"
      criteria = " And ((tblItemMasterPricingSpecs.PricingGroupID) Between 1 And 10) OR ((tblItemMasterPricingSpecs.PricingGroupID) Between 18 And 19)"
End Select
If Len(criteria) Then
    updateQuery = UpdateQuery & criteria
    Currentdb.Execute updateQuery
End If