Solved

return value that equal today

Posted on 2012-04-03
12
471 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

632 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