?
Solved

Constant date value in SQL query

Posted on 2003-03-12
8
Medium Priority
?
197 Views
Last Modified: 2010-08-05
I have SQL query with parameter "Check Date":
SELECT Distribution_Checks_1.fundid, Distribution_Checks_1.IDCode, Distribution_Checks_1.[payee-name], Distribution_Checks_1.[Payee-Address1], Distribution_Checks_1.[Payee-Address2], Distribution_Checks_1.[Payee-City], Distribution_Checks_1.[Payee-State], Distribution_Checks_1.[Payee-Zip], Distribution_Checks_1.[Payee-Attention], Distribution_Checks_1.grdescription, Distribution_Checks_1.CheckNum, Distribution_Checks_1.CheckAmt, Distribution_Checks_1.CheckDate, Distribution_Checks_1.fund_name, Distribution_Checks_1.FOUND_sql_Profile_OrgName, Distribution_Checks_1.LName, Distribution_Checks_1.FName, Distribution_Checks_1.Prefix, Distribution_Checks_1.Suffix, Distribution_Checks_1.Salutation, Distribution_Checks_1.FOUND_sql_Profile_Address1, Distribution_Checks_1.FOUND_sql_Profile_Address2, Distribution_Checks_1.FOUND_sql_Profile_City, Distribution_Checks_1.FOUND_sql_Profile_State, Distribution_Checks_1.FOUND_sql_Profile_Zip, Distribution_Checks_1.ID, Distribution_Checks_1.DivisionCode, Distribution_Checks_1.DivName, Distribution_Checks_1.pic, Distribution_Checks_1.signature, Distribution_Checks_1.SignatureName, Distribution_Checks_1.FOUND_sql_Profile_1_Address1, Distribution_Checks_1.FOUND_sql_Profile_1_Address2, Distribution_Checks_1.FOUND_sql_Profile_1_City, Distribution_Checks_1.FOUND_sql_Profile_1_State, Distribution_Checks_1.FOUND_sql_Profile_1_Zip, Distribution_Checks_1.FOUND_sql_Profile_1_OrgName, Distribution_Checks_1.small_logo, Distribution_Checks_1.[Anonymous-Fund], Distribution_Checks_1.web_site, Distribution_Checks_1.WorkPhone
FROM Distribution_Checks_1
WHERE Distribution_Checks_1.CheckDate=[Check Date]
ORDER BY Distribution_Checks_1.fundid, Distribution_Checks_1.IDCode;

I need to change [Check Date] on real date.Table have field "Check Date" and values "1/10/2003" and "1/17/2003".When I set date in SQL query ....WHERE Distribution_Checks_1.CheckDate=1/17/2003 ORDER BY...
I see result with empty records.What is wrong?Thanks.
0
Comment
Question by:MByte
[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
8 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 200 total points
ID: 8118868
change this:
....WHERE Distribution_Checks_1.CheckDate=1/17/2003 ORDER BY...

to this:

....WHERE Distribution_Checks_1.CheckDate= #1/17/2003# ORDER BY...


the #...# s are needed to tell Access to convert 1/17/2003 to a DATE type value.

It is IMPORTANT to understand that Date values are NOT stored in the database as 1/17/2003  but rather Date-type values in Access are stored as NUMBERS (type Single - they have sa decimal point).  The Integer part of the NUMBER is the count of the number of days since 12/31/1899 (midnight), and the Fraction part (to the Right of the Decimal point) is time as a fraction of 1 day (measured in seconds), so that 12:00 NOON would be .5


You also need to make sure that what is actually stored in the CheckDate field is just the DATE part and DOES NOT include the TIME (since the field is probably defined as Date/Time and could include BOTH date and Time.  If the Field is filled by using the Date() function, then you get JUST the Date part (all values will have the Fractional part as .0, and will be equivalent to MIDNIGHT).  If the field is filled with the Now() function, then you get BOTH date and Time when the record was changed (or created), and if THAT is the case, then your Where clause will FAIL, since the Field contains TIME information, but the test date DOES not, hence the EQUALITY test will fail (unless the Record JUST HAPPENED to be created at EXACTLY MIDNIGHT, not 1 second after).

AW
0
 
LVL 1

Expert Comment

by:njelger
ID: 8119181
little recommendation:

If you use date constants don't use american format mm/dd/yyyy but instead yyyy-mm-dd and it will not fail anywhere.

/ j
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8119288
#1/17/2003# with AUTOMATICALLY convert the given date to whatever is the current Local setting for DateFormat, as set in the Control Panel.

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Expert Comment

by:njelger
ID: 8119320
Probably, but don't count on it. Well, maybe in ms Access.

/ j
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8124372
njelger, after all this IS an Access Question Board, if I am not mistaken, rather than the more general Database question board....LOL

AW
0
 
LVL 1

Expert Comment

by:njelger
ID: 8125866
its cool....but you can screw it up in access too if you use VBA..

CU / j
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8785560
Hi MByte,
This question has been abandoned and needs to be finalized (102 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0
 
LVL 18

Expert Comment

by:1William
ID: 8889063
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to Arthur_Wood
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William
EE Cleanup Volunteer
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

765 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