Custom Date Format DD MMM YYYY

I need to know how to create a custom date format so the date is DD MMM YYYY with the MMM being all caps. I can do the basic DD MMM YYYY but the three letter month is in proper case. How do I force Excel to show my month abbreviation in all caps?

Help would be appreciated, this is for a novice user so I'd like to give him something simple or some code he can run with out knowing VBA. I'm happy to write it for him but execution must be simple.


Post your answer or comment

comments powered by Disqus
Hi,

I have got a date created as a string (date)

the output format of my code in VBA is dd/mm/yy ie 01/02/2004

I want to be able to get this into a dd/mmm/yyyy format ie 01-feb-2004

how can i do this?

I know i can extract the month using month(date), how can i have this in a aphabetical format and not numeric?

Sorry if this is v easy

Thanks

Ian

Hi everyone

My query is exporting date data to csv showing some times of day, whereas I just want the whole number (day only, no times), to go out on the format dd/mm/yyyy. In Excel I'd use the Int() function, but its use is eluding me in Access.

Can someone pls help me to format the final output to dd/mm/yyyy only usng the query, as I'd prefer not to tinker with the source data.

Regards

Ward

I have files with a date format dd/mm/yyyy. Using the FileDateTime I can obtain these dates. However I need to change the format of the dates to yyyy/mm/dd and transfer the new format to a field in a table.

How can I change the format?

I have tried: -

DateValue = Format(FileDateTime(strFilePathFileName1), "yyyy/mm/dd")

however this still returns dd/mm/yyyy?


[This message has been edited by aziz rasul (edited 12-14-2001).]

Hi

I upload sharepoint documents from a server to a sharepoint based in USA. The server is set to UK/EU date format dd/mm/yyyy and I have now set the sharepoint regional settings to UK date format.

In my excel I have say a range of dates in August

01/08/2012
02/08/2012
03/08/2012
04/08/2012
05/08/2012
06/08/2012
07/08/2012
09/08/2012
10/08/2012
11/08/2012
12/08/2012

For these dates when they go from the server copy to the upload on to sharepoint it is swapping the date from dd/mm/yyyy to mm/dd/yyyy so if I look at the dates in the filter in Excel they are showing as:

2012 > Jan > 08
2012 > Feb> 08
2012 > Mar > 08
2012 > Apr > 08
2012 > May > 08
2012 > Jun > 08
2012 > Jul > 08
2012 > Sept > 08
2012 > Oct > 08
2012 > Nov > 08
2012 > Dec > 08

Any dates with a day larger than 12 it shows the date correctly - dd/mm/yyyy such as:

13/08/2012

And in filter will be shown as:

2012 > Aug > 13

How can I "lock" - if that is the right word - the dates with months less than or equal 12 to dd/mm/yyyy format?

I know that if there is a day

I have a database with a number of date fields every single one has been specified to (short date format) and are on machines with UK dates format dd/mm/yyyy. However after using the database for a few weeks the dates change to US format. For no particular reason and I cannot trace it to anything.

I have changed all of the code support the forms to Date() instead of Now() but this has made no diffrenece as users are still reporting the dates have changed to us format.

The database is running on a standard NT server with the desktops using windows NT. Users cannot access/amend the date format, therefore it is always english. But I cannot determine why thiis happening, and it is happending to all users.

Any help would be appreciated

Pete

Hi
I have 2003 Access Dbase from which some of the fields must be exported as comma deliminated txt and email to a repository.

I need the end user to see DOB, date format dd/mm/yyyy (15/06/1959) but it must be exported as ,15061959,Town,State....etc

Now I have formatted Table, Form & Query Fields (dd/mm/yyyy) and then carefully chosen the text export options...Removing the / date deliminator etc and saved the export format & kind. Included the correct path etc in to a macro and used Notepad.exe to display.

When it displays... as follows...15061959 0:00:00, IT ADDS A TIME?????

Short term workaround I have end user entering 2x IE 01/01/0001 & 01010001 as seperate text field. If I format date as ddmmyyyy then it displays this way for end user of course and not acceptable for other reports

Is there some way I get around this...or code/query/function I can use to convert for export purposes??????

Hi

I"m re-using some code i used a long time ago (Access 2000/2003), for a dynamic search. I can search for a date (which in the UK and in my DB is dd/mm/yyyy) by doing:


	Code:
	If InStr(Me![SrchEntryDate], "*") > 0 Then
        where = where & " AND [EntryDate] like '" + Me![EntryDate] + "'"
    Else
       If Not IsNull(Me![SrchEntryDate]) Then
           where = where & " AND [EntryDate] like #" & Format(Me![SrchEntryDate], "mm/dd/yyyy") & "#"
        End If
    End If

And i can search between two date ranges by doing:


	Code:
	If Not IsNull(Me![SrchEntryDateEnd]) Then
    If Not IsNull(Me![SrchEntryDateStart]) Then
        where = where & " AND [EntryDate] between #" + _
        Format(Me![SrchEntryDateStart], "mm/dd/yyyy") + "# AND #" & Format(Me![SrchEntryDateEnd], "mm/dd/yyyy") _
        & "#"
    Else
        where = where
    End If
End If

The only issue is that i can't remember why i used 'mm/dd/yyyy' instead of 'dd/mm/yyyy' - and either/both seem to work just fine?

Does anyone know if it's OK for me to go ahead and change this to dd/mm/yyyy? I can remember that i used mm/dd/yyyy for a reason, but i'm wondering if Access 2007 means that i don't have to do this.

Many thanks!

Eddie

I'm building a report that requires me to concatenate several fields plus additional words, etc. But not all of the fields are the same data type. I have the date formatted the way I want it in a date/time field in one table (dd mmmm yyyy), and I want to append that date into a text field in another table, maintaining the same format.

Now, when I do a normal append or update query, it appends as medium date format (dd-mmm-yy). If I change the field type in the original table from date/time to text, it also shows up in medium date format.

Any ideas on how to make this work, or other options for concatenating fields with different data types?

edit: I don't want to change the data type of the original field to text.

Hi,
I have created a custom date and time format (MM/DD/YYYY HHMM) I am trying to get the default value to put the date and time when someone puts in a new record.
I have tried the Now() in the default value and it gives me the date and time but the only problem is that it gives me time in HH:MM:SS and AM/PM. Is ther a way to have it just the HHMM and military only.

Thank you in advance
Al

I apoligize for the inconvience I need the date and time together example mm/dd/yyyy - hhmm. (10/11/2002 - 1425)

thanks
Al

I am a newbie in Ms Access, i need help to solve this problem.

I am creating a database to store the customer info, then now, i need to create a report which allow me to printout the data created on certain date. Before that, i need to select some fields from my master table and stored into a temp table. However, i am having problem in date format when i write the SQL code.

I stored the create date in medium date (dd-mmm-yyyy).
Then I have a form which allow me to keyin the report start date and report end date (dd-mmm-yyyy). But the date show as (dd/mm/yyyy). Can someone please help me to check on the code on below? TQ!

Private Sub runReport()

Dim rs1 As DAO.Recordset
Dim sqlSelectGL1 As String
Dim sqlInsert As String

Dim startDate As String
Dim endDate As String

Dim Branch1 As String
Dim UserName As String
Dim AgentName As String
Dim DateCreate As String

endDate = Format(CDate(txtEnd), "dd-mmm-yy")
startDate = Format(CDate(txtBegin), "dd-mmm-yy")

MsgBox endDate
sqlSelectGL1 = "SELECT Branch, UserName, IntermediaryName, CreateDate FROM tblMaster WHERE CreateDate >= '" & startDate & "' and CreateDate

Hello All,
I have a nifty automatic pdf printing scheme set up using pdf995. I use criteria from the reports themselves to name the output files for the pdf. On one of the reports they want the date to be a part of the filename. When I try to base it on a date field on a form (the form where the reports are being printed from) the print to pdf fails because the date format is 05/11/2006. I cannot have the slashes in the filename. I formated the textbox with the date to dd mmm yyyy so it shows up formatted correctly but I gues when it is pulling the info for the pdf it pulls the standard slash format thus the pdf will not print. So, how can I make it so access or better yet pdf995 recognizes the date in a filename friendly format? VBA, or other? Thank you all for your help.

Greg

Hi,

I've written some vba to set a combo box value list as last month or the month before. Now I'm trying to format it & am having issues.

I've set the combo box itself to be mmm yyyy & once a selection is made, it displays like that, but the dropdown is still showing eg 01/02/2011. What do I need to do to get it to show in the dropdown as mmm yyyy while still being set as a date (needed for later)?
This is my code (please don't laugh!!!! I try!):

Dim myDateArray(1) As String, myDateList As String, i As Integer, m As Integer, f As Integer, subi As Integer
If Int(VBA.Year(Now()) / 4) = VBA.Year(Now()) / 4 Then
f = 29
Else
f = 28
End If

For i = 0 To 1
If i = 0 Then
subi = 13
Else
subi = m
End If
m = VBA.Month(Now())
myDateArray(i) = CStr(VBA.DateSerial(VBA.Year(Now()), m, 1) - Choose(subi, 30, 31, 31, f, 31, 30, 31, 30, 31, 31, 30, 31, 0) - Choose(m, 31, 31, f, 31, 30, 31, 30, 31, 31, 30, 31, 30))
Next i
myDateList = Join(myDateArray, ";")

Me!cmbYr.RowSource = myDateList


Thanks

Hi guys, while helping me with some stuff on my DB, hayley pointed out wierd quirky thing in access with regards to the UK date format

Originally she told me i was using US format - but in the table it had the date format in dd/mm/yyyy

however the # symbol in the coding is representing US dates ie try searching for 12/06/03, guaranteed it is more likely to read this as 6/12/03.

She suggested that i remove the #'s but then i got nothing... she explained

"Regarding the US fromat - I noticed when takin gout the # symbol it didn't return dates because VB reads it as 12/06/3 instead of 12/06/03 in otherwords it misses out the 0 therefore doensn't display the records it should. Must be something else required in the coding. The only way I've got round this is to use Medium date - see the example I sent you! "

Many thanks to hayley for that. I couldn't imagine that sneeking up on me, because the only dates which have a problem are ones which have the first two numbers of the date (xx/mm/yyyy/) which are smaller than 13!!!!

So does anyone know how to fix this, without using medium date format? It's not a big deal to use medium date format but a) i'll have to change things and b) it's pretty wierd stuff!!!

Cheers - ask for any more info if needed!!

Edd

When mail merging from an Access 2000 database into Word 2000 date fields are shown as dd/mm/yy in the merged documents.

I would like them to display as mmm-yyyy in these merged word documents.

The date field is set as medium format in the database table and displays as dd-mmm-yyyy

How do I get the query used in the mail merge to force the date to be displayed as mmm-yyyy in my merged documents?

Hi,

I am creating a data entry program for data being collected from Bangalore, India. My problem is the date formats in India are "DD/MM/YYYY" as opposed to the US format of "MM/DD/YYYY". When I apply the dd/mm/yyyy format to my table design and also to the text box on the form, the format works except for say May 2, 2006, which would be entered as 02/05/2006. As soon as I move to another control Access flips the days and months to 05/02/2006. If I modify the format to dd/mmm/yyyy Access tells me the date is 05/Feb/2006. How can I get Access to stop 'helping' me and only take the data as it is entered and formatted, short of splitting the date fields.

Sidebar-Personally, I always feel that dates should be entered with a three character month, because of confusion that can be generated when using strictly two digit days and two digit months when both values are 12 or less.

I would like to automatically set the format of the data entered by the user or the data read from the recordset to the date/time format dd-mmm-yyyy hh:mm:ss.

Can I acheive this automatically by a VB code statement without using the format statement every time I read the data.

Tried the advanced search but no joy.
I have a form that returns transactions between 2 dates using SQL - the 2 textbox controls are named txtStDte and txtFinDte, these are unbound and formatted dd-mmm-yyyy and it all works beautifully. The issue is when a user selects either control (Access 2007) shows the general Date/Time with the time being the PC clock time.
Does anyone know how in the hell to stop them doing that and only show the date without the time; having to bind them to a table with a set date format seems well over the top

Thanks in advance

Kind Regards

Jonathan

Hi,

I have a problem filtering date ranges in a subform. On my main form (called frmStore) I have two unbound date fields called txtStart and txtEnd. The user enters Start Order Date and End Order Date and presses return (the dates are entered dd/mm/yy format). These dates’ ranges are then filtered in the subform (called frmsStore). Sometimes this works correctly and sometimes it doesn’t.
When it doesn’t work and I re-enter the dates in US format e.g. mm/dd/yy, it then works.
I have all my date fields in my database set to UK format (dd-mmm-yyyy) but Access must be converting it back to US format.

In the table tblOrders, the OrderDate field is set to Date/Time and the format is set to dd-mmm-yyyy.
In the form, frmStore, the two unbound date fields txtStart and txtEnd the format is set to Short Date.

On Lost Focus event procedure of txtEnd the following code is attached:

Private Sub txtEnd_LostFocus()
Me.frmsStore.Form.FilterOn = True
Me.frmsStore.Form.Filter = "[OrderDate]>=#" & CDate(txtStart) & "# AND [OrderDate]

I want to convert dd/mmm/yyyy to MMM/YYYYY in my query. The audit date format in Table is 99/99/0000;0;_
I created; Inspection Due: [Audit Date]+364 in the query. I want to show the next inspection is due in 1-year but only show the Month and Year.
I tried to have the table show the change but can't get it to work there either.

I have a text box in a report with the following in the Control Source Property:-

="Please find below details of collections made during the period " & Forms!criteriafrm6!Combo0 & " and " & Forms!criteriafrm6!Combo3 & " inclusive."

Combo0 and Combo6 are input by the user in the format ddmmyy to select the date range to print. The report prints out the date in format dd/mm/yyyy.
How do I get the report to print the date in Long Date format (eg. 03 February 2009)?

A solution or link would be grateful.

Ta

Hello all,

me again! Got a very strange error today when trying to filter on a form that maybe someone has seen before.

I have a form that displays a list of outstanding work items for a user. They have the ability to filter these items based on, among other things, the date that they came in.

So I have 2 unbound text boxes for the date range itself, both set to Short Date format (UK format - dd/mm/yyyy). The date field on the table and form is the same format.

So I have the following code to implement the filter, and if I type in dates like, for example, 15th June 2009 to 26th June 2009 (in the Short Date format) it all works ok. However if I type in a single digit day (i.e. anything from the 01st to the 09th of a month) it converts the date into US format for the actual filtering. I have a msgbox set up to check the dates as I filter and it seems fine there, but the filter itself is incorrect.


	Code:
	        'Generate the SQL code for the filter
        DateCriteria = "[Start Date]" & " >= #" & Format(Me.FromDateFilter, "dd/mm/yyyy") & "# AND " & "[Start Date]" & "

Hi all

I have been having a problem trying to enter a date in a table using vba. I have fixed it now, but can anyone shed any light as to why my two examples use the date format differently but both work???

I know that vba uses the american date format but surely, it should remain the same no matter what type of procedure you are running.

Please see my examples.

Using the db.Execute method I have had to specify the date format as "mm/dd/yyyy" and also use the #

	Code:
	Sub dateEntry1()

  Dim db As DAO.Database
  Set db = CurrentDb
  
  ' date is entered in the format dd/mm/yyyy
  myDate = InputBox("Please enter date", "Date required")
  
  db.Execute "Update tblDate Set runDate = #" & Format(myDate, "mm/dd/yyyy") & "#", dbFailOnError
  
End Sub

Using a recordset I just have to use the expected format of "dd/mm/yyyy" and no #

	Code:
	Sub dateEntry2()

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  
  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblDate")
    
  ' date is entered in the format dd/mm/yyyy
  myDate = InputBox("Please enter date", "Date required")
  
  rs.Edit
  rs(0) = Format(myDate, "dd/mm/yyyy")
  rs.Update
  
  rs.Close
  db.Close

End Sub

I thought this may be different for just recordsets, so I tested using a SELECT statement. I had to use the "mm/dd/yyyy" format and #... Confused!


	Code:
	Sub DateFind()

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  
  Set db = CurrentDb
  
  ' date is entered in the format dd/mm/yyyy
  myDate = InputBox("Please enter date", "Date required")
  
  Set rs = _
  db.OpenRecordset("SELECT * FROM tblDate WHERE runDate = #" & Format(myDate, "mm/dd/yyyy") & "#")
   
  MsgBox rs.RecordCount
      
  rs.Close
  db.Close
  
End Sub

Why does it do this or is there something I need to do so that I can just use the same format throughout my code.

Thanks

Hi all - I have an extremely frustrating problem with date formats in Access/VBA and hope you can help!

We generate a large number of reports here, and often end users will be looking at dates in particular. I just about been able to ascertain that regardless of what date it is, Excel always uses the MM/DD/YYYY format.

Access and the associated VBA we generate for it is proving less reliable however. Towards the end of last month it was very happy accepting dates in DD/MM/YYYY format (presumably because you can't have the 11th of the 30th month in 2011). However now its switched around, and is treating dates as MM/DD/YYYY - so today's date (8 Dec 2011) is actually being treated as 12 Aug 2011.

I've tried using format to structure the date this way, but Access/VBA still sporadically treats in reverse anyway, and changes it back to the other format - there seems to be no consistency to it! I've even tried using DateSerial and it still gets it wrong.

I'm slowly going insane - is there a better way/different approach I can take to using dates? I know dates are stored as a number, can I use that format instead to make it more reliable?

Hi Guys,

I Have been using this function basically to export data to .xls and giving the last part of the file name as todays date. Been working fine, but I've noticed today that is not formatting the date to DD/MM/YYYY anymore??
Can anyone see anything I'm missing??

Thanks for any help

Louchey

Public Function exportFile()
Dim FileToExport As String

FileToExport = "S:SHAREDBen LPipe_Supports_" & Format(Date$, "dd-mm-yyyy")

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Pipe_Supports", FileToExport
End Function


Not finding an answer? Try a Google search.