Access Script & Form
----------------------------------------------------------------------------
Union Query Script
SELECT * FROM [Table_NORTH SALES]
UNION ALL SELECT * FROM [Table_SOUTH SALES];
 
----------------------------------------------------------------------------
Concatenate
FullName: [FirstName] &" "[MiddleInitial]" "&" "&[LastName]  
FullName: [FirstName] &(" "+[MiddleInitial]+".")&" "&[LastName]   excludes middle if null
 
----------------------------------------------------------------------------
 
IFF Function (total Units, if null then 0, else calc)
Total Units: =IIf(IsNull([Receiving_qry With and  Without Matching Invoice_qry]![SumOfUnits]),0,+[Receiving_qry With and  Without Matching Invoice_qry]![SumOfUnits])+iff(IsNull([Receiving_qry With and  Without Matching Invoice_qry]![SumOfShp Qty]),0,+[Receiving_qry With and  Without Matching Invoice_qry]![SumOfShp Qty])
 
----------------------------------------------------------------------------
Date Criteria Format:
>=#2/2/2012# and <#2/4/2012#
 
----------------------------------------------------------------------------
Code for Striping AlphaNumeric string to show Alpha only:
Use as function eg: Your_Variable_Name = CharsOnly([String_Name_Where_You_Want_The_Numbers_Removed])
EG2: JnlType: CharsOnly([Qry_11142_All]![Receipt Voucher])
 
Function CharsOnly(varConvert As Variant) As String
'Returns only the characters from a passed string.
 
    Dim curChar As String
    Dim ctr As Integer
 
    If IsNull(varConvert) Then
        CharsOnly = ""
        Exit Function
    End If
 
    For ctr = 1 To Len(varConvert)
        curChar = Mid(varConvert, ctr, 1)
        If Not(IsNumeric(curChar)) Then
            CharsOnly = CharsOnly & curChar
        End If
    Next
 
End Function
----------------------------------------------------------------------------
Code for Striping AlphaNumeric string to show Numeric only:
Use as function eg: Your_Variable_Name = RevmoveAlphas([String_Name_Where_You_Want_The_Numbers_Removed])
 
Function RemoveAlphas (ByVal AlphaNum as Variant)
 
Dim Clean As String
Dim Pos, A_Char$
 
Pos = 1
If IsNull(AlphaNum) Then Exit Function
 
For Pos = 1 To Len(AlphaNum)
A_Char$ = Mid(AlphaNum, Pos, 1)
If A_Char$ >= "0" And A_Char$ <= "9" Then
Clean$ = Clean$ + A_Char$
End If
Next Pos
 
RemoveAlphas = Clean$
 
End Function
 
---------------------------------------------------------------------------
5/25/16
Code for Selecting Left 10 characters of a data cell:
Key: Left([qry_Weekly PO]![Cust Po Num],10)
 
---------------------------------------------------------------------------

2/12/18
IIf AND statement in Access
TRY:
IIf(([TranType]="PX") And ([Market]<>"NAT") And ([Derived Type]="PA"),1,0)
 
--------------------------------------------------------------------------
9/20/18
Countif equivalent: Build EG:
ValidNbrSalesOrder: IIf([Mkt_30_SalesOrders]![SalesOrderType]="Valid_SO",1,0)
EG:
=IIF([SafetyGlasses]='Always';1;0) + IIF([HardHats]='Always';1;0 .....
 
---------------------------------------------------------------------------
9/20/18
Search for word in string use InStr function 
eg:  Find the position of a character in a string InStr function
=InStr(1,[FirstName],"i")
If [FirstName] is “Colin”, the result is 4.
 
Example:
Group2: IIf(InStr(1,[Mkt_21_TrackingNbr_Summary]![Charge Description],"Ground")<>0,"Ground","NotGround")
 
Example2:
Group2: IIf(InStr(1,[Mkt_21_TrackingNbr_Summary]![Charge Description],"Ground")<>0,"Ground",IIf(InStr(1,[Mkt_21_TrackingNbr_Summary]![Charge Description],"Air")<>0,"Air",IIf(InStr(1,[Mkt_21_TrackingNbr_Summary]![Charge Description],"Fuel")<>0,"FSC",IIf(InStr(1,[Mkt_21_TrackingNbr_Summary]![Charge Description],"Delivery Area")<>0,"Delivery Area Surcharge",IIf(InStr(1,[Mkt_21_TrackingNbr_Summary]![Charge Description],"Residential")<>0,"Residential Surcharge","Other")))))
 
Example3:
Group2: IIf(InStr(1,[Mkt_20_TrackingNbr]![Charge Description],"Ground")<>0,"Ground",IIf(InStr(1,[Mkt_20_TrackingNbr]![Charge Description],"Air")<>0,"Air",IIf(InStr(1,[Mkt_20_TrackingNbr]![Charge Description],"Fuel")<>0,"FSC",IIf(InStr(1,[Mkt_20_TrackingNbr]![Charge Description],"Delivery Area")<>0,"Delivery Area Surcharge",IIf(InStr(1,[Mkt_20_TrackingNbr]![Charge Description],"Residential")<>0,"Residential Surcharge","Other")))))
 
-------------------------------------------------------------------------------