US and UK dates confused - Microsoft Access / VBA

John M

Hi,
I have spent some time trying to sort out a date problem (see mail an hour
or so ago) and am now getting to see what the problem is.

If I present today's date as 07/02/2004 in a search it come up as 02/07/04.
as a consequence I can't get the right record selected. How do i ensure all
references to dates are as in UK? My PC is ... I think correctly set up.

This has driven me round the bend!!

Thanks in advance

John M

Nov 12 '05 #1

Subscribe Reply

1927

Fletcher Arnold

"John M" <jo**@jmawer.de mon.co.uk> wrote in message
news:c0******** ***********@new s.demon.co.uk.. .

Hi,
I have spent some time trying to sort out a date problem (see mail an hour
or so ago) and am now getting to see what the problem is.

If I present today's date as 07/02/2004 in a search it come up as 02/07/04. as a consequence I can't get the right record selected. How do i ensure all references to dates are as in UK? My PC is ... I think correctly set up.

This has driven me round the bend!!

Thanks in advance

John M

If you are designing queries using the Access GUI, then you can write dates
in dd/mm/yyyy and provided your PC is set up correctly, then these will be
interpreted and adjusted accordingly (have a look at the SQL generated for
your query). However, when you are writing code you are simply supplying a
string, and no attempt is made to convert this based on regional settings.

You could simply format the date when you create the WHERE clause

strWhere = "WHERE MyDate = #" & Format(dteDate, "mm/dd/yyyy") & "#"

or use a function such as DateSerial which is great for returning
unambiguous dates.

Fletcher

Nov 12 '05 #2

Bas Cost Budde

John M wrote:

Hi,
I have spent some time trying to sort out a date problem (see mail an hour
or so ago) and am now getting to see what the problem is.

If I present today's date as 07/02/2004 in a search it come up as 02/07/04.
as a consequence I can't get the right record selected. How do i ensure all
references to dates are as in UK? My PC is ... I think correctly set up.

May be; but Access (Jet, actually) uses US date format. Live with that
(I do) and maybe format your dates with format(yourdate , "mm/dd/yyyy")
in the queries.

--
Bas Cost Budde
http://www.heuveltop.nl/BasCB

Nov 12 '05 #3

Lyle Fairfield

"John M" <jo**@jmawer.de mon.co.uk> wrote in
news:c0******** ***********@new s.demon.co.uk:

If I present today's date as 07/02/2004 in a search it come up as
02/07/04. as a consequence I can't get the right record selected. How
do i ensure all references to dates are as in UK? My PC is ... I think
correctly set up.

Access handles dates as UK dates if you write "really stubborn and perverse"
in front of each. If you want a civilized, consistent, widely understandable
approach try standardizing to "yyyy-mm-dd".

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Nov 12 '05 #4

Bas Cost Budde

Lyle Fairfield wrote:

If you want a civilized, consistent, widely understandable
approach try standardizing to "yyyy-mm-dd".

I recognize ISO!
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #5

Similar topics

2861

Javascript dates and their manipulation

by: Peter Pfeiffer | last post by:

I've written several scripts that have "while" blocks which increment a date by one day if the date does not match one of a group of dates. However, sometimes it apparently steps out out the while loop even though my condition isn't met. Will work for a few loops then steps out often. Are there javascript "date" issues? I have also noticed different results between Firefox and Internet Explorer. thanks for any comments,

Javascript

1926

Controlling Dates in Access 2000

by: Cillies | last post by:

Does anyknow how to control dates in acess 2000, the thing is I want to search for records using a form by date. I will be using a between date search box. But i want to enter say todays date but retrieve the results for exactly one month later. E.G. I if I enter 10-05-04 to 14-05-04 I want to see the records for exactly 30/31 days later. I was thinking could I manipulate the "Today'sDate" function + 30days.

Microsoft Access / VBA

2130

Opening subreports with dates

by: Don Sealer | last post by:

I have a report that includes 5 different subreports. I'd like to be able to open this report using a date function (Start Date and End Date). I'd like all five subreports to show the data from those dates. I have learned how to use this start/end function but I can't figure out how to use it with these subreports. Right now the report opens but asks for start/end dates for each of the five reports. Is there a way I can input one set of...

Microsoft Access / VBA

1307

Opening Subreports with dates

by: Don Sealer | last post by:

Microsoft Access / VBA

2082

Dates confusions

by: Lindie | last post by:

The more I read the more confused I get. Too much on dates calulations in the groups. I need to know how often a book has been loaned out over the past year- 52 weeks. My table has Book DateOut Total

Microsoft Access / VBA

1910

Dates from Sql

by: tshad | last post by:

I have a dataset with a date in it. The type is smalldatetime on Sql Server and I am just doing a "SELECT * from x" to get it. All my other fields are fine, but this one gives me an error of: Specified cast is not valid. The data that is coming back is: 2005-05-09 17:52:59.923

C# / C Sharp

29470

SQL Between Dates

by: @sh | last post by:

For some reason guys my SQL string isn't working and I'm probably doing something stupid? SELECT * FROM sol_session SELECT * FROM SOL_Session WHERE DateGiven '10/09/2006' AND < '13/09/2006'

Microsoft SQL Server

2422

PHP + MySQL + Dates = ?

by: Michael Sharman | last post by:

Hi guys, I'm a little confused with dates. Ok, all I want to do is store a date in MySQL as a datetime object and be able to read and format it using PHP. My datatype in MySQL is DATETIME and I'm inserting a date using the MySQL function now(). So far so good. When I var_dump() the value from the database it says it's a string,

PHP

1576

Problem with dates

by: Lewe22 | last post by:

On button click, what i'm trying to do is (in steps) Find Current Date Return First Date of Current Month (Eg: 10/06/2008 would become 01/06/2008) Use in a query to pull out .Reflect in a field on a form called txtMonthID. contains the following sample data: MONTH_ID SESS MONTH MONTH_START_DATE MONTH_END_DATE 07-JUN08 07 JUN 01/06/2008 30/06/2008 07-JUL08 07 JUL 01/07/2008 ...

Microsoft Access / VBA

9672

What is ONU?

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...

General

10437

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10214

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

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...

Online Marketing

10164

The easy way to turn off automatic updates for Windows 10/11

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,...

Windows Server

10001

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

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...

General

5437

Trying to create a lan-to-lan vpn between two differents networks

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...

Networking - Hardware / Configuration

4113

transfer the data from one system to another through ip address

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

C# / C Sharp

3723

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2920

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General