Format() not working

[OPMCoordinator]

this is driving me nuts! #@@!@#%$#!

i have a database on a computer 1000km away from here on out company network with a straightforward xtab query that currently shows the following

expr1: Format([tablename].[date],"mmmm")

it is the column heading for the xtab.

it was working fine when i left a few weeks ago. it now comes up with "undefined function "format" blah blah blah".

i had a ring from the users saying it had fallen over so i tried loading up from here. it didnt work on my pc.

it = format([whatever].[date],"mmmm")

i looked at the query and said "bollocks, it should work". it didnt work. i opened another database on my pc and replicated the formula on a different set of data. it did work. i said "i told you so". i left that database open and went back to the troublesome one. the original query worked. i said "huh?" i printed out half of the urgent reports for them (snapshot to email) i got halfway through to a report that had slightly different tables and fell over because of drive x on my pc does not equal drive x on the other. I changed my drive letters to match the other. it didnt work. i said "grrr". i tried the first half just to be sure. it didnt work. i said "what the...?". i tried back to my own database. it worked. i had my second cigarrette since i gave up. then my third. feeling sick, i am writing this. i am stumped.

why would the format function work on one database and not on another ( it did work before!)

Cheers

Mike

 

[ByteMyzer]

Sometimes, for whatever reason, maybe a bug... er, feature, of Access, the VB library references in the database itself get corrupted. Even the built-in functions (string functions, too) don't work, as in your case. Most times, whenever I've had that problem, I create a brand new blank database and import the objects from the corrupted one, and then everything works again. You might try this and see if it works in your case.

 

[Lister]

I have just run your Format function in both Access97 & 2K. Sorry to say it worked fine in both times.
Have you tried recreating the query to see if it is a bug within the old one? Or if you declare the Date/Time field within the query, as simply

Format([NameOfDateTimeField],”mmmm”)

Also check if the Field is a Date/Time field and not Text (or string) value.
If it is a Text field you will have to use a DateValue() function inside the Format() function like this.

Expr1: Format(DateValue([YouTextField]),"mmmm")

Let us know how you get on.
Hope this is some help.

 

[SQL_Hell]

Hi,

I agree with ByteMyzer, VBA has probably dropped its references, are you sure that you or anyone else aren't copying the file over the network while it is still open, this corruputs access files (in my experience)

Why not create a batch file to distribute the file to all machines in one go, then will have no more hassles.

 

[OPMCoordinator]

Thank you Lister.
The feild is absolutely definitely date/time. the function was working a few weeks ago, but is now not.
the same problem is repeated in about 8 queries feeding 2 different reports/subreports thoughout the database.
i tried using it in a new query but still it did not work.

Thank you also SQL Hell.
the file resides on a network file server type drive and there is no need for anyone to copy it. there is a lady at that site who does most of the database construction and data entry, with a number of people drawing reports from it.

it is sounding more and more likely that it is the references. is there any way to fix that without moving all of the objects accross to a new database? how difficult is it? i am hoping it is as easy as importing everything using the import bit of access. there are a LOT of objects in the database. if it is not simple it is a weeks trip away and i dont want that right now - although my mate cleaned up when he went fishing last weekend and i wouldnt mind some of that action!

Cheers
Mike C

 

[Lister]

Bugger.
If I were you I would test it. Import the tables needed, the queries and any reports into a new database. (I am assuming that this is being run in Access and not a SQL Server back end).
Then try running the report\query in the new database. If this is successful you know that you have a corrupted database on your hands. Also if the table is corrupted it wont let you import it into a new database.

DON’T TRY TO REPAIR THE DATABASE WITH THE REPAIR WIZARD UNLESS IT ASKS TO OR IF YOU HAVE NO OTHER RECOURSE. THIS CAN DO MORE HARM THAN GOOD.

If you think (be as sure as you can be) that an object is corrupted you can import it from one of your back ups (you do have back ups don’t you?).
I have had forms and tables fall over on me and you can get them back this way.

Also, check corrupted in the search tab on this forum. There are heaps of references.

Good luck mate.
Let us know how you get on.