Access SQL Functions

I seldom use an Access database, but I still look down on it. Haha! Serious. No, it was used at the end. But for me, the database is the same thing, haha. But there are still a lot to learn. In fact, there are a lot of applications, and I also find that the ACCESS database is quite powerful, that is, the library functions are not so rich!

Today, I encountered a problem. If the time on the computer is changed from the Control Panel to AM or am, instead of 24 hours, the stored time in the database also changes. If such a problem occurs, the SQL statement is incorrect. It is estimated that there may be no function for writing the date conversion. I have added this function and it is no problem to execute the SQL statement.

Data Inventory time format: 10:16:41

1.

Select * from table
Where lastmodifytime> cdate ("10:16:41 ");

2.

Select * from table
Where lastmodifytime> cdate ("Maid 19:16:41 ")

The query results are normal when SQL is executed in both cases. Shuang --

▲Date/Time
Cdate converts a string into a date select cdate ("2005/4/5 ")
Date returns the current date
Dateadd adds a specified date to select dateadd ("D", 30, date () to add 30 days to the current date, where D can be converted to yyyy or H.
Datediff determines the interval between two dates select datediff ("D", "2006-5-1", "2006-6-1") returns 31, where D can be converted to yyyy, M, H, etc.
Datepart returns a part of the date. Select datepart ("D", "2006-5-1") returns 1, that is, 1, and D can also be converted to yyyy or M.
Day returns the D part of the date, which is equivalent to the D part of datepart.
Hour of the date returned by hour
Isdate determines whether it is a date. It is a date that returns-1, not a date that returns 0
Minute returns the minute of the date.
Month returns the month of the date.
Now returns the current time (complete time, including year, month, day, hour, minute, second)
Second returns the second part of the date.
Time returns the current time (except last year/month/day)
Weekday returns the current week of a date (Sunday is 1, Monday is 2, and Tuesday is 3...), for example, select weekday (now ());
Year returns the year of a date.

Check
Isempty checks whether it is null (however, no matter what the situation is, 0 is returned)
Isnull checks whether the value is null. If the value is null, 0 is returned. If the value is not null,-1 is returned.
Isnumeric checks whether it is a number. If it is a number,-1 is returned; otherwise, 0 is returned.

Arithmetic
ABS absolute value
Returns the tangent of ATN.
Cos cosine value
Exp returns the given power of E.
Fix returns the integer part of the number (that is, the fractional part is completely truncated)
Int rounded down the number to the nearest integer. (Actually equivalent to fix)
Log returns the base-e logarithm.
RND returns a random value between 0 and 1.
SGN returns the positive and negative signs of a number (positive returns 1, negative returns-returns 0)
Sin sine Value
Sqr returns the square root value.
Tan tangent

▲ProgramProcess
Choose returns the value of the string group following the first parameter. Select choose (1, "A", "B", "C") returns a and 1 to 2, return B, change to 3, and return C (the first parameter can also be a field)
IIF returns a specific value select IIF ("3> 1", "OK", "false") based on the expression, and returns OK

▲Sql Aggregate functions
AVG obtains the average value of a field.
Count count
MAX: maximum value of a field
Min: obtain the minimum value of a field.
STDev estimates the standard deviation of the sample (ignore the logical value and text in the sample ).
Stdevp calculates the standard deviation of the entire sample population given in the form of parameters (ignoring logical values and text.
Sum calculation field sum
VaR estimates the sample variance (ignore the logical values and text in the sample ).
Varp calculates the variance of the entire sample population (ignore the logical values and text in the sample population ).

▲ text
ASC returns the acⅱ Value of the letter, and select ASC ("A") returns 65
CHR converts the ASCII value to the select CHR (65) character) return "A"
Format formatted string, select format (now (), 'yyyy-mm-dd'), return similar to "", select format (3/9, "0.00") returns the 0.33
select instr ("ABC", "A") position of the instr query substring in the string. Returns 1, select instr ("ABC ", "F ") returns 0
lcase returns the string in lowercase
left truncated string
Len returns the string length
ltrim left truncated space
Mid Returns the substring select mid ("123 ", 1, 2) As middemo returns 12
right truncated string
rtrim right truncated space
Space Generated space select space (4) returns four spaces
strcomp to compare whether the two strings have the same content (Case Insensitive). Select strcomp ("ABC", "ABC") returns 0, select strcomp ("ABC ", "123")-1 is returned.
trim intercepts spaces at both ends of the string.
ucase converts the string to uppercase.