DataBase - changing date format
Asked By Jean-Paul
24-May-08 01:49 PM
Hi,
I have a table where dates are stored in foramt: dd/mm/yyyy
Now I have difficulties running SQL or filters.
I was told I should use the mm/dd/yyyy format.
How can I change the format of exsisting records
Thanks
JP
Accounting
(1)
DateTimes
(1)
RBrandt
(1)
Belgium
(1)
Jean
(1)
PFFFFFFFFFF
(1)
Inputters
(1)
Foramt
(1)
Rick Brandt replied...
Your existing records are not stored with a format. DateTimes are stored as
numbers. Right now as I post the DateTime (according to Access) is...
39592.5571296296
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Evi replied...
You don't need to do this, Jean-Paul. You can change your format when you
actually run a sql statement or a query.
Format([YourDateField]),"0") works very well in code (for instance, if you
are filtering via a text box in a form) so that you can treat the field as
if it was a number.
In Sql statements themselves, turn the date around so that you type
If filtering directly in queries, Access seems quite happy with what you
have in your Regional Settings ie here in the Uk, I type the 5th of March as
a peek at the Sql statement shows that it has been obligingly converted to
If your region is not accustomed to the mm/dd/yyyy format, then it will be
easy for your inputters to make mistakes when entering dates into the tables
or cause confusion when they are reading data.
Evi
Jean-Paul replied...
Thanks Evi for your kind reply...
I finally arrived solving the date problem in the SQL statement...
PFFFFFFFFFF how confusing
JP, Belgium (storing a date as dd/mm/yyyy)
Tony Toews [MVP] replied...
Actually the dates are displayed in dd/mm/yyyy format according to
your systems Regional Settings.
SQL statements require that the dates be either completely unambiguous
or in mm/dd/yy, or mm/dd/yyyy format. Otherwise Access/Jet will do
it's best to interpret the date with unknown results depending on the
specific date it is working with. You can't assume that the system
you are working on is using those date formats. Thus you should use
the logic at the following web page.
Return Dates in US #mm/dd/yyyy# format
http://www.mvps.org/access/datetime/date0005.htm
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
3D = 3D = 3D = 3D = 3D = 3D = 3D = 3D = 3D = 3D = 3D = 3D = 3D Advanced Financial Accounting, 9th Edition 2011, Baker, Christensen, Cottrell, Instructor Manual & Solutions Manual Advanced Financial Accounting, 9th Edition 2011, Baker, Christensen, Cottrell, Test Bank Advertising and Promotion: An Integrated Marketing Communications Ferrell, Test Bank Chemistry for Environmental Engineering and Science, 5th Edition, Sawyer, Solutions Manual College Accounting, 13th Edition 2012, Price, Haddock, Farina, Instructor Manual & Solutions Manual College Accounting, 13th Edition 2012, Price, Haddock, Farina, Test Bank College Accounting: A Contemporary Approach, 2nd Edition 2012, Haddock, Price, Farina, Instructor Manual & Solutions Manual College Accounting: A Contemporary Approach, 2nd Edition 2012, Haddock, Price, Farina, Test Bank Computer Accounting with QuickBooksr 2011, 13th Edition 2012, Donna Kay, Instructor Manual & Solutions Manual Computer Accounting with QuickBooksr 2011, 13th Edition 2012, Donna Kay, Test Bank Construction Contracts, 3rd Edition 2011
and users want to start over at 1000 due to problems with number spaces in accounting system. I could start a new database, but the problem is with jobs that are thank the FTC of the USA for making this disclaimer possible / necessary. Pam, Does the accounting system "link" back to the access database? In other words, how does a user on the accounting system look up an invoice number? Is it done manually or is done through software a dumb question, but can you increase the size of the job number on the accounting software? How many open jobs do you have? Have you thought about MOVING (copy to ahead of you. I think that one of the big issues here is how the accounting system interacts or does not interact with the Access database. What limitations does the accounting system impose on the Access database other than a 4 digit job number? Also, is the job number in the accounting system a 4 DIGIT field or a 4 CHARACTER field? If it is a 4 some other options availalbe, but it does involved a conversion of the fields in the accounting database. Is that an option? If will be helpfull to know that the limitations are
I am looking for help in knowing the right questions to ask. Situation: Main Office accounting software data lives in a SQL Server database in another city. Local office connects to the accounting suite (as I understand it) through a VPN tunnel. Historically, daily sales and delivery data sale was hand carried to the local office, where it was manually entered into the accounting system. Recently, one of the points of sale received a software upgrade that makes it download a .csv file of the daily sales info, which can be imported into the accounting system. Apparently, there is no validation step before the import. If the point of sale created a new customer (or item) that the accounting software does not know about, the import dies a violent and messy death. (The obvious I accept as a given that I will not ever get authorization from the IT / Accounting gods to get at the master data directly. I gather that it is possible to nomail feeds the bit bucket :-) Access Discussions James A. Fortune CDMAPoster (1) SQL Server (1) Accounting (1) Oracle (1) Office (1) Excel (1) SQL Server database (1) Situation Main Office accounting software (1) I have a similar situation, including a SQL Server database in another city
a problem, but I would definitely check with any tax authority about this change in accounting method. - - Leslie Vail, CPA, MCT, MVP MCBSP-Application for Microsoft Dynamics GP MCBSP-Installation and not care about changes in inventory valuation methods, however they do care about changes in accounting methods (for example from accruals to cash or viceversa). Best regards, - - MG.- Mariano Gomez, MIS for nearly a dozen years. The applicable code section and definition of what constitutes an accounting change is very long and covers many more areas than merely cash and accrual. Below the 'definition' section: (notice th OR after the first clause) .01 Change in method of accounting defined. (1) Section 1.446-1(e)(2)(ii)(a) provides that a change in method of accounting includes a change in the overall plan of accounting for gross income or deductions, or a change in the treatment of any material item or the taking of the item as a deduction. In determining whether a taxpayer’s accounting practice for an item involves timing, generally the relevant question is whether the practice permanently year in which income is reported, it involves timing and is therefore a method of accounting. See Rev. Proc. 91-31, 1991-1 C.B. 566. (2) Although a method of
09 - 12 / 31 / 09) my previous balance should be $1, 800.00 Access Queries Discussions Accounting (1) TransactionDistrictID (1) TransActionAmount (1) DistrictID (1) Database (1) Report (1) PreviousBalance (1) TransactionDate (1 If Not IsNull(varDate) Then intYear = Year(varDate) If intStartMonth = 1 And intStartDay = 1 Then ' accounting year is calendar year AccountingYear = intYear Else ' if date is before start of accounting year then ' accounting year starts in previous year If varDate < DateSerial(intYear, intStartMonth, intStartDay) Then AccountingYear = intYear - 1 intYear Else ' date is after start of accounting year so ' accounting year starts in current year AccountingYear = intYear & "-" & intYear + 1 End If End If End If End Function This returns the accounting year for any date by passing the date and the month and day on which the accounting year starts into the function. Then use a query along these lines: SELECT TransactionDate, TransactionAmount