Below is a more readable version of the SQL Select statement in our Access DateTime
Select M_Employees.Employee_ID, M_Employees.Work_Hours, M_Employees.Work_Date,
M_Employees.Work_Hours*M_Employees_PayRate.PayRate as Pay From M_Employees
Inner Join M_Empoyee_Payrate on M_Employees.Employee_ID=M_Employee_PayRate.Employee_ID
Where (((M_Employees.Work_Date)<>=M_Employee_PayRate.Start_Date and (M_Employees.Word_Date)<=IIF(IsNull(M_Employee_PayRate.Stop_date),Date(),M_employee_payrate.stop_date)));
More Date/Time Criteria Query Examples:
When using hard coded date values in an SQL query you'll need to
bracket the date value with # signs. Here is an example:
WHERE (((Client_Account_Transactions.[Date Invoiced])>#1/1/2006#));
Below is an example of how the date criteria looks in the design
Here is an example of referencing a form field for the date
To use any part of a date other than the whole date in a
datetime query you'll need to know the SQL DatePart Function.
Often you'll want to break a date down into days, week, months, quarters, etc.
Below is a summary of the DatePart function within Access SQL:
The syntax for the datepart function is:
DatePart(interval, date[,firstdayofweek[, firstweekofyear]])
Arguments are as follows:
interval - Required. String expression interval of time/date you
want to retrieve.
date - Required. Variant (Date) value in any standard date format.
firstdayofweek - Optional. A constant that specifies the first day
of the week. The default value is Sunday.
firstweekofyear - Optional. A constant that specifies the first
week of the year. The default is the 1st week in which January 1 occurs.
argument has these settings:
||Day of year
More Examples of DatePart Operations:
Age Calculation DateTime Query Using Date Part Function