Formatting a date different from the date options available

I am using ACCESS 2007 and would like to format the date as ex: 2009-Nov-19 
in a table.  It would also be nice if this cell would automatically format 
the date to the above format even if the person entering the date typed in 
7/9/1975.  Could anyone help me do this, or can it even be done?
Thanks, Janet B
0
Utf
11/19/2009 8:01:01 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
874 Views

Similar Articles

[PageSpeed] 38

Janet B <Janet B@discussions.microsoft.com> wrote:

>I am using ACCESS 2007 and would like to format the date as ex: 2009-Nov-19 
>in a table.  It would also be nice if this cell would automatically format 
>the date to the above format even if the person entering the date typed in 
>7/9/1975.


As long as the table field is data type Date/Time, you can
do that in a table field, query field or form/report text
box by setting its Format property to a custom format:

	yyyy-mmm-d

Because users should never see the sheet view of a table,
the only place you should need to do that is on a text box
in a form or report.

-- 
Marsh
MVP [MS Access]
0
Marshall
11/19/2009 8:54:04 PM
Reply:

Similar Artilces:

Bank Loan. Due Date Follow Up, Email Reminders
Hi guys. As our business is growing, we were handled a significant amount of money from the bank. The money is collected each time we give the bank an oficial document stating the value of the product we are commercializing. In GP 8, what is the best way to handle these money loans?, they have a termination date and we are obligated to pay the loaned money plus interest. Is GP 8capable of sending reminders? Thanks in advance Daniel, Sounds like this may be handled by the Payables module together with either Revenue/Expense Deferrals or the Scheduled Payments. You should be able to ...

Change date from mm-dd-yyyy to MMM-YY in Access
How do I change a field which contains a date in a mm-dd- yyyy format into mmm-yy format. For Example: Ship Month = 03/01/2004 Convert "Ship Month" value to display "Mar-04" Hi Vince, Set the Format property of the field, and/or the control(s) that are displaying it, to "mmm-yy" On Tue, 20 Apr 2004 12:01:52 -0700, "Vince" <anonymous@discussions.microsoft.com> wrote: >How do I change a field which contains a date in a mm-dd- >yyyy format into mmm-yy format. > For Example: Ship Month = 03/01/2004 > Convert "Ship Month&qu...

Calc date one year from today
Im trying to calculate a date range that will monitor warranty (364 days) from date of purchase order ?? Thanks for the help Ken Hi Ken! Use: =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1) It will even cover the difference between Leap Years and non-Leap years which your 364 has a problem with. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Ken" <anonymous@discussions.microsoft.com> wrote in message news:11d0601c3f626$44145840$a301280a@phx.gbl......

Publisher 2003 Stlyes and Formatting
why does a selected Style revert to a default font after I close and then reopen my document? No, if you answer Yes that you want to save the changes. "janep" <janep@discussions.microsoft.com> wrote in message news:9242DFAB-5D9C-40C5-A8C1-8D339FCB2786@microsoft.com... > why does a selected Style revert to a default font after I close and then > reopen my document? > ...

Conditional Formating 06-04-10
I have developed a rule for a column of data. =$ak$7:$ak$11 =$ak$4:$ak$6 I want to repeat this same rule to adjacent columns, AL thru AS. Then I want to repeat the same rule for the range AK15 AK22 THRU AS15 AS22. Is there a way to repeat this same formula without re entering the same formula throughout the entire workbook? Any help would be greatly appreciated. Actually what this formula will do? -------------------- (Ms-Exl-Learner) -------------------- "Going Crazy with excel" wrote: > I have developed a rule for a column of data. > =$ak$7:$ak$11 ...

Printing Formatted Cells
I have conditional format on a cell to format with say Fill Red ... It displays OK on the screen but doesn't appear when printed? I'm sure it'll be something obvious! Is your print setting for color, or B&W? Long shot, but I'm not sure of any other reason why what-you-see is not what-you-get. -- Best Regards, Luke M "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:C94E101A-9579-4FDB-8AEF-91D59E1EC3A4@microsoft.com... >I have conditional format on a cell to format with say Fill Red ... It > displays OK on the sc...

Taxes Area... Suggestion or are these features available?
Does the Taxes area in Ms Money 2006 allow for State Taxes? Is there any easy interface to enter a periodic paycheck? Peter In order: What do you mean by allow? Calculate? No. Estimate? No. Track and export data useful for preparing? Yes, if used properly. Track withholding? Yes, if used properly. Many of us find it easy. YMMV but we'd be glad to help. "Peter DeSimone" <peterdes@yahoo.com> wrote in message news:435d8eda$1_2@alt.athenanews.com... > > Does the Taxes area in Ms Money 2006 allow for State Taxes? > > Is there any easy interface to enter a p...

Date reference in an IIF statement
The following statement works well. It is the default value for a payment. However, every year I have to go in and change the year. =(IIf(Date()>#3/31/2010#,"$45.00","$40.00")) I would like to have it look at 3/31/(of whatever year it is) so I dont have to reprogram this every year. How do I change the date reference? Thanks in advance. On Wed, 7 Apr 2010 09:16:01 -0700, Presto wrote: > The following statement works well. It is the default value for a payment. > However, every year I have to go in and change the year. > > =(IIf(Date...

How to change the format?
I have some data from a SQL database I have put into an excel spreadsheet, this has produced four columns of data. In the coloumn B is a reference number and in coloumn C is the name the field and in coloumn D is the data. Now there can be upto 12 rows of information for the same ref. number with different field names and their relevant data. I need to do is to reformat the page so that the so that the field names in coloumn C go across the page, but I don't have a clue on how to do this, any suggestions? Tim Hi Tim, Ito sounds like you want to want to transpose your data. For e...

format to calculation cell
I have a spreadsheet which has a weight column. This is exported as 25kg, 1000kg, 10x2kg from another program. I am trying to use this weight column in a calculation. I have inserted a new column and copied the information over and then used the find/replace to remove the KG to use in the calc cell. This works fine for the 25 / 1000 etc, but the 10x2 will not caculate. Is there a way that when i paste the cells that i can get it to change to 20 automatically. ( i presume that the x will need to be a * ?) thanks. -- mdma --------------------------------------------------------------...

HTML formatting #2
One of my clients sends me email in HTML format, but it comes through in plain text. I tried going to Format>HTML, but all of the options under format are unavailable in said email. Can anyone help me? Thanks, A.Funk ...

Formatting cell for state abbreviations
I am unable to format cell to accept ME, the abbreviation for Maine. It continues to revert to a lowercase "E" as in "Me" while other states such as NH, VA and LA maintain their uppercase second letter. What am I doing wrong? Thank you -- LovesArt247 This is a AutoCorrect item for people who mistype the word "Me" (as in me myself) Tools | Autocorrect; locate and remove this entry -- remember it will effect all Office apps OR: after Excel gives you Me, use CTRL+Z to undo best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme rem...

Time formats in Excel 2003
Being in the UK, I obviously want to use the English(UK) date format, so Christmas Day is 25/12/05 rather than 12/25/05. However, this defaults the time format to hh/mm/ss (why, I do not know, since I can assure American readers we're not normally that precise), so I have to reset it English(US) to get hh/mm, which resets the default date format to mm/dd/yy, so I have to change it back the next time I want to format a cell as a date. Any way round this? It's only a minor problem but it's irritating me. Steve Stephen, If you set up your own custom format with hh/mm, it w...

different versions
What are the differences between the Home version of PowerPoint and the "full" version? No difference I Believe. The wording 'home' is referring to 'office' and now powerpoint. As in "office *home* and student edition" - normally includes, word, excel, powerpoint, onenote cheers TAJ Simmons Powerpoint Wizard awesome graphics for powerpoint http://www.PresentationPictures.com powerpoint pictures, tutorials, free samples... "SScheck" <SScheck@discussions.microsoft.com> wrote in message news:91EAD4B2-1F46-46D0-8A73-426C...

OFFSET for multiple columns & combobox format
Is there a way to have an OFFSET range of two columns? I have been using multiple columns for my X axis values with non-dynamic charts. Now i'm designing a dynamic chart, but Excel will not let me select two columns (month/wk) for my X axis. Another simple question > how can I format a comboBox text in a chart? Thanks. -- when u change the way u look @ things, the things u look at change. To create an offset for multiple columns, first define a range name that makes up those columns. For example, the name "XAxisCol" covers columns A and B and refers to the formula / r...

Payment Terms for specific date
Hi! Our client needs to have Payment Terms for specific date. For example, they want it to be Jun 1, 2009 They sell product but customer doesn't have to pay until June of 2009 Can this be accomplished using GP functionality? If not, what is the best way to customize/make it work? Thank you, Ivan Not sure how you would do this without a customization, but here is what you can try. I assume your customer is running 6-months no interests, no payments type thing for the month of December (again, just an assumption). 1) You could conceivably setup a payment term called JUNE012009,...

Duplicating data according to Dates
Well, that's the best description I can think of anyway. I need help with a solution. I have a set of data (vehicles) that I have managed to extract a complete history for. This dataset shows the month and year of every inspection that was completed on the vehicle according to the location that it was at when the inspection took place. Here is an example: Vehicle -- Location -- Year -- Month Car1 -- Location1 -- 2006 -- 9 Car1 -- Location2 -- 2006 -- 12 Car2 -- Location1 -- 2007 -- 2 Car2 -- Location2 -- 2006 -- 11 Car2 -- Location3 -- 2007 -- 5 Car3 -- Location1 -- 2006 -- 9 My goal is...

Need To Add VBA Tools Like A Date Picker
I have written a couple of VBA projects for my company. Most of the computers run them all fine but some do not. About half of the computers are running Windows XP Pro w/ Office XP Small Buisness and the other Half are running on Windows 2000 Pro w/ Office 2000 Small Business. My problem is that some of the office 2000 computers do not have Date Pickers in there VBA so they get errors when they try and run the programs. It says something like error: project cannot be found. I want to know where I can update my VBA tools from. Any help would be greatly apprecitated. Thanks, Mark ...

Windows Update optional updates doesn't see
This is a multi-part message in MIME format. ------=_NextPart_000_0049_01CB1560.CEB60470 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit Hello there, for some time I can no longer view the optional updates. Can you tell me a solution? S.O.: Windows Vista Home Premium Service Pack 2. Language: Italian. I attach a screenshot. Thanks. Fabiano Liberatori ------=_NextPart_000_0049_01CB1560.CEB60470 Content-Type: image/jpeg; name="Windows Update Error.jpg" Content-Transfer-Encoding: bas...

Typing to Nullable (Of Date) vs typing to Date
This is just an example of the crux of the problem: CType(CObj("11/09/2009"), Date) Returns date object as expected. CType(CObj("11/09/2009"), Nullable(of Date)) In QuickWatch returns the string. At runtime crashes out with invalid Cast. Can someone explain the different behaviour to me please? I don't see why one works and one doesn't. Also assuming I have a gridview returning IOrderedDictionary, how do I convert blanks strings to nothing and non-blank string to date? Do I have to resort to IIF? Given that Nullable(of Date) can't cope with di...

referencing multiple cells when data is organised differently
I have a list of products with sales targets on one tab. The products go vertically down the page. On my second tab, the list of products goes horizontally across the page (the other way round from the first tab). Is there a way I can reference the first cell and copy the formula in such a way that I can quickly link the other cells. Its possible to 'drag' the formulas down or across when the original data goes in the same direction as the new one but I cant figure out how to quickly link multiple cells if the data is organised diffenrently. Thanks for any help! ...

Conditional Formatting Query
Hi I'm facing a problem from last month but I'm not able to put conditional formatting with Icons. If somebody can help as its really urgent for me !!!!! I have values for two months - Current and Previous. If current month's value is closer to zero than previous month's value then i want to show a green arrow. If current month's value is away from zero than previous month's value then i want to show a red arrow. Though these values are positive and negative both, i need to apply conditional formatting on absolute values. e.g. previous Cur...

Why does Excel Copy and Paste change dates from the original when.
When copying and pasting dates, Excel pastes strange dates. Where do these come from and how do I stop this from occurring? Copy and paste from where to where? Perhaps the "dates" you are copying are Text and not real dates. Give an example of a "strange date". Gord Dibben Excel MVP On Tue, 25 Jan 2005 13:27:04 -0800, "Excel changes dates in copy and paste" <Excel changes dates in copy and paste@discussions.microsoft.com> wrote: >When copying and pasting dates, Excel pastes strange dates. Where do these >come from and how do I stop this from o...

Help! different display size: excel on laptop and desktop #2
mickedog: Thank you! But it's not convenient to set up everytime you switch your computer. I'll try screen resolution, though. Thanks again -- nune ----------------------------------------------------------------------- nunee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1464 View this thread: http://www.excelforum.com/showthread.php?threadid=26266 ...

Converting dates to a number
What formula could I use to convert a date of birth into an age?, e.g., 06/25/71, 33. Yes, I know I can just subtract but is there a formula that can do it? Marguerite A very rough way of doing so, if you don't need to count leap years ... =Int((Today()-A1)/365)&" years old" Where A1 is the cell which houses your date. Note this MUST be an Excel recognized date. To check if it's an Excel recognized date, look here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=323 HTH, and regards, Zack Barresse "Kathy" <Kathy@discussions.microsoft.com> wr...