• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

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)="FT") AND ((CHECKINOUT.CHECKTIME)=Date()))
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.
0
andybrooke
Asked:
andybrooke
  • 4
  • 4
  • 3
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this query


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)="FT") AND (DateValue(CHECKINOUT.CHECKTIME)=Date()))
ORDER BY USERINFO.Name;


added DateValue to the expression

(DateValue(CHECKINOUT.CHECKTIME)=Date())
0
 
andybrookeAuthor Commented:
Hi, I tried that and got an error.
"Data type mismatch in criteria expression.
0
 
andybrookeAuthor Commented:
I have just double checked and the data type on the field CHECKTIME is a Date/Time
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
try this query, see if you will get the error

select CHECKINOUT.CHECKTIME, DateValue(CHECKINOUT.CHECKTIME)
From   CHECKINOUT
0
 
wdosanjosCommented:
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)="FT") AND ( DateDiff("d",CHECKINOUT.CHECKTIME, Date()) = 0 )
ORDER BY USERINFO.Name;
0
 
IrogSintaCommented:
DateValue ends up being a string that is why there is a mismatch.  You could try (CDate(DateValue(CHECKINOUT.CHECKTIME))=Date()) or
(CDate(Int(CDbl(CHECKINOUT.CHECKTIME))) = Date()).  I have observed that the latter format runs faster than using DateValue but you could use either.
0
 
andybrookeAuthor Commented:
Hi Capricorn1,  I tried your mini query and it worked. it returned two columns. the last "Expr1001"  only had the date...
0
 
Rey Obrero (Capricorn1)Commented:
<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.
0
 
IrogSintaCommented:
<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)) it works.
0
 
Rey Obrero (Capricorn1)Commented:
check for null values in the field select  CHECKTIME

or run this query


select USERID, CHECKTIME
From   CHECKINOUT
Where CHECKTIME  Is Null Or [CHECKTIME] & ""=""
0
 
andybrookeAuthor Commented:
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
0
 
IrogSintaCommented:
@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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now