473,832 Members | 2,132 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

A2K - still have a slight date problem...

Hi,

I generate a report using two dates (From and To). I notice if I enter
01/10/2003 that it is interpreted by Access as 10/01/2003 i.e 10th January
rather than 1st October as I intended.

This is the old problem of US date format mm/dd/yy versus dd/mm/yy.
I am stepping through the code and the dates do seem to be ok but when the
report runs I get records earlier than 1st October which is not what I want.

Any ideas on how to allow the use to enter in dd/mm/yyyy format and for that
date not to be misinterpreted by Access??
This is driving me quite potty.

thanks
Martin
Nov 12 '05 #1
13 3102
TS

"Deano" <ma************ @hotmail.com> wrote in message
news:SC******** ***********@war ds.force9.net.. .
Hi,

I generate a report using two dates (From and To). I notice if I enter
01/10/2003 that it is interpreted by Access as 10/01/2003 i.e 10th January
rather than 1st October as I intended.

This is the old problem of US date format mm/dd/yy versus dd/mm/yy.
I am stepping through the code and the dates do seem to be ok but when the
report runs I get records earlier than 1st October which is not what I want.
Any ideas on how to allow the use to enter in dd/mm/yyyy format and for that date not to be misinterpreted by Access??
This is driving me quite potty.


I recently had the same problem and was informed Access always compares
dates in mm/dd/yyyy format in the VB SQL code.

I think it works with your International settings everywhere else, unless
otherwised formatted.

Hope this helps
Ken
Nov 12 '05 #2
"Deano" <ma************ @hotmail.com> wrote in message news:<SC******* ************@wa rds.force9.net> ...
Hi,

I generate a report using two dates (From and To). I notice if I enter
01/10/2003 that it is interpreted by Access as 10/01/2003 i.e 10th January
rather than 1st October as I intended.

This is the old problem of US date format mm/dd/yy versus dd/mm/yy.
I am stepping through the code and the dates do seem to be ok but when the
report runs I get records earlier than 1st October which is not what I want.

Any ideas on how to allow the use to enter in dd/mm/yyyy format and for that
date not to be misinterpreted by Access??
This is driving me quite potty.

thanks
Martin


If you get your dates from a form, you can format the date there to be
in English (dd-mm-yyyy) format instead of US format. Either that or
force the format in your query (just wrap the input in the format
function)
Nov 12 '05 #3
In addition to the feedback you've already got (all of which is correct,
BTW), check Allen Browne's "Internatio nal Dates in Access" at
http://users.bigpond.net.au/abrowne1/ser-36.html
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Deano" <ma************ @hotmail.com> wrote in message
news:SC******** ***********@war ds.force9.net.. .
Hi,

I generate a report using two dates (From and To). I notice if I enter
01/10/2003 that it is interpreted by Access as 10/01/2003 i.e 10th January
rather than 1st October as I intended.

This is the old problem of US date format mm/dd/yy versus dd/mm/yy.
I am stepping through the code and the dates do seem to be ok but when the
report runs I get records earlier than 1st October which is not what I want.
Any ideas on how to allow the use to enter in dd/mm/yyyy format and for that date not to be misinterpreted by Access??
This is driving me quite potty.

thanks
Martin

Nov 12 '05 #4
Douglas J. Steele wrote:
In addition to the feedback you've already got (all of which is
correct, BTW), check Allen Browne's "Internatio nal Dates in Access" at
http://users.bigpond.net.au/abrowne1/ser-36.html


OK, thanks all, I think i've got it through my thick skull at last. I've
formatted the date parameters within the SQL as mm/dd/yyyy and this now
*appears* to work ok and is returning the results I expect.

Nov 12 '05 #5
ma************@ hotmail.com (Deano) wrote in
<ZD************ *******@wards.f orce9.net>:
Douglas J. Steele wrote:
In addition to the feedback you've already got (all of which is
correct, BTW), check Allen Browne's "Internatio nal Dates in
Access" at http://users.bigpond.net.au/abrowne1/ser-36.html


OK, thanks all, I think i've got it through my thick skull at
last. I've formatted the date parameters within the SQL as
mm/dd/yyyy and this now *appears* to work ok and is returning the
results I expect.


The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #6

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:94******** *************** ****@24.168.128 .78...
ma************@ hotmail.com (Deano) wrote in
<ZD************ *******@wards.f orce9.net>:
Douglas J. Steele wrote:
In addition to the feedback you've already got (all of which is
correct, BTW), check Allen Browne's "Internatio nal Dates in
Access" at http://users.bigpond.net.au/abrowne1/ser-36.html


OK, thanks all, I think i've got it through my thick skull at
last. I've formatted the date parameters within the SQL as
mm/dd/yyyy and this now *appears* to work ok and is returning the
results I expect.


The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().


While admittedly picky, you can run into problems with dd-mmm-yyyy if your
users don't all speak the same language: unless everything is configured
properly, it may not accept some of the mmm inputs.

The ISO standard yyyy-mm-dd is guaranteed to work, though, or, as you note,
you can use DateSerial.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

Nov 12 '05 #7
Douglas J. Steele wrote:
"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:94******** *************** ****@24.168.128 .78...
ma************@ hotmail.com (Deano) wrote in
<ZD************ *******@wards.f orce9.net>:
Douglas J. Steele wrote:
In addition to the feedback you've already got (all of which is
correct, BTW), check Allen Browne's "Internatio nal Dates in
Access" at http://users.bigpond.net.au/abrowne1/ser-36.html

OK, thanks all, I think i've got it through my thick skull at
last. I've formatted the date parameters within the SQL as
mm/dd/yyyy and this now *appears* to work ok and is returning the
results I expect.


The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().


While admittedly picky, you can run into problems with dd-mmm-yyyy if
your users don't all speak the same language: unless everything is
configured properly, it may not accept some of the mmm inputs.

The ISO standard yyyy-mm-dd is guaranteed to work, though, or, as you
note, you can use DateSerial.


I did look at using Dateserial as I felt i could break up the user's date
input and assign to a date variable via Dateserial. However I was running
into problems as it was requiring integers simply weren't working for me. I
was getting overflow errors and the like.
This is still a concern for me so I will revisit DateSerial.
Nov 12 '05 #8
NOSPAM_djsteele @NOSPAM_canada. com (Douglas J. Steele) wrote in
<j2************ *********@news0 2.bloor.is.net. cable.rogers.co m>:
"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:94******* *************** *****@24.168.12 8.78...
ma************@ hotmail.com (Deano) wrote in
<ZD************ *******@wards.f orce9.net>:
>Douglas J. Steele wrote:
>> In addition to the feedback you've already got (all of which
>> is correct, BTW), check Allen Browne's "Internatio nal Dates
>> in Access" at
>> http://users.bigpond.net.au/abrowne1/ser-36.html
>
>OK, thanks all, I think i've got it through my thick skull at
>last. I've formatted the date parameters within the SQL as
>mm/dd/yyyy and this now *appears* to work ok and is returning
>the results I expect.
The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().


While admittedly picky, you can run into problems with dd-mmm-yyyy
if your users don't all speak the same language: unless everything
is configured properly, it may not accept some of the mmm inputs.


I didn't know that -- I thought it was always an unambiguous
format.

And I don't mean for display here, or for input. Users can use
whatever format they want for display and input, I was only talking
about formatting the date in a SQL string. Are you saying that
Format([value from date field],"dd-mmm-yyyy") might produce
incorrect results?
The ISO standard yyyy-mm-dd is guaranteed to work, though, or, as
you note, you can use DateSerial.


I certainly use DateSerial(), myself, but I've only ever written
one application that was used outside the US.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #9
ma************@ hotmail.com (Deano) wrote in
<or************ *******@wards.f orce9.net>:
Douglas J. Steele wrote:
"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:94******** *************** ****@24.168.128 .78...
ma************@ hotmail.com (Deano) wrote in
<ZD************ *******@wards.f orce9.net>:

Douglas J. Steele wrote:
> In addition to the feedback you've already got (all of which
> is correct, BTW), check Allen Browne's "Internatio nal Dates
> in Access" at
> http://users.bigpond.net.au/abrowne1/ser-36.html

OK, thanks all, I think i've got it through my thick skull at
last. I've formatted the date parameters within the SQL as
mm/dd/yyyy and this now *appears* to work ok and is returning
the results I expect.

The only real solution, though, is to use a non-ambiguous date
format, such as dd-mmm-yyyy or passing your dates with
DateSerial().


While admittedly picky, you can run into problems with
dd-mmm-yyyy if your users don't all speak the same language:
unless everything is configured properly, it may not accept some
of the mmm inputs.

The ISO standard yyyy-mm-dd is guaranteed to work, though, or,
as you note, you can use DateSerial.


I did look at using Dateserial as I felt i could break up the
user's date input and assign to a date variable via Dateserial.
However I was running into problems as it was requiring integers
simply weren't working for me. I was getting overflow errors and
the like. This is still a concern for me so I will revisit
DateSerial.


Passing date values with DateSerial requires that parse the input
date (presumably from a control on a form) with the Year(), Month()
and Day() functions. So, if a user types into a control called
fldDate on frmMyForm:

6/2/2003

you'd pass that in SQL as:

DateSerial(Year (Forms!frmMyFor m!ldDate), _
Month(Forms!frm MyForm!ldDate), _
Day(Forms!frmMy Form!ldDate)

Now, this assumes that the user is putting in the date in an order
that is in accordance with the user's localized date settings. If I
sat down at a machine that was set for UK dates and thought it was
set for US dates, I could get garbled data.

This is one good reason for choosing unambiguous date formats for
display, not just for processing behind the scenes. But that can
lead to other problems.

Another option would be to use a date picker by itself, or a date
picker in conjunction with a text field. Personally, I'd hate to
have to use a date picker, as I like to type. The combination of
the two gives the user the choice while also giving visual
feedback. This means that if I typed 6/2/2003 meaning June 2 on a
PC that was using UK dates, I'd immediately see the date picker
show February 6, and that would tell me that I'd typed it wrong.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3739
by: Dominique Javet | last post by:
Hello, I'm new to php and mysql and I use Dreamweaver MX 2004, so sorry for this "newbie" question... I've found no answer in the forum ... I've a date problem with my formular. In my mysql DB my filed "date" in table "experience" is like this: Y-m-d (2002-07-23). My fied`date` is date, NOT NULL with no default entry My form read well the date data depending the id, (pe. 30.02.2003), but when I submit a new date, I receive as result...
7
8872
by: martini | last post by:
Need help: I am trying to call a DB2 stored procedure that requires parameters: 4x int, date, varchar, int. I use VB6 & oledb. I'm using statements: cmd.CommandText = "{call db2admin.proc(1, 0, 3, 3, '2002-04-22', '0000000', 351)}" and cmd.CommandText = "{call db2admin.proc(1, 0, 3, 3, {d '2002-04-22'}, '0000000', 351)}" and I get following error:
8
2253
by: Ishbel Kargar | last post by:
Since upgrading from old laptop (Windows 98) to new laptop (Windows XP), my mail-merge letters are doing strange things with date formats. For instance, my reminder letter for lapsed subs carries the 'expiry' date as a merge field from the database, and this previously was shown as dd/mm/yy (UK format). Now it persists in showing as mm/dd/yy. I've gone to the Windows Control Panel and made sure that my Regional settings show UK format,...
4
1488
by: Adam Maltby | last post by:
Hi, I need to do a transparent label but with a difference. I have drawn on my form a custom background using CreateGraphics as I needed an ellipse looking fill in a rectangle - so the actual background colour of the form is still set to "control" colour. If I use the background transparent setting instead of seeing my drawn on background and it's gradient i see the forms background colour. If the label was on a solid coloured bit of...
12
1557
by: Aaron Gray | last post by:
Hi, Here's some new code I have not seen before it is used in WikiPedia as well <!--> <style type="text/css" media="screen"> ... </style> <!-->
7
2110
by: creative1 | last post by:
Hello everyone. I am experiencing a strange problem that I can't fix on my own. I think I need expert's suggestions for this. The problem is: I want to print account statement (or any other report) from VB form based on an entered date in the masked fields (dd/mm/yy). I am using one form to display five reports. I send date to display report after formatting it (m/dd/yy). When I enter date range like this in masked textbox (DD/MM/YY) ...
0
3694
by: jans78 | last post by:
Appreciate if you all can help me to solve my Crystal Report problems First, I create some parameters and one of the parameters is Date. I set the parameter for the date is String. For example : parameter 1 - From Collection Date parameter 2 - To Collection Date I should display the results base on this selection criteria : 1) specific date - to enter specific date in FROM and TO field eg 20071227 to 20071227 2) greater or equal date -...
2
1479
by: nicola | last post by:
hello, I need a simple and slight forum write in c# exume me, for the ot, but I'm desperated
40
2012
by: Dave | last post by:
Hello, I'm teaching myself C by working my way through Steve Summit's tutorial (http://www.eskimo.com/~scs/cclass/cclass.html). In one of the questions (assignment 6, exercise 7), you have to write a function to read lines of arbitrary length from the command line, using malloc() and realloc() to allocate the necessary memory to hold the lines. I came up with this: char *getline(char *line) {
0
9795
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9642
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10498
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10540
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10212
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6951
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5623
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5789
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4421
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.