Home

Aggregate Functions -Comparison Operators - Date Functions - Domain Aggregate Functions -Field TypesFormatting Functions - Logic Operators - Mathematical Operators -   SwitchBoard Items -Visual Basic and Variables - Wildcard CharactersActiveX Controls

Using Comparison Operators, Logic Operators and Wildcard Characters in Access.

Operator Operator Meaning Example Example Meaning

Comparison Operators

= Equals =smith or ="smith" Equals smith
> Greater Than >5000 Greater than 5000
< Less Than <1/1/99 or <#1/1/99# Earlier than January 1, 1999
>= Greater tahn or equal to >=m or >="m" Greater thatn or equal to the letter M
<= Less than or equal to <=12/31/98 or <=#12/31/98# earlier thanor equal to december 31, 1998
<> Not equal to  <>CA or <> "CA" Does not equal CA
Between Between two values (inclusive) Between 15 and 25 A number from 15 to 25
In Within a set or list of values IN("NY","AZ",NJ) NY, AZ or NJ
Is Null Field is empty Is null Records that have no value in this field
Is Not Null Field is not empty Is not null Records that do have a value in this field
Like Matches a pattern Like "MO*" Records that start with MO followed by lany other characters.

Logical Operators can be used on either Boolean (yes/no) data or numeric data.  When they are used with numeric data, they perform bitwise logical operations.                                              Top

AND Logical Conjunction of two expressions Both are true >=1 and <=10 Between 1 and 10, inclusive
Operand1 Operand2 Result  
True True True  
True False False  
True Null Null  
False True False  
False Null False  
Null True Null  
Null Null Null TOP
OR - Logical disjunction of two expressions If One or the other is true result is true UT or AZ or MI  Either UT or AZ or MI
Operand1 Operand2 Result  
True True True  
True  False True  
True Null True  
False True True  
False False False  
False  Null Null  
Null True True  
Null False Null  
Null Null Null TOP
NOT Logical opposite of an expression Not true Not Like MO* Records that don't start with MO
Operand Result    
True False    
False True    
Null Null    
XOR Logical exclusive or of two expressions TOP
Operand1 Operand2 Result  
True True False  
True False True  
True Null Null  
False True True  
False False False  
False Null Null  
Null True Null  
Null False Null  
Null Null Null  
EQV - Logical equivalence of two expressions TOP
Operand1 Operand2 Results
True True True
True False False
True Null Null
False True False
False False True
False Null Null
Null True Null
Null False Null
Null Null Null
IMP - Logical Implication of two experessions  TOP
Operand1 Operand2 Result
True True True
True False False
True Null Null
False True False
False False True
False Null True
Null True True
Null False Null
Null Null Null

Wildcard Characters                                    Top

? Any single character P? Values that start withP followed by any single character
* Any characters (619) Any text that starts with (619)
[field name] Some other field in the QBE grid <[UnitPrice] Records where this field value is less that the value in the UnitPrice field

                                                                               Top
Using Date Functions in the Query Criteria Line in Access 

Example Result
Date() The current date
<=Date() The current date and all dates before the current date.
>=Date() The current date and all dates after the current date.
<=Date()-30 Dates earlier than or equal to 30 days ago
Between Date() and Date()-30 Dates within the last 30 days
Between Date() and Date()+30 Date within the next 30 days
Between Date()-60 and Date()-30 Dates between 30 and 60 days ago
>DateAdd("m",2,Date()) Dates that are greater than 1 month ("m") from the current date
Between DateAdd("m",-2,Date()) and Date() Dates between two months ago and the current date
Between DateAdd("m",2,Date()) and Date() Dates between the current date and two months from now.
<DateAdd("yyyy",-1,Date()) Dates that are earlier than 1 year ("yyyy") ago.

                                                                                Top
Mathematical Operators
in precedence order

( ) Do what is inside first  
^ Exponent
- Unary - negative number
+ Unary - positive number
* Multiply
/ Division
\ Integer Division
Mod Remainder of division(modulo)
+ Add
- Subtract
& Join two text strings
                                                              Top

Aggregate Functions

Function What They Do Example
Avg Averages data in a group of records Avg([InvoiceAmt])
Count Counts the number of records in group Count([Lastname])
Max Selects the largest number in a group of records Max([InvoiceAmt])
Min Selects the smallest number in a group of records Min([InvoiceAmt])
StDev Calculates the Standard Deviation of a group of records StDev([age])
Sum Totals the data for a group of records Sum([age])
Var Calculates the Variance of a group of records Var([age])

                                                                            Top

Domain Aggregate Functions

Perform statistical calculations on a set of records (or domain)  in any table or query, overriding Group By expressions.  =DSUM("expression", "domain","criteria")
DAvg Calculates the average of the specified field values from the selected recordset
DCount Calculates the number of records with nonnull values in the specified field from the selected recordset
DMax Provides the maximum value of the specified field from the selected recordset
DMin Provides the minimum value of the specified feild from the selected recordset.
DStDev Estimates a population sample standard deviation of the specified field from the selected recordset.
DStDevP Estimates a population standard deviation of the specified field from the selected recordset.
DSum Calculates the sum of the specified field values from the selected recordset.
DVar Estimates a population sample variance of the specified field from the selected recordset.
DVarP Estimates a population variance of the specified field from the selected recordset.
DFirst Provides the value in the specified field from the first physical record in the selected recordset.
DLast Provides the value in the specified field from the last physical record in the selected recordset
DLookup Provides the value in the specified field from the selected recordset based on the specified criteria.

Top

Formatting Functions

Convert expressions from one format to another.  You can use these functions instead of changing a field's properties.
CCur    
CDbl    
CInt    
Clng    
Csng    
CStr    
CVar    
Format Formats a number, date, time, or string  
left Returns theleftmost n characters in a string  
Mid REturns a string that's part of another string  
Right Returns the rightmost n characters in a string.  
Visual Basic and Variables      TOP
A variable name represents a memory location in which the data is stored that may or may not change during program execution.  The Dim statement is used to declare a variable and to identify its data type.
Type Values Declaration
Byte Positive integers from 0 to 255 Dim age as Byte
Boolean Values that are either True or False Dim Paid as Boolean
Integer Whole numbers between -32,768 and 32,767 Dim age as Integer
Long Whole numbers between -2,147,483,648 and 2,147,483,647 Dim Stars As Long
String Text up to approximately 65,000 characters in length Dim Lastname as String
Currency Numbers with up to four decimal places between -955,337,203,685.5808 and 955,337,203,685.5807 Dim InvoiceAmt As Currency
Single Real numbers in the range +-1.40x10-45 to +-3.40 X1038 Dim Galaxies as Single
Double Real numbers in the range +-4.94 X 10-324 to +-1.79 x10308 Dim Stars as Double
Date Dates and times stored as real numbers Dim ToDate as Date
Object Data that holds Object references Dim Graph as Object
Variant Can contain any of the preceding data types Dim Catchall as Variant
TOP    
 

Switchboard Items

To have more than 8 items per swiitchboard.

1. Open the Switchboard Form in Design mode.
2. Open the Form's Class/code module.
3. Find the Private Sub FillOptions.
4. Look for the Constant Dec:
5. Const ConNumButtons=8 
6. Change the 8 to whatever number you want (probably 10 to 12 as you won"t be able to fit much more on the screen)
7. Go back to the Form
8. Add 2 or more (depending upon how many you specified above) CommandButtons with associated with associated Labels Labels.
9. Delete any Default Captions for the CommandButtons & Labels.
10. Rename the these controls, option 9, OptionLabel9, etc.
11. In the OnClick Event of Option 9 & Option Label9, put = HandleButtonClick(9). Repeat for each new control. 
12. Open the Switchboard Items Table. 

 

TOP

Field Types
Auto Number A number that is assigned automatically and never changes again.
Currency Dollar amounts
Date/Time Dates and times
Hyper Link Hyper Link address
Lookup Values that come from another table, a query, or a list of values you supply.
Memo Can contain numbers or letters up to 64,000 including spaces
Number Byte - 0 to 255, Integer, Long Integer, Single precision, double precision. 
OLE - object Picture, sound, or wordprocessing
Text Can contain numbers or characters up to 255 bytes including spaces.
Yes/NO Can contain Boolean values (yes/no, true/false, on/off) Can contain only one of two values
 

ActiveX Controls are typically used by programmers to add functionality to a form.

1. Click in the section where the new control should appear.
2. Click the More tools button in the toolbox or chose insert ActiveX control to show a menu of the ActiveX controls that come with Access.
3. Select a control from the menu.
4. Right-click the control, choose properties, and set any properties you may need to adjust for the control.

Examples of ActiveX controls from Microsoft Web Site:

Message Box Example

  1. On the Insert menu, point to Advanced, and then click ActiveX Control.
  2. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 CommandButton.
    2. In the Name box, type CommandButton1.
    3. Click OK.
  3. On the Insert menu, point to Advanced, and then click Script.
  4. In the Script dialog box, do the following:
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, type the following code:
                Sub CommandButton1_Click()
                  ' The Following line displays a Message Box with the date
                  ' when the command button is clicked.
                  msgbox "Today's date is " &DATE,64,"My MsgBox"
                End sub
      								
    3. Click OK.

Label Example

  1. On the Insert menu, point to Advanced, and then click ActiveX Control.
  2. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 CommandButton.
    2. In the Name box, type CommandButton2.
    3. Click OK.
  3. On the Insert menu, point to Advanced, and then click ActiveX Control.
  4. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 Label.
    2. In the Name box, type Label1.
    3. In the Width box, type 200.
    4. Click OK.
  5. On the Insert menu, point to Advanced, and then click Script.
  6. In the Script dialog box, do the following:
    1. Under Language, click VBScript , and then click to clear the Run Script on Server check box.
    2. In the Script box, type the following code:
                Sub CommandButton2_Click()
                  ' The following example fills a Label control with text
                  ' when the command button is clicked.
                  Label1.Caption="Today's date is " & DATE
                End sub
      								
    3. Click OK.

Text Box Example

  1. On the Insert menu, point to Advanced, and then click ActiveX Control.
  2. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 TextBox.
    2. In the Name box, type TextBox1.
    3. In the Width box, enter 140.
    4. Click OK.
  3. On the Insert menu, point to Advanced, and then click ActiveX Control.
  4. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 CommandButton.
    2. In the Name box, type CommandButton3.
    3. Click OK.
  5. On the Insert menu, point to Advanced, and then click ActiveX Control.
  6. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 TextBox.
    2. In the Name box, type TextBox2.
    3. In the Width box, type 300.
    4. Click OK.
  7. On the Insert menu, point to Advanced, and then click Script.
  8. In the Script dialog box, do the following:
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, type the following code:
                Sub CommandButton3_Click()
                  ' The following example fills in a text box
                  ' when the command button is clicked
                  TextBox2.Text="You have entered: "& UCASE(TextBox1.Text)
                End sub
      								
    3. Click OK.

Check Box Example

  1. On the Insert menu, point to Advanced, and then click ActiveX Control.
  2. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 CheckBox.
    2. In the Name box, type CheckBox1.
    3. Click OK.
  3. On the Insert menu, point to Advanced, and then click ActiveX Control.
  4. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 CommandButton.
    2. In the Name box, type CommandButton4.
    3. In the Width box, type 140.
    4. Click OK.
  5. On the Insert menu, point to Advanced, and then click ActiveX Control.
  6. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 Label.
    2. In the Name box, type Label2.
    3. In the Width box, type 300.
    4. Click OK.
  7. On the Insert menu, point to Advanced, and then click Script.
  8. In the Script dialog box, do the following:
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, type the following code:
                sub CommandButton4_Click()
                  ' This example evaluates the value of the checkbox and fills
                  ' in text in the label when the command button is clicked
                  if CheckBox1.Value=TRUE then Label2.Caption="The checkbox _
                    is checked"
                  if CheckBox1.Value=FALSE then Label2.Caption="The checkbox _
                    is not checked"
                end sub
      								
    3. Click OK.

Option Button Example

  1. On the Insert menu, point to Advanced, and then click ActiveX Control.
  2. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 OptionButton.
    2. In the Name box, type OptionButton1.
    3. Click OK.
  3. On the Insert menu, point to Advanced, and then click ActiveX Control.
  4. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 CommandButton.
    2. In the Name box, type CommandButton5.
    3. Click OK.
  5. On the Insert menu, point to Advanced, and then click ActiveX Control.
  6. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 Label.
    2. In the Name box, type Label3.
    3. In the Width box, enter 300.
    4. Click OK.
  7. Press ENTER.
  8. On the Insert menu, point to Advanced, and then click ActiveX Control.
  9. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 OptionButton.
    2. In the Name box, type OptionButton2. Click OK.
  10. On the Insert menu, point to Advanced, and then click Script.
  11. In the Script dialog box, do the following:
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, enter the following code:
                Sub CommandButton5_Click()
                  ' This example evaluates the value of the option button and
                    fills
      
                  ' in text in the label when the command button is clicked
                  If OptionButton1.Value=True Then
                  Label3.Caption = "This option is selected"
                  Else
                  Label3.Caption = "The other option is selected"
                  End If
                End sub
      								
    3. Click OK.

Toggle Button Example

  1. On the Insert menu, point to Advanced, and then click ActiveX Control.
  2. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 ToggleButton.
    2. In the Name box, type ToggleButton1.
    3. In the Width box, type 100. In the Height box, type 30.
    4. Click OK.
  3. On the Insert menu, point to Advanced, and then click Script.
  4. In the Script dialog box, do the following:
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, enter the following code:
                sub ToggleButton1_Click()
                  ' This example evaluates the value of the toggle button and
                  ' fills in text in the label when the command button is
                    clicked.
      
                  If ToggleButton1.Value = True Then
                  ToggleButton1.Caption = "DEPRESSED"
                  Else
                  ToggleButton1.Caption = ""
                  End If
                End Sub
      
      								
    3. Click OK.

Spin Button Example

  1. On the Insert menu, point to Advanced, and then click ActiveX Control.
  2. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 SpinButton.
    2. In the Name box, type SpinButton1.
    3. Click Properties. In the Properties window, do the following:
      1. In the Properties list, click Min. In the Value box, type -100. Click Apply.
      2. Click the Close button in the upper right corner of the Properties window.
      3. In the Edit ActiveX Control dialog box, click OK.
    4. Click OK.
  3. On the Insert menu, point to Advanced, and then click ActiveX Control.
  4. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 Label.
    2. In the Name box, type Label4.
    3. Click OK.
  5. On the Insert menu, point to Advanced, and then click Script.
  6. In the Script dialog box, do the following:
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, type the following code:
                sub SpinButton1_SpinDown()
                  ' This example evaluates if the spin button has been depressed
                    and
      
                  ' fills in text in the label when the command button is clicked
                  Label4.Caption = SpinButton1.Value
                  end sub
      
                sub SpinButton1_SpinUp()
                  ' This example evaluates if the spin button has not been
                    depressed
                  ' and fills in text in the label when the command button is
                    clicked
                  Label4.Caption = SpinButton1.Value
                end sub
      
      								
    3. Click OK.

Scroll Bar Example

  1. On the Insert menu, point to Advanced, and then click ActiveX Control.
  2. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 ScrollBar.
    2. In the Name box, type ScrollBar1.
    3. Click Properties. In the Properties window, do the following:
      1. In the Properties list, click Min.
      2. In the Value box, type -32766. Click Apply.
      3. Click the Close button in the upper right corner of the Properties window.
      4. In the Edit ActiveX Control dialog box, click OK.
    4. Click OK.
  3. On the Insert menu, point to Advanced, and then click ActiveX Control.
  4. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select "Microsoft Forms 2.0 Label."
    2. In the Name box, type Label5.
    3. Click OK.
  5. On the Insert menu, point to Advanced, and then click Script.
  6. In the Script dialog box, do the following:
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, enter the following code:
                Sub ScrollBar_Change()
                  ' This example evaluates the position of the marker on the
                    scroll
                  ' bar fills in text in the label when the command button is
                    clicked
                  Label5.Caption = -(ScrollBar1.Value)
                End sub
      
      								
    3. Click OK.

List Box Example

  1. On the Insert menu, point to Advanced, and then click ActiveX Control.
  2. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 ListBox.
    2. In the Name box, type ListBox1.
    3. Click Properties. In the Properties window, do the following:
      1. In the Properties list, click BorderStyle.
      2. In the Value list, select 1-Single.
      3. Click Apply.
      4. Click the Close button in the upper right corner of the Properties window.
      5. In the Edit ActiveX Control dialog box, click OK.
    4. Click OK.
  3. On the Insert menu, point to Advanced, and then click ActiveX Control.
  4. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 Label.
    2. In the Name box, type Label6.
    3. In the Width box, enter 400.
    4. Click OK.
  5. On the Insert menu, point to Advanced, and then click Script.
  6. In the Script dialog box, do the following:
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, type the following code:
                Sub ListBox1_Click()
                  ' This example evaluates which option in a list box has been
                  ' selected and fills in the resulting text in the label when
                  ' the command button is clicked
                  Label6.Caption= "You have selected " & _
                  Listbox1.List(ListBox1.ListIndex)
                End sub
      								

      NOTE: A ListBox requires you to write script that populates the list with values in an array. The script should be inserted at the top of the page so that it runs when your page is loaded into a Web browser.

    3. Click OK.
  7. Press CTRL+HOME. Press ENTER. Press the UP ARROW key.

    The insertion point should be positioned at the beginning of a blank line at the top of the page.
  8. On the Insert menu, point to Advanced, and then click Script.
  9. In the Script dialog box, do the following:
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, type the following code:
                Sub window_onLoad()
                  ' This example creates and populates the array used in the
                  ' proceeding list box example
                  ' dim array to populate ListBox
                  Dim ListBoxArray(2)
                  ListBoxArray(0)="RED"
                  ListBoxArray(1)="WHITE"
                  ListBoxArray(2)="BLUE"
                  ListBox1.List=ListBoxArray
                End sub
      								
    3. Click OK.

Combo Box Example

  1. On the Insert menu, point to Advanced, and then click ActiveX Control.
  2. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 ComboBox.
    2. In the Name box, type ComboBox1. Click OK.
  3. On the Insert menu, point to Advanced, and then click ActiveX Control.
  4. In the ActiveX Control Properties dialog box, do the following:
    1. In the Pick A Control list, select Microsoft Forms 2.0 Label.
    2. In the Name box, type Label7.
    3. In the Width box, type 400. Click OK.
  5. On the Insert menu, point to Advanced, and then click Script.
  6. In the Script dialog box, do the following:
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, enter the following code:
                sub ComboBox1_Click()
                  ' This example evaluates which option in a combo box has been
                  ' selected and fills in the resulting text in the label when
                  ' the command button is clicked.
                  Label7.Caption= "You have selected " & _
                  ComboBox1.List(ComboBox1.ListIndex)
                end sub
      								

      NOTE: A ListBox requires you to write script that populates the list with values in an array. The script should be inserted at the top of the page so that it executes when your page is loaded into a Web browser.

  7. Double-click the Script icon at the top of the page.

    NOTE: This icon was created in step 6 of the List Box example.
    1. Under Language, click VBScript, and then click to clear the Run Script on Server check box.
    2. In the Script box, type the following code.

      NOTE: Place this code at the bottom of the Script box, after the existing script, but before the last line that reads "end sub."
                ' This example creates and populates the array used in the
                ' combo box example.
                '
                ' dimension array to populate ComboBox
                Dim ListBoxArray(2)
                ListBoxArray(0)="RED"
                ListBoxArray(1)="WHITE"
                ListBoxArray(2)="BLUE"
                ListBox1.List=ListBoxArray
      								
    3. Click OK.
  8. Using sql code for a query list and report list in the same list box.
  9. SELECT [Name] FROM MSysObjects WHERE [Type]=5 And Left([Name],1)<>"~" Or [Type]=-32764 And Left([Name],1)<>"~" ORDER BY [Name];