software is fun software is fun - 1 year ago 69
SQL Question

My Query is not giving the right results in access

SELECT (SignIn.VisitorFirstName & " " & SignIn.VisitorLastName) AS [Visitor Name], SignIn.SignInDateTime AS [Sign In Time], ([UserList.FirstName] & " " & [UserList.LastName]) AS Reason
FROM SignIn INNER JOIN UserList ON SignIn.AssignedPO = UserList.POid
WHERE (((SignIn.Complete)=No) AND ((Format([SignInDateTime],"Short Date")) Between #8/8/2016# And #8/10/2016#))
ORDER BY SignIn.SignInDateTime;

I am getting values for dates outside the scope.

The query runs but returns the wrong values. (see image below)

EDIT from comments: It's returning values before August 8th


enter image description here

Properties of my DateTime field (SignInDateTime)

enter image description here

Answer Source

Not sure what this conversion/comparison is doing

((Format([SignInDateTime],"Short Date"))

You might be safe to use CDate or just the Field itself - it looks like it's a date time field anyways

If it is a Date/Time field, try changing this

((Format([SignInDateTime],"Short Date"))

To this

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download