Rocky Smolin
rockysmolin at bchacc.com
Fri Apr 1 16:00:02 CDT 2011
Demonstrating once again why I've been on this list for lo these many years.
I'm going to be doing a lot of scheduling work for this client over in
Bahrain in the coming months - Creating a whole shop floor scheduling and
control system for them - very date intensive.
"I pity the fool who's not a member of AccessD" - Mr. T.
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, April 01, 2011 10:38 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Un-American Date Filter
Hi Rocky et al
After all these years it's a surprise to me how much uncertainty, hype, and
misunderstandings there seems to be about how to handle dates and SQL.
It isn't that difficult and no magic is involved, so please read carefully.
The rules are simple but strict and should be followed at all times. No
exceptions as there do not exist cases where the rules fall short.
First, one has to distinguish between a "date/time value" (of data type
Date) and a "string expression for a date/time value" (of data type String).
The use of any other data type like Double (the underlying data type of
Date) or Long is never needed or may even fail (Long!).
In VB(A) data type Date is always what to use:
Dim datSomeDateTime As Date
datSomeDateTime = Now
The only exception is Variant which can be used as (a temporary) data type
if Null must be an acceptable value.
Dim datSomeDateTime As Date
Dim varSomeDateTime As Variant
varSomeDateTime = rst!Fields("SomeDateTime")
If Not IsNull(varSomeDateTime) Then
datSomeDateTime = varSomeDateTime
End If
String expressions for date/time values are needed only for display of
formatted values and for building SQL strings.
A. Formatted values are used for:
1. A TextBox if you for some reason prefer not to set the Format property of
this (clear the Format property):
Me!txtSomeDateTime = Format(Now, "mmm-dd") or used as ControlSource:
=Format(Now, "mmm-dd")
2. A recordsource for a Combo/ListBox (where any field not text _will_ be
casted to text out of your control):
Select *, Format([SomeDateTime], "mmm-dd") From tblSomeTable
3. Some other purposes like export to a text file.
B. SQL strings are used in many occasions, mostly for creating dynamic SQL
queries.
Problem here is that different SQL engines as well as ADO take different
formats and delimiters for date/time values.
In SQL, date/time values can origin from three sources:
1. Fields of data type Date
2. Functions that return data type Date
3. String expressions
Whenever a string expression for a date/time value is needed, the date/time
value must be converted by you to a string of a defined format to play safe;
if you don't, VB(A) will itself cast the date/time value to text using the
local settings of Windows, which means you are out of control and your code
will fail sooner or later.
As to which format to use, it is all about avoiding any possible trap.
Here are some typical formats and why not to use them:
dd/mm/yyyy - will fail in most cases
mm/dd/yyyy - accepted by most engines but will fail in ADO (FindFirst)
yyyymmdd - will fail for some engines and anywhere in Access dd-mmm-yy and
any other format using literal months - will fail in non-English
environments
That leaves this single unambiguous format - the ISO format - which will
never fail:
yyyy/mm/dd (or yyyy-mm-dd, doesn't normally matter)
Further, and maybe for the reason to be safe, this _is_ the preferred and
recommended format for SQL Server.
Thus, there really is no reason to use any other format than this.
So, some examples:
1.
strSQL = "Select tblA.*, tblB.* From tblA, tblB Where
A.[SomeDateField]=B.[SomeDateField];"
2.
strSQL = "Select tblA.* From tblA, tblB Where
A.[SomeDateField]=DateAdd('d',-8,B.[SomeDateField]);"
strSQL = "Select tblA.* From tblA, tblB Where
A.[SomeDateField]=DateSerial(" & intYear & "," & intMonth & "," & intDay &
");"
3.
strDate = Format(DateAdd("d", -8, Date), "yyyy\/mm\/dd") For Access SQL:
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]=#" & strDate &
"#"; For SQL Server and many other engines:
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]='" & strDate &
"'";
One important additional note is about fetching values from a TextBox. If
this is either bound to a field of data type Date or has been applied a
Format property as a date, it will return a value of data type Date.
Further, a typed in date _will_ be read according to the local settings of
Windows even if these differ from the Format property setting. Thus, the
Format property setting has no influence of the value returned. It is only
when the TextBox has focus and if you then read the Text property of the
TextBox you will have the displayed string returned.
This means that the value of such a TextBox can safely (except for Null) be
passed to a date/time variable:
datSomDateValue = Forms!frmSomeForm!txtSomeDate or to be explicit:
datSomDateValue = Forms!frmSomeForm!txtSomeDate.Value
This also means that this will never fail (except for Null values or if the
form is closed):
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]=
Forms.frmSomeForm.txtSomeDate";
However, this may fail because the date value is casted according to the
local settings:
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= #" &
Forms!frmSomeForm!txtSomeDate & "#";
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= #" &
datSomeDateValue & "#";
This will not fail in Access SQL (for most other engines replace # with
single quote):
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= #" &
Format(Forms!frmSomeForm!txtSomeDate, "yyyy\/mm\/dd") & "#";
But this may fail (because the date/time values will be casted to text out
of your control):
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= DateValue(" &
Forms!frmSomeForm!txtSomeDate & ")";
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]=#" &
DateValue(Forms!frmSomeForm!txtSomeDate) & "#";
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]= CDate(" &
Forms!frmSomeForm!txtSomeDate & ")";
strSQL = "Select tblA.* From tblA Where A.[SomeDateField]=#" &
CDate(Forms!frmSomeForm!txtSomeDate) & "#";
It also means that - regarding the topic - this doesn't make sense because
Forms.frmSomeForm.txtSomeDate already is of data type Date:
datSomeDateTime = DateValue(Forms!frmSomeForm!txtSomeDate)
What happens is that first Forms.frmSomeForm.txtSomeDate is casted to a
string - because that is what DateValue expects - then casted by DateValue
to data type Date. Back and forth. What you will obtain, however, by this
method is that a possible time part of Forms!frmSomeForm!txtSomeDate will be
stripped.
This makes no sense at all as it converts a date/time to a date/time:
datSomeDateTime = CDate(Forms!frmSomeForm!txtSomeDate)
The only reason for using:
datSomeDateTime = DateValue(Forms!frmSomeForm!txtSomeDate)
would be the rare case where the TextBox is neither bound to a date/time
field nor formatted as a date/time - thus returning a value of data type
Text - allowing the user to input a date as text in free style.
Happy Dating!
/gustav
>>> rockysmolin at bchacc.com 31-03-2011 19:55 >>>
Dear List:
I create a temp table using a SQL statement I construct in code to
incorporate various filter - one of which is a date filter.
Works well here but when I send it to the user in Bahrain who's regional
setting is (I assume from the screen shot he sent) English U.K. where the
date format is dd/mm/yyyy it fails. I set my regional settings on my box to
U.K. and sure enough it fails - returns all records with no date filtering.
Here's the SQL statement that creates the table:
INSERT INTO tblKittingToMIS ( PartNumber, OrderNumber, DueDate, QuantityDue
) SELECT tblDemand.PartNumber, tblDemand.OrderNumber, tblDemand.DueDate,
tblDemand.QuantityDue FROM tblDemand WHERE DueDate >= #31/03/2011# AND
DueDate <= #07/04/2011#
which is supposed to filter out any record in tblDemand where the Due Date
is outside of the range March 31 to April 7. But it don't.
When I look in tblDemand, the dates are displayed properly as dd/mm/yyyy.
Why doesn't this work? Should I be using some kind of different syntax for
this filter?
MTIA
Rocky Smolin
Beach Access Software
858-259-4334
Skype: rocky.smolin
www.e-z-mrp.com <http://www.e-z-mrp.com/>
www.bchacc.com <http://www.bchacc.com/>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com