Jiangws's Blog

 
 
 
  • About

    夢を叶える為
 
Access combo box Set unique column but show other column and save other column May 11th, 2021
  1. Combo box : control souce -unique column, but set first column to the column you want to show(Other Column)
  2. Other column: set hide control and set the value when combo box change
Posted in Access || Comments Off on Access combo box Set unique column but show other column and save other column
The Bang! (Exclamation Operator) in VBA August 2nd, 2020

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 …

Posted in Access || Comments Off on The Bang! (Exclamation Operator) in VBA
MS Access Select top n query grouped by multiple fields July 26th, 2020
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 
Posted in Access || Comments Off on MS Access Select top n query grouped by multiple fields
对数据进行分组的SQL July 26th, 2020

Table:

idscore
156
291
367
454
556
669
761
883
999
Subject_table

MS Access

SELECT Switch(score < 60,  "less 60",
        	score < 80,  "60 - 80",
        	score < 100 , 
Posted in Access || Comments Off on 对数据进行分组的SQL
transfering an export/import specification from one database to another (1 Viewer) July 24th, 2020

In Access 2007, display the “External Data” tab. Click on the “Access” option.

Use the Browse button to locate the file you want to import the saved import-export specification. Once a database has been specified, the “OK” button will become …

Posted in Access || Comments Off on transfering an export/import specification from one database to another (1 Viewer)
How do I simulate Excel’s Ceiling/Floor Functions in VBA? July 18th, 2020

To simulate these two Excel functions in Access Visual Basic, paste the following code into a new module and save it:

CODE

Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is 
Posted in Access || Comments Off on How do I simulate Excel’s Ceiling/Floor Functions in VBA?
Pad single-digit numbers with a zero June 4th, 2020

VBA has a Format() function that you can use to pad numbers.

animal = “sinani-” & Format$(i, “00”)
This will pad single-digit numbers with a 0. Your two- and three-digit numbers will continue to work as expected.

From:https://stackoverflow.com/questions/31628535/pad-single-digit-numbers-with-a-zero

Posted in Access || Comments Off on Pad single-digit numbers with a zero
Strings and Manipulations(VBA) June 4th, 2020

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, …

Posted in Access || Comments Off on Strings and Manipulations(VBA)
AccessでExcelのROUNDUP・ROUNDDOWN関数に相当するものを作る方法 November 14th, 2016
○分類
VBA
○解説
  • モジュールを新規作成して、以下のコードを入力して保存します。

    Option Compare Database

    Public Function RoundUp(x As Currency, s As Integer) As Currency

    Dim w As Currency
    t = 10 ^ Abs(s)

    If x > 0 Then
    If s > 0 Then
    RoundUp = -Int(-x *

Posted in Access || Comments Off on AccessでExcelのROUNDUP・ROUNDDOWN関数に相当するものを作る方法
Access 2007 SQL the default value for switch March 20th, 2015

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 …

Posted in Access || Comments Off on Access 2007 SQL the default value for switch