Access 2007 date format Results

I have converted an old database to Access 2007 and find I need to edit my queries that use the date format function.

eg change from Format([date],'yyyy') to Year(date)

I can change each query manually but is there a quicker way to do this?

I have an Access 2007 database where I generate and export a report to Excel 2007. I then have a button on my report to open and format the exported file. The file opens just fine. The initial formating is completed, but then it hacks on defining conditional formats. My conditional formating is pretty simple - comparing all the date fields in column G with 4 other date fields (A1, B1, C1, and D1). The error is one I found to be common when searching the net 'Run-time error '5': Invalid procedure call or argument' I have not been able to find another thread with a solution that fitls my issue. Below is my code - it stops on the first call to Add a Formula for conditional format. I followed other advise of recording a macro in Excel and then copy/paste to Access, but I've made some minor modifications not pertaining to the conditional formulas. Any help is much appriciated.

Private Sub btnExport_SB_Click()
Dim objXLApp As Object
Dim objXLBook As Object
Dim strFormula1 As String
Dim strFormula2 As String
Dim strFormula3 As String
Dim strFormula4 As String

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("D:dataWorkDataProjects ProjectPlanningqryProjectMSTR3_SB.xls")
objXLApp.Application.Visible = True
'strFormula1 = "=IF(G1


Today the database has decided for whatever reason to display hashes in 'date' text fields on certain reports. Also some text field formatting that before displayed correctly now does not! I can overcome by increasing the width of the field but I do not want to have to do this to all the reports onthe DB!

I have not made any changes to the individual forms/reports - in Access 2007 is there any flag/option that can change formatting requirements on forms / reports without the administrator knowing!

It is frustrating to say the least!

Thanks for any help offered.

NB. If I open a backup/older copy of the DB - the reports etc. display as expected. But, I am sure I have not changed the reporting field formats in any way on the other DB.
Regards
G

Hullo!

I am running an Access 2003 database in Access 2007 using Vista.

I would like the formatting of the [date] control in a report to be based on the contents of the [accuracy] field.

Ex:

Date: 9/17/2008 Accuracy:"Accurate to Month"
Displayed in control: 9/2008

Date: 9/17/2008 Accuracy:"Accurate to Day"
Displayed in control: 9/17/2008

Date: 9/17/2008 Accuracy:"Accurate to Year"
Displayed in control: 2008

I've tried to use a macro to control the visibility of fields formatted based on the content of the Accuracy field. (If [Accuracy] was "Accurate to Month", the control displaying [Date] that was formatted mm/yyyy would be the only one visible as opposed to the other two controls formatted mm/dd/yyyy and yyyy.) Unfortunately my attempt at making the macro was in vain, all I got was a message to fix my macro and I don't even know where to start.

Any help would be greatly appreciated. Thank you!

I have a small piece of code that is supposed to write out a REPORT in to an Excel file in Excel format and open the document in Excel.

It works fine in witht he command that is now commented out. But with a similar command for Access 2007 does not perform the smae thing. The code that I am using now for Access 2007 does not work.

	Code:
	DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NewEmailList"

Here is the complete code.

	Code:
	Public Function ExportEmailList()
 Dim Filename As String
 
 ' Get the Current Path and Make up the file name
 Filename = Application.CurrentProject.Path & "NEW_Email_List_" & Format(Date, "yyyymmdd") & ".xls"

 MsgBox "Writing Email list to File-" & Filename
'Generate the report in the proper format with names concatenated. For Access 2003. Not for 2007.
 'DoCmd.OutputTo acOutputReport, "rptNewEmailList", acFormatXLS, Filename, -1
 
 ' For Access 2007
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NewEmailList", Filename, True
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NewEmailList", "NEW_Email_List_" & Format(Date, "yyyymmdd") &
".xls", True
 MsgBox "Completed Writing Email list"
  
End Function

Can someone please tell me what command that i should use in Access 2007 (on Vista) to write out or save a report in an Excel file and open the file automatically.

Can anyone tell me what is wrong with this SQL Satement when used in a MS Access 2007 CurrentDB.OpenRecordset command.

("SELECT tStatusChanged.AddressNumber, tStatusChanged.StatusNumber, tStatusChanged.StatusChangedDate" _
& " FROM tStatusChanged" _
& " WHERE tStatusChanged.StatusChangedDate

I have searched for solutions here and read many posts. importing html site:http://www.access-rogrammers.co.uk/forums

I run Vista and Access 2007 and have a copy of Microsoft Visual Studio 2008 Express-Edition . I can create and build excel macros but am just a medical device engineer and not a programmer (yet ). None the less, I want to get to know how to use Access and some VB.

I have a smallish project. I have well over 10k html files which are website pages all with the same format but without tables. I want to move the data of interest from the html to a data base.

This is the format of the core of the html data I want to extract form the files.


This is title text
Presented By: first name, last name <person@gmail.com>
Date: Wednesday, 11 June 2005, at 11:40 p.m.

This could be thousands of words of text




There is 6 fields of data here that I want to capture and import.This is title text first name last name email date This could be thousands of words of text

I tried importing a single file with import html and only get this error: Text field specification field separator matches decimal separator or text delimiter. followed by:Error occured trying to import file ... file not imported.


I need some guidance. At the moment it looks daunting but I know I can do it. I sure don't want to do this data input by hand. I looked at some of the threads based on this search:
string manipulation importing html site:http://www.access-programmers.co.uk/forums

I found unanswered questions or excel importing or things that did not make sense to me.

I hope someone can give me some basic tips to get going in the right direction. I will need to RTFM but I am hoping I can have my teeth in this problem enough to hear it squeal a little and not just laugh.

Thanks for considering this problem.

I have an application built in Access 2002 and running in Access 2002 /2003. It is a split with an Access front end and multiple versions of the back end. Access, Sql Server 2005 Express, and Sql Serve 2008 Express. All of the versions in what ever combination run fine with the application running Access 2002 or 2003 on the client side and running Access 2002/2003 or either version of SQL Server Express backend on the server side. The tables are linked so that the application remains an Access application except for storing the data. There is a desire to upgrade to Access 2007/2007 runtime.The application runs fine with the Access 2007/2007 runtime when the front end iis Access and the back end is Access but fails when SQL2005 or SQL 2009 is the back end. The application has code that errors out when the client is Access 2007 or Access 2007 runtime and the backend is SS 2005/2008 with an ODBC error - ODBC call failed - unable to convert string to date - error #241.
The code fails in both of the following statements (a DCount and a Docmd.RunSQL).
1:
....
strSearch = "[CustomerId] = " & Chr(34) & Me![cboCustomerId] & Chr(34) _
& " and [CustomerType] = " & Chr(34) & Me![cboCustomerType] & Chr(34) _
& " and [OrderDate] = " & "#" & Format(Me![cboOrderDate], "MM/DD/YYYY") & "#" _
& " and [OrderNumber] = " & Me![cboOrderNumber]

intCnt = DCount("[CustomerId]", "tblCertificate", strSearch)
If intCnt = 0 Then
.....
2:
.....
strSQL = "Insert Into tblCertificate (CertificateNbr,CustomerId,CustomerType,OrderDate, OrderNumber," _
& "PONumber,Method) Values(" & intNextCertificateNbr & ",'" & Me.CustomerId & "','" & Me.CustomerType & "'," _
& "#" & Format(Me![OrderDate], "MM/DD/YYYY") & "#" & "," & Me.OrderNumber & "," & Me.PONumber & ",'" & strMethod & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
...
I know that SQL Server uses a different syntax for its dates ( # vs ' and " vs ' ). I wonder why the the application works on the Access 2002/2003 client side for both an Access function and a RunSql command but fails when the Access 2007/2007 runtime client is used. Could it be the ODBC driver that is being used?
Any ideas?
thanks..

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

Greetings,

I have bumped into the first occurrence of needing to work with user input date values in the application I am developing.

Having been warned about Access and date nonsense:
“International Dates in Access” “1. Misinterpretation in the User Interface”
http://allenbrowne.com/ser-36.html#Interface

I thought to find some sort of "helper control" to make date validation easier. I came across the Microsoft DateTimePicker ActiveX control that is found in the MSCOMCT2.OCX file.
http://msdn.microsoft.com/en-us/libr...=vs.60%29.aspx

At first glance yesterday afternoon it looked bullet-proof enough. However today in further testing, cracks/limitations seem to be appearing.

I wanted to arrive at dates in USA MM/DD/YYYY format. So I have told that control I want to use format "3" (Custom) and filled in CustomFormat mask:

MM'/'dd'/'yyy

The control will not obey my two digit Month / Day request in the dates it displays in the control. As well, when I extract the value from the control and sent it to an unbound field control, the "0" padding characters for Month / Day are missing.

For formatting I switched to the other slash "" char in the CustomFormat field and the control updates its view to use the other slash, however still returns the standard "/" character as the slash when reading the control's value.

So, I am thinking that all of the fancy formatting which is said to be possible with the control formats the display of the control only, and that reading the control's value returns a more standardized date string. (But then what about my two digit Month / Day spec?)

Finally, time keeps slipping back through the floorboard cracks. I keep whacking the time part in the Value property of the control, and it holds that way for a little while, and then time comes right back shortly. So on its own, the Value property is back to:

10/13/2011 9:43:52 AM

as I type this message. Clearly the time is not specified in my CustomFormat mask, and like I said the Format is set to "3" which should mean custom, based on CustomFormat.

Suggestions on how to avoid such aggravations? Thanks!

In Access my dates are formated as short dates, but when it was migrated to SQL it showed as Date and Time. I notice that it only have a short date with time as my selection. So I am assuming that I can't change to short dates. Is that correct to say

I am converting an old database that was from Access 2002 to Access 2007. Everything has gone well and I'm at the final point. The problem I have run into is with a function in VBA that is no longer valid in Access 2007. It is something like this:


	Code:
	Select Case ReportName
        Case "Report1"
            'some code
        Case "Report2"
            'some code
        Case "Report3"
            'some code
End Select
 
txt = InputBox("Enter file name:", , "C:ExcelFiles" & ReportName & "_" & Format(Date, "yyyymmdd") & ".xls")
 
DoCmd.OutputTo acOutputReport, ReportName, acFormatXLS, txt

From what I gather, outputting a report directly to Excel is no longer an included function in Access 2007. The problem is I have a bunch of reports and the user can select if they want to export them to Excel. Is there a simple way to do this?

I have a shared out database that is working fine. Every user I have opens and operates it just fine. However, there is one individual when he is logged in his database is displaying dates in a different format which is causing all sorts of problems in the DB.

For example. When I log in a piece of equipment into the DB the date from the document number (julian) is converted into a calendar date and entered into the table as DD-MMM-YY. But when this one individual logs in an item the date entered is in the format MM/DD/YY.

This is running MS Access 2007 on a Vista machine.

Any hints/tips on what to look for to fix this?

Thx!

Hello,


I am using Access 2007 and I need to import a delimited .txt file in access. I am having difficulties importing the correct dates. I have tried importing using the ‘Open file’ dialog which creates a linked database, and i have tried creating a new database and using the import wizard to append the data. I will be adding delimited data on a monthly base to the database therefore I feel a linked database has no purpose. Depending on the method of importing the date fields either show this error “#Num!” or stay blank after import.

I have three columns with different date and time notations. For instance “DEC 2009” for date notation and in another column “2009-12-07 00:00:00” for date and duration and one more with “0.00” for duration in minutes. The access import wizard does not allow three different notations. How to import this data?

How can I specify different time and date notations for these columns? Do I need to specify these in advance during the import wizard or is it possible to do this after the import?

How can I create a specification file for the import wizard specifying the correct notations? The wizard only allows this after a correct import, while the problem is that I cannot create a correct import.

The user gets the front end of Access 2007 from an outside entity. We have no ability to put code in it to export/create a spreadsheet. She exports data to an Excel 2003 format, which includes dates. She checks "Export data with formatting and layout." and "Open the destination file after the export operation is complete..." She copies rows from that Excel 2003 spreadsheet to another one. The dates in the copied rows turn into a five-digit number, apparently a serial date, in the spreadsheet they are copied into. Sometimes the dates which were already in that spreadsheet also turn into the five-digit numbers. The user formats them as a date, and the dates display as 13- Jan-11. However, after she saves the spreadsheet and reloads it, the dates are back to displaying as serial dates.
She has also tried copying the rows from Access 2007 and pasting them into an Excel 2003 spreadsheet. The result of the process is the same.
What do we do differently?

Tags: Access 2007, Excel 2003
(I was not able to get them put on initially and cannot now while editing.)

Hi All

I have searched endlessly for this with no real results

Oh I am new at this so forgive me if this is an obvious task that I just can't find an answer to

I have a access 2007 database we use for tracking employee training. Some of the training expires after a couple of years. I have a field called ExpiraryDate that uses this format "99/99/0000". This portion of the database is a subform of the main "employee information form"

What I would like it to do is when the PC calendar reaches a month before the date entered it automatically sends an email to the supervisor letting him/her know that they need to reschedule a certain training for that person(s) (Database will obviously have to be running) (I.E. of expired training = First Aid Training)

It should be running almost every day by some user which would help ensure that the email would be sent.

Any help with this will be greatly appreciated

Thanks in advance

Ken

I am at a loss as to why my dates in my table datasheet are not consistent in the Date/Time format. In the table and specifically the Date/Time field it is formatted as the selection "Short Date". I am located in the U.S. using MS Access 2003. The database I'm using was a free download from the MS website called "Accounting Ledger" and it is for Access 2003.


	Code:
	http://office.microsoft.com/en-us/templates/TC010175341033.aspx?CategoryID=CT101426031033

While I have dabbled in databases some I am really at a loss as to why I am seeing two >>different

Hey all, I've been using this site for a while and find it to be a great resource. This is my first post as I can't find a solution anywhere.

I have a make table query that runs off a linked sql server table. It's called:

dbo_LienReleaseMaster

The query is fairly basic:

SELECT dbo_LienReleaseMaster.[RCACT#], dbo_LienReleaseMaster.RCCLNT, dbo_LienReleaseMaster.RCCTID, dbo_LienReleaseMaster.RCRCDT, dbo_LienReleaseMaster.RCRFDT, dbo_LienReleaseMaster.RCSTID, dbo_LienReleaseMaster.RCSTDT, dbo_LienReleaseMaster.RCSTCD, dbo_LienReleaseMaster.RCSSTC, dbo_LienReleaseMaster.MHMLTP, dbo_LienReleaseMaster.MHMLDT, dbo_LienReleaseMaster.DLCRUR, dbo_LienReleaseMaster.DLDTPR, dbo_LienReleaseMaster.DLOPT, dbo_LienReleaseMaster.DLRQUR, dbo_LienReleaseMaster.DLSTAT, dbo_LienReleaseMaster.ATAUTO, dbo_LienReleaseMaster.ATCRDT, dbo_LienReleaseMaster.CNSTDF, dbo_LienReleaseMaster.EPSIID, dbo_LienReleaseMaster.SICITY, dbo_LienReleaseMaster.[1stReconSubDate], dbo_LienReleaseMaster.ActiveFlag, dbo_LienReleaseMaster.PHoldFlag, dbo_LienReleaseMaster.THoldFlag, dbo_LienReleaseMaster.CompletedFlag, dbo_LienReleaseMaster.ProcessedType, dbo_LienReleaseMaster.ReleaseType, dbo_LienReleaseMaster.ComplianceFlag, dbo_LienReleaseMaster.STHIRS, dbo_LienReleaseMaster.DLRABPID INTO SNAPSHOT
FROM dbo_LienReleaseMaster
WHERE (((dbo_LienReleaseMaster.RCRFDT)

Hi

Access 2007, MDB file, Local Machine

Just when you think you've got your head around this.....

I am in the U.K. so I want dates to be displayed in forms and tables with the local format of dd/mm/yyyy. When I call Now() I do indeed get the correct format returned e.g.

01/04/2012 13:36:43 (April !st 2012)

Because of the laregly unpredictable and potentially horrific results with dates (I would hate to get my tax return wrong!!), I have got into the habit of adding all dates into my tables with # delimiters:-

	Code:
	 
Dim Rec_Date As Date
 
Rec_Date = NOW()
SQL_STR = "UPDATE My_Table SET Date_Rec_Added = # " & Rec_Date & "#"
Execute... blah blah

This (I think) adds the date in the required U.S. format of mm/dd/yyyy .

In my VB modules, I also try to convert any dates submitted by U.K. users into U.S. format and then always # them for table retrievals / calulations / comparisons etc.

Now, I had expected Access/Windows to display dates in my forms and tables with the local U.K. format... it/they don't. I sort of thought that was their job (using locale settings)? I do not have any formatting set on textboxes or table column defs.

What doesn't this happen please?

Hi there,

I'm having a strange issue when importing an Excel 2007 worksheet to Access 2007. I have 4 date fields that are formatted in Excel as Date/Time type *3/14/2001. I have my import process set up by a command button that deletes and appends the new data to the Access table. I have the same Date/Time formatting in the table for all 4 fields.
When I import, 2 of the fields don't come across and the import errors table states Type Conversion Failure.

I don't understand why, if all of the data types are the same, just 2 of the fields would have problems.

Update: I do understand that Access looks at the first several rows to determine the datatype and, in looking closer, these two fields are blank for about the first 20 rows, I put in 0/00/0000 in the first row for both and the data came across. But, since the Excel file data will change (via copy/paste from another source) I don't want my users to have to do this each time. Any other option?

Thanks,

Any ideas??
Thanks!


Not finding an answer? Try a Google search.