Here's my problem. I have a person in New Zealand that I'm having problems with the date. Everything works fine except I have a date field in one form that updates another table with the date. I'm using an Update statement to update the table, if I look at the date format that is being sent it is in the correct format DD/MM/YYYY but when I bring up the other table the date is in the format MM/DD/YYYY If I enter the date into that table it comes out with the correct format. I understand how Access handles dates but I don't know how to fix this problem. Hopefully someone can point me in the correct direction.
Thanks
5 1963 NeoPa 32,579
Recognized Expert Moderator MVP
Why don't you explain what you want help with. I expect the SQL of the UPDATE statement wouldn't hurt either.
You say you understand how dates work in Access, but I have to say that such an understanding isn't evident from what you do say. I'm hearing much confusion, that I think we can certainly help with. We need a question though, to start with.
Sorry about that, in my program the user (from New Zealand) can either select a date from a drop down calendar (I'm using clsmonthCal written by Stephen Lebans) or enter the date, the date is saved in a variable VBWBDate this variable show the date in the correct format DD/MM/YYYY. This date is then used to update another table using the SQL update statement. - "Update BanquetMaster set Banquet_amount = " & VNewfee & ", Banquet_date = #" & VBWBDate & "# where banquet_name = '" & VMatTitle & "'"
when you look at the table BanquetMaster the Banquet_date has the correct date only the format is MM/DD/YYYY. Banquet_date is set as short date. I hope this explains my problem better, if not I'll try again.
Thanks
NeoPa 32,579
Recognized Expert Moderator MVP
This is fine Tom. I know where you're coming from.
This problem is fundamentally down to the confusion of how dates are stored and how they are formatted when displayed. Assuming a DateTime field or variable is used, dates are always stored as a double-precision value. This doesn't change whichever country or defined locale your PC is set up for. How these values are formatted by default though, certainly does change depending on the locale. Unfortunately, when dealing with SQL (creating a SQL string to execute from within VBA code), putting the date in involves formatting in one way or another. The standard for SQL (so this is not open to variation across the world, unlike the display format) is m/d/y - just like they use in the US (See Literal DateTimes and Their Delimiters (#)).
What this means is that, to create professional, portable, code you need to format the date value explicitly when adding it into a SQL string (No matter where in the world you are writing your code). If you were to use the following, then your problem would disappear : - strSQL = "UPDATE [BanquetMaster] " & _
-
"SET [Banquet_amount] = " & VNewfee & ", " & _
-
" [Banquet_date = #" & Format(VBWBDate, "m/d/yyyy") & "# " & _
-
"WHERE ([banquet_name] = '" & VMatTitle & "')"
Thanks very much. That seem to do the trick, I learn something every day.
NeoPa 32,579
Recognized Expert Moderator MVP CD Tom:
I learn something every day.
That's good news for us Tom. We love that people are learning (BTW I continue to learn new things here myself too).
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: paul |
last post by:
Hi everyone,
I'm having problems displaying a date in the correct format and I was
wondering if anyone knew how to help.
I'm using pear to extract a date from our back end database (the date
is in DATE_FORMAT_ISO_EXTENDED), and I was to display the date in
"Wednesday, February 23rd 2005, 11:04" format.
I'm using something like:
|
by: George |
last post by:
I have two tables and want to find the Maximum date for a given GIN.
I have been able to produce the result in Sybase but I am having
problems in Oracle.
Example of my query in Sybase.
create table #temp1 (
groupcode char(10),
loc_acc_no int,
gin int,
|
by: fak |
last post by:
I have a workorder entry form. There is a table that contains
workorder information and another table that contains work dates that
are closed (fully booked). When the workorder form is being filled
in, I need the workorder date field to check if it is a "closed" date
(one listed in the other table). I was trying to use an AfterUpdate
on the workorder date, but I am uncertain how to lookup that field in
the other table and not allow work...
|
by: Colm O'Hagan |
last post by:
Hi there,
I having a problem with a database I'm setting up, I would be
delighted if someone out there could help.
The database I'm setting up is a task register datebase, it will be
used to create work schedules for workers in a metal work shop, based
on the tasks required to make a part and the tasks required to make
the subcomponents of the part.
|
by: Flipmode |
last post by:
I am fairly novice at VB and access in particular and I am having
problems trying store a date into database field.
This is the scenario I am faced with.
I want to when click on a pause button and store the current date time
in a pause time field then when it is then unclicked it is to add the
time that elapsed to a total pause time field and the reset the pause
time back to Null.
| |
by: Lonewolf |
last post by:
Hi all,
I'm having difficulties passing data back to managed class from my
native class when the data is generated from within a native thread in
the native class itself. I will give the following runtime error,
" Attempting to call into managed code without transitioning out first.
Do not attempt to run managed code inside low-level native
extensibility points, such as the vectored exception handler, since
doing so can cause corruption...
|
by: tarafinlay |
last post by:
Hi all,
I am new to access and am finding it a bit unintuitive having worked
with SQL server in the past... And I am in a bit of a hurry because my
employer wants me to crank something out which at first seemed like a
piece of cake but I realize now I should have done a little more
research before I got started. Here is the issue:
I have a table with data relating to our pastry products. I built a
form with multiple combo boxes that are...
|
by: rcamarda |
last post by:
I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:
PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
|
by: Beeker |
last post by:
I have a table called 'RawData' that collects production data. We run a
report on this data everyday to see the performance of each employee. I
have another table called 'tblStandards' with standards for each area
and an effective date of the standard. Any production numbers before
the effectivity date will use the older standard and any on or after
the date will use the newer standard. My problem is how to write a
formula in a query that...
|
by: wvmbark |
last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months.
Every day we incur numerous service problems “Events”. Each morning we have a global conference call where events which occurred within the previous 24 hours are discussed. Prior to the call, an analyst has to review these events and provide a report, ‘The Morning...
|
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...
| |
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...
|
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...
|
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,...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |