[AccessD] New thrd: dates

Hi Bill

GUI is the user interface which includes the graphic query designer.

Your questions are already answered by Stuart.

The difference between DateValue (and TimeValue) and CDate is that for a date/time expression, CDate will return the full date/time value while DateValue and TimeValue only returns a part of it.

In addition you have CVDate which - though labelled obsolete - works perfectly well and has the advantage over CDate that it accepts Null values (returning Null).

The great value of DateValue is that it reads a textbox using the local format as the first attempt. Thus a date like 2014-09-11 will here by default be displayed as 11-09-2014, and DateValue will read and convert this to the date/time value of 2014-09-11.

This is where much code fails. If you have a query with filtering on a textbox this way:

Select * From Table Where DateField = [Forms]![frmMyForm]![txtDate]

it will work if  you specify [Forms]![frmMyForm]![txtDate] as a parameter of data type Date (and the form is open).

But if you write SQL code like this:

strSQL = "Select * From Table Where DateField = #" & Me!txtDate & "#"

it will fail outside the US as it will result in:

Select * From Table Where DateField = #11-09-2014#

To solve this, the simple method shown by Stuart will work, though you should make it a habit to use the ISO sequence: 

strDate = Format(Me!txtDate, "yyyy\/mm\/dd")
strSQL = "Select * From Table Where DateField = #" & strDate & "#"

or, as Stuart mentions:

strSQL = "Select * From Table Where DateField = DateValue(" & Me!txtDate & ")"

You will often meet this falty construction which only shows a lack of understanding of the mechanics:

strSQL = "Select * From Table Where DateField = #" & CDate(Me!txtDate) & "#"

The reason it fails is, that even though CDate correctly converts the expression to a date/time value, this cannot be concatenated with the SQL string, thus Access has to cast it to a text expression. However, this will be done as to the local settings resulting (here) in 11-09-2014 where you would need 09-11-2014.

/gustav


-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Bill Benson
Sendt: 21. august 2014 17:13
Til: Access Developers discussion and problem solving
Emne: [AccessD] New thrd: dates

Please forgive my lack of experience, also I thought since this is a deeper discussion on dates I would retain some but not all of the former posts.

GUI. What is meant by GUI date? Is this a bound textbox reading a date from the back end?

Example, my backend had Aug 4th 2014 stored as 08/04/2014. In the local environment with no local formatting, would that display as
04/08/2014 In Europe?

Now supposed unbound forms (which are what primarily work with)

If I populate a text box with

TXTLastChange = DLOOKUP ("Max(Change_dt)","MyTable","")

And the max date in the table is 08/10/2014, what will show in the local
(Europe) text box?, I assume based on what has been said, 10/08/2014, no?

If suppose I have another text box called TxtEffective_dt.  The user wants to enter Aug 12th, 2014 so thwy, being in Europe, enter 12/08/2014 or 12/08/14.

When I take that value to enter it into the database (remember backend is
US) would I write

Sql = "Insert into myTable (name, address, effective_dt) values ('" & txtname & "','" & txtaddress & "', #" & txteffectivedate & "#)"

Or
Sql = "Insert into myTable (name, address, effective_dt) values ('" & txtname & "','" & txtaddress & "', #" & cdate(txteffectivedate) & "#)"

Or something else that will have to convert the text property of txteffectivedate to the US format?

>
> On Aug 21, 2014 10:43 AM, "Gustav Brock" <gustav at cactus.dk> wrote:
>>
>> Hi Bill
>>
>> The workaround in SQL code is always to use the ISO format: yyyy-mm-dd.
>> It will never fail.
>>
>> As for the GUI, it never fails as long as you follow the simple rules mentioned.
>>
>> /gustav