Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

return value that equal today

Posted on 2012-04-03
12
Medium Priority
?
484 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1500 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
Independent Software Vendors: 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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

885 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