The Bang! (Exclamation Operator) in VBA

Posted by & filed under Access.

One of the most peculiar aspects of Visual Basic for Applications in the ! operator, called the “bang”. There’s a lot of confusion about the bang operator, and nowhere have I seen the correct whole story about it. So, I …

MS Access Select top n query grouped by multiple fields

Posted by & filed under Access.

SELECT StudentID, Year, Subject,  AVG(TestScore) AS AvgScore
  FROM
(
  SELECT StudentID, Year, Subject, TestScore
   FROM MyTable t
   WHERE TestID IN
  (
   SELECT TOP 3 TestID 
     FROM MyTable
    WHERE StudentID = t.StudentID
      AND Year = t.Year
      AND Subject = t.Subject
    ORDER 

Strings and Manipulations(VBA)

Posted by & filed under Access.

Text Functions, Workbook
Before continuing please be aware of the following information available in Excel HELP

FIND(find_text,within_text,start_num)
Worksheet Description VBA
CHAR Returns the character specified by the code number CHR
CLEAN Removes all nonprintable characters from text
Characters 0-31, 129, …

Access 2007 SQL the default value for switch

Posted by & filed under Access.

I am using switch statement in access 2007, i want to know how I can specify default value.
select
switch
(
MyCol = 1, ‘Value is One’,
MyCol = 2, ‘Value is Two’
) from MyTable
-

SELECT
Switch(MyTable.[MyCol]=’1′,’Terrestrial’,
MyTable.[MyCol]=’2′,’Value …