Solved

return value that equal today

Posted on 2012-04-03
12
463 Views
Last Modified: 2012-04-03
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
Comment
Question by:andybrooke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37801961
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
 

Author Comment

by:andybrooke
ID: 37801984
Hi, I tried that and got an error.
"Data type mismatch in criteria expression.
0
 

Author Comment

by:andybrooke
ID: 37801993
I have just double checked and the data type on the field CHECKTIME is a Date/Time
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 120

Expert Comment

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

select CHECKINOUT.CHECKTIME, DateValue(CHECKINOUT.CHECKTIME)
From   CHECKINOUT
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37802036
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37802048
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
 

Author Comment

by:andybrooke
ID: 37802077
Hi Capricorn1,  I tried your mini query and it worked. it returned two columns. the last "Expr1001"  only had the date...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37802092
<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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37802147
<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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37802179
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
 

Author Comment

by:andybrooke
ID: 37802204
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37802209
@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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question