andybrooke
asked on
return value that equal today
Hello I have a query that looks up a table and returns certain values. I want to filter it further and only show fields with todays date.
there is a field which is formated to date/time and displays the input as follows:
03/04/2012 10:00:00
the query i have written is bellow:
SELECT USERINFO.Name, USERINFO.USERID, CHECKINOUT.CHECKTYPE, USERINFO.ATT, USERINFO.ShiftStatus, CHECKINOUT.CHECKTIME
FROM USERINFO LEFT JOIN CHECKINOUT ON USERINFO.USERID = CHECKINOUT.USERID
WHERE (((USERINFO.ATT)=1) AND ((USERINFO.ShiftStatus)="F
ORDER BY USERINFO.Name;
when i input into the criteria Date() it returns zero rows.
what is the formula for the criteria to return anything were the field "CHECKTIME" equals todays date but ignore the time.
there is a field which is formated to date/time and displays the input as follows:
03/04/2012 10:00:00
the query i have written is bellow:
SELECT USERINFO.Name, USERINFO.USERID, CHECKINOUT.CHECKTYPE, USERINFO.ATT, USERINFO.ShiftStatus, CHECKINOUT.CHECKTIME
FROM USERINFO LEFT JOIN CHECKINOUT ON USERINFO.USERID = CHECKINOUT.USERID
WHERE (((USERINFO.ATT)=1) AND ((USERINFO.ShiftStatus)="F
ORDER BY USERINFO.Name;
when i input into the criteria Date() it returns zero rows.
what is the formula for the criteria to return anything were the field "CHECKTIME" equals todays date but ignore the time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have just double checked and the data type on the field CHECKTIME is a Date/Time
try this query, see if you will get the error
select CHECKINOUT.CHECKTIME, DateValue(CHECKINOUT.CHECK
From CHECKINOUT
select CHECKINOUT.CHECKTIME, DateValue(CHECKINOUT.CHECK
From CHECKINOUT
Try this (uses the DateDiff function):
SELECT USERINFO.Name, USERINFO.USERID, CHECKINOUT.CHECKTYPE, USERINFO.ATT, USERINFO.ShiftStatus, CHECKINOUT.CHECKTIME
FROM USERINFO LEFT JOIN CHECKINOUT ON USERINFO.USERID = CHECKINOUT.USERID
WHERE (((USERINFO.ATT)=1) AND ((USERINFO.ShiftStatus)="F
ORDER BY USERINFO.Name;
SELECT USERINFO.Name, USERINFO.USERID, CHECKINOUT.CHECKTYPE, USERINFO.ATT, USERINFO.ShiftStatus, CHECKINOUT.CHECKTIME
FROM USERINFO LEFT JOIN CHECKINOUT ON USERINFO.USERID = CHECKINOUT.USERID
WHERE (((USERINFO.ATT)=1) AND ((USERINFO.ShiftStatus)="F
ORDER BY USERINFO.Name;
DateValue ends up being a string that is why there is a mismatch. You could try (CDate(DateValue(CHECKINOU
(CDate(Int(CDbl(CHECKINOUT
(CDate(Int(CDbl(CHECKINOUT
ASKER
Hi Capricorn1, I tried your mini query and it worked. it returned two columns. the last "Expr1001" only had the date...
<DateValue ends up being a string that is why there is a mismatch. > NOT true
DateValue Function
Returns a Variant (Date).
Syntax
DateValue(date)
The required date argument (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) is normally a string expression (string expression: An expression that evaluates to a sequence of contiguous characters. Elements of the expression can be: functions that return a string or a string Variant (VarType 8); a string literal, constant, variable, or Variant.) representing a date from January 1, 100 through December 31, 9999. However, date can also be any expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) that can represent a date, a time, or both a date and time, in that range.
DateValue Function
Returns a Variant (Date).
Syntax
DateValue(date)
The required date argument (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) is normally a string expression (string expression: An expression that evaluates to a sequence of contiguous characters. Elements of the expression can be: functions that return a string or a string Variant (VarType 8); a string literal, constant, variable, or Variant.) representing a date from January 1, 100 through December 31, 9999. However, date can also be any expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) that can represent a date, a time, or both a date and time, in that range.
<DateValue ends up being a string that is why there is a mismatch. > NOT true
I stand corrected. It's interesting that I also have some queries that get the data type mismatch error when I comparing a DateValue(date-time) to a date. But if I use CDate(DateValue(date-time)
I stand corrected. It's interesting that I also have some queries that get the data type mismatch error when I comparing a DateValue(date-time) to a date. But if I use CDate(DateValue(date-time)
check for null values in the field select CHECKTIME
or run this query
select USERID, CHECKTIME
From CHECKINOUT
Where CHECKTIME Is Null Or [CHECKTIME] & ""=""
or run this query
select USERID, CHECKTIME
From CHECKINOUT
Where CHECKTIME Is Null Or [CHECKTIME] & ""=""
ASKER
I have solved the problem.
because i was using a left join some rows were blank in the date/time field. I have changed this to inner join and this has removed the blank fields so now the query works
because i was using a left join some rows were blank in the date/time field. I have changed this to inner join and this has removed the blank fields so now the query works
@capricorn1, With your mention of null values, I guess that's the reason I had the same error and relied on CDate instead not realizing what the real problem was.
ASKER
"Data type mismatch in criteria expression.