Solved

return value that equal today

Posted on 2012-04-03
12
450 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
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero 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
 
LVL 119

Expert Comment

by:Rey Obrero
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now