Format() function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Format() function

Author

Topic

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-20 : 11:36:42

Why does this query produce an error?

SELECT [Order Number], Format([Order date],"m/yyyy") AS Period
FROM orders
ORDER BY [Order Date];

The results bring back "#Error" in the column for [Period]

This is in Access 97.

Thanks

GreySky
Starting Member

20 Posts

Posted - 2003-08-20 : 12:07:25

Assuming Order Date is a *date*, then you should have no problem.

I'd suggest ensuring you don't have any broken/missing references. Goto a module, Tools->References.

David Atkins

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-20 : 13:34:30

It is a date.

What references should I be adding.

Currently I have checked:
VBA
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library
Microsoft Data Formatting Object Library

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-20 : 13:51:39

What is - Microsoft Data Formatting Object Library ?

I've never needed this.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-20 : 14:05:38

Not sure it is just displayed in my references as checked.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-20 : 15:02:38

Really strange.
Does the Format() function work in Debug window at least?

Whether ?format(date, "m/yyyy") produces 8/2003 there?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-20 : 16:33:54

I am having this issue in only one database. Others seem to work fine.

I am working around it right now, but if anyone has ideas about this I'd appreciate it.

Thanks!!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-21 : 02:00:20

Try: create new (empty) db and import into it
all objects from the problem db.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-21 : 02:09:21

Plus, I still don't know whether it works at all (e.g. in
Debug window). Maybe this function was re-defined (overwritten)
in some db's module.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-21 : 08:56:51

Yes it works in the debug window. I forgot to mention that, thanks.

Of other databases still not that one.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-21 : 09:16:44

Stupid question: are you sure [ORderDate] has a date datatype?

Are all of it's value valid dates? Null's may produce #error#; i don't remember exactly.

- Jeff

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-21 : 09:35:16

It is a date field, I will check for valid entries or nulls.

GreySky
Starting Member

20 Posts

Posted - 2003-08-21 : 12:47:28

Format is defined in the VBA object library. Check to ensure you're using the correct VBA reference (there may be many).

Also, you might want to try using Format$ (returns a string vs. a variant). I doubt this will help, but I don't see why it doesn't work in the first place.

David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources