Dear Members, I am using Access 2003. I have created a crosstab query and organized the date field as “months”. But when I wish to open my query it gives error as “undefined function format in expression”. When I open the query in design view I see column heading as “format(“mmm”;[MyDateField])”. It seems that my db can not understand this function that it has created. This query was functioning well before but now it gives error. So I want to ask: Is there a problem with my MsAccess programme? If not how can I solve this problem? Thanks for your future help. Very best wishes to all members. Ruhi Kulez
Thank you very much, I shall try your advises and procedures.
Best regards
Ruhi
Ruhi,
Yes, it is possible - you can set the “Compact on Close” option on your database.
Personally, I don’t think it’s a good idea to have a compact and repair executed every time you close the database. Like any other major update of the database, compact and repair is an opportunity to corrupt the database. It would be better to create a scheduled job that runs once a day (more often, if really necessary). The scheduled job should first perform a backup of the database, and only then perform a compact and repair.
In general, copying a file (if by “copying and pasting” you mean copying the file in Windows Explorer) does not corrupt it. In fact, that’s one of the simplest ways to create a backup of a database - provided you are sure that the database is closed. Copying a file that is open in another program may cause corruption.
To create a scheduled job to perform a backup and a compact and restore of a database, I would suggest doing the following:
Create a new, empty database.
Add a code module to that database
In the code module, add a public procedure that executes the “Backup Database” command (specifying the file path to the database you want to back up), and then executes the “Compact and Repair” command (specifying the file path to the database you want to compact)
Save and close the new database
Create a “scheduled task” (in Windows XP, run “Scheduled Tasks” from the Accessories folder on the Start menu; for later versions of Windows, run “Task Scheduler” from the “Administrative Tools”). Specify that the task should run Microsoft Access, but add the “command line option” /x myMacroName. The /x tells Access to run the macro immediately upon opening the database; of course, replace myMacroName with the name of the public procedure you created.
I strongly recommend creating a separate Access database to contain the procedure that performs the backup and compact processes. It should be much less vulnerable that a procedure in the database on which you are operating. In addition, you can expand the procedure to operate on multiple databases (hint: create a table in the new database to contain a list of the databases to process, and change the procedure to use a recordset to loop through the table entries), so a single scheduled task can “manage” all your databases.
Michael S. Meyers-Jouan
Michael,
This is very brief explanation, it has enlighted my mind. I want to ask two more questions:
- Is it possible to “compact and repair” automatically when closing the db and is it wise to do it?
- Does copying and pasting the db corrupt it?
Very best wishes
Ruhi
Dear Veena,
As I have written in my later posts I have solved the problem. Thanks for your nice interest.
Ruhi
Yes it seems to uncheck and check the references solving the problem. Thanks for your reply. Best wishes and a happy new year to you and all members.
Hi Ruhi,
Can you post your enture SQL string here …?
This SQL with “Format” function works perfectly in Access…:
SELECT Format(OrdDate,‘mmm’) AS MyMon, Sum(OrdAmt) AS MyAmt
FROM OrderMas
GROUP BY Format(OrdDate,‘mmm’)
Regards
Veena
Ruhi,
An Access file (a .MDB or a .ACCDB) is a very complicated file, that contains all the records from all the tables you have created, as well as all the forms, reports, queries, Visual Basic code, etc. The Access application allocates space in the file for each component as it is created. If any component is modified, another space is allocated, the item is copied to the new space with all the changes applied, and the old copy is marked as free space.
Access will sometimes re-use the free space for new items. However, much of the “free space” is not re-used because it is fragmented into small pieces. Access provides a “Compact and Repair” tool that makes a new copy of the entire database, moving everything into an efficient order, so that the database file is as small as possible.
When you are using a database, Access is making many, many changes to the database file. There are different threads of execution that may be making different changes in parallel. If anything happens to cause one or more of these threads to stop unexpectedly, some part of the Access database file is left in an unexpected state (for example, a data record is written into a particular location, but the table that describes the free space is not updated, so later something else will over-write the data). That is a corrupt Access database.
In may cases, the “Compact and Repair” tool can identify the corruption and fix it. But in other cases (particularly when it is a Visual Basic procedure, or a complex object like a form that has been corrupted), the “Compact and Repiar” tool will not fix the problem. That is when it becomes important to copy each element to a new database.
You also asked if this happens only to Access, or to other databases. It can happen to any database; that is why more powerful databases like Microsoft SQL Server have backup tools and a wide variety of other ways to detect and correct problems. However, Access is much more likely to encounter corruption than most databases, for a number of reasons. Here are a few:
Access uses a single file for the entire database; most database systems use separate files for transaction logs and for tablesAccess has multiple threads of execution running in a single process; most databases use a more robust execution modelAccess is a single application, running in the “client environment” (that is, running as a user program); most databases use services, which run in a system environment (and therefore are less vulnerable to interruption and interference)
Michael S. Meyers-Jouan
This guy had converted his db from Access2000 which may not be your case and you already repaired it so you wouldn’t be able to check this but his problem was related to the fact that msowc.dll was selected under references, didn’t show up as missing in the references but was indeed missing. In case it happens again…
http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/37436/Cannot-format-a-date-in-Query-returns-a-blank
I guess I always check the references not by seeing if they look OK because sometimes they do even if they aren’t (I think this is because it checks against the registry and if the key is there it thinks it is even if the actual reference is not but don’'t quote on this I am not sure) but the way I check them is by deselecting them and re-selecting them. If they are there you will be able to re-select them.
Dear Michael,
I have read your instructions and tried them. My "Format function"didn’t worked on another machine and there was no missing references. I have re-installed MsAccess but couldn’t solved my problem either. I unchecked and checked references again. I saw a mail of you at the access group that you were advising to open a new db and to import all files from the old db. I have tried this and at last my format function is working now! Now I want to ask you:
1.What is the cause of corruption?
2.Is it a sick of msaccess or can it be seen with other db programs?
I couldn’t look at the site Andrija’s original response, because there is a trouble with the link.
Thanks again for all replies. Very best wishes.
Ruhi
Dear Michael,
Thanks for your kind interest. I wish to check this site and I’m sure I will learn much about my problem. I didn’t checked my db on another computer but I’ll do it as soon as possible. I think there is a corruption because of a virus attack. Yet I am not a computer specialist I have trouble to solve it. I used various antivirus software but microsoft essentials still reporting corrupted files. I will inform you after solving the problem. Thanks for your and other members replies again with my very best wishes to all.
Ruhi
Ruhi,
I did a little research. You originally stated that your formula is:
format(“mmm”;[MyDateField])
Several people have commented on your use of the semicolon – ; – where we are accustomed to see a comma – , --. When you change the Regional Settings in Windows to Turkish, the semicolon does replace the comma as the list separator.
However, I have been unable to find any documentation that the Format() function operates differently in the Turkish version of Access than in the English version. In the English version, the Format function has the following syntax:
Format(expression [, format ] [, firstdayofweek ] [, firstweekofyear ] )
While the string “mmm” is a valid expression, the value of [ MyDateField] is not a valid format.
You also stated that “This query was functioning well before but now it gives error.” If the function was working in the same query and on the same computer then it is time to try to determine what has changed on that computer. If the function was working in the same query but on a differentcomputer, then you need to check the libraries that are referenced by the database (see Andrija’s original response (http://visualbasic.ittoolbox.com/groups/technical-functional/visualbasic-l/re-undefined-function-format-in-expression-3903997). If the function was working in a different query, you may need to check the order of the arguments.
Michael S. Meyers-Jouan
Ruhi
I believe you are using Query wizard not the Sql view in writing this query. As far as the format function is concerned, it is part of the VBA library which should be checked in your object references. If you unreference this object, the format function and other VBA functions will not work or be recognized. The only DLL we should be talking about is VBA (Visual Basic for Applications) If you have written your own format function as a module and you are trying to use it in your query, then that is a whole different story. The format function is a core function in VBA and is the same in Access, Excel and Visual Basic 6.0
The general format is Format( [DataVariable], [Formattype]) If your vba object is corrupted then you must replace it. What I will do is just uninstall Access and re-install it.
The date format statement as I know it in vba uses a a comma not a colon. Format(Date, “mm/dd/yy”) if your regional time is set to US. Could you give us the error number.
I’m going to crawl back into my box! That’s twice now. I think I’m in
holiday wind-down mode and my brain is starting to freeze in this snow!!
:))
Martin
Thanks to all members who replied to me.
As I said before I am using MS Access 2003 and prepared my crosstab query with query wizard and this query was functioning well before. In my db semi-colons are working not colons (I don’t know why, may be because of preferences etc). So there is nothing wrong with syntax. But I tried the ones of Jtabe and Quanvu. I get the same error message. I have checked missing references and made compact and repair processes before. Fredvanleeuwen’s first sql statement gives the same error message and the second one gives an error message as “invalid sql statement(expected DELETE…etc)”.
My opinion is like Clavo and Andrijaxl .Trouble seems to be with DLL or OCX or references needed to be loaded again. I am know trying their advices. But may be I should reload MS Access again?!
Thanks again all replies. Very best wishes to all.
Ruhi Kulez
Copied and pasted from Access:
SELECT Format([entrydate],“MMM”) AS ColMonth
FROM tblexperts;
Works fine, embedded in an Access query,
In VB6 I would do it like:
varsql = “SELECT Format([edate],“MMM”) AS ColMonth FROM tblexperts;”
Set QRYTMP = db.openrecordset(varsql, dbopendynaset)
(In DAO 3.51)
My experience has been that Access will throw the error in either case. Whether the function call is in VBA or in the SQL stored as a Query Object.
Doug
Martin
A function can be embedded in an Acces query. He has at least 2 problems with the format fuction. The order of the parameters and the syntax.
Format([Mydatefield], “mmmm”) is the right syntax and order of the parameters.
The compiler did catch the syntax error, but he would have ended up with a runtime error too.
I agree with not using simi-colons, but I also thought format syntax was Format(expression[, format[, firstdayofweek[, firstweekofyear]]]). In other words, format([MyDateField],“mmm”). I don’t think the format function is different even if it is in a query.
I have run in to this error before and it usually is a reference that is missing. The other thing that can happen is that when another program is installed, it replaces some of the shared DLLs or OCX files with older versions. This can cause problems.
Also, have you run a repair and compact on the database?
The parameters are written backwards plus wrong formatting
The correct way is
format([MyDateField], “mm”) for short month 01
or format([MyDatefield], “mmmm”) for long month January.
