Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

How to resolve Access 2003 error "Function is not available in expressions in query expression 'Format(Format(a.Value.Date,"0000\/00\/00"),"?

During the execution of a query in Access 2003 using an MDB file, I am getting the following error when trying to reformat a txt field that contains a date value in the format yyyymmdd.
This is a field I imported from a txt file.

How would you reconstuct the text date value to rearrange the date in format
mm/dd/yyyy?

See attached word doc for the format synax error.
FormatDate.doc
Avatar of D B
D B
Flag of United States of America image

I'm not certain since it has been quite some time since I've used Access, but does it have to be converted to a date first, then formatted?
If so, the following should work:
Format(CDate(Mid(a.PostingDate, 5,2) & "/" & Right(a.PostingDate, 2) & "/" & Left(a.PostingDate, 4)), "mm/dd/yyyy")
Avatar of zimmer9

ASKER

I got the error:

Function is not available in expressions in query expression 'Format(CDate(Mid(a.PostingDate,5,2) & "/" & Right(a.PostingDate,2) & "/" & Left(a.PostingDate, 4)), "mm/dd/yyyy")'. See attachment.
FormatDate2.doc
Avatar of Gustav Brock
First, in the VBA editor, go to menu Tools, References and resolve any reference marked MISSING.

Then, to obtain a true date value, you can use this expression:

TruePostingDate: CDate(Format(a.PostingDate, "@@@@\/@@\/@@"))

/gustav
are you certain that all of your [PostingDate] record values are valid dates and not NULLs?

I think Gustav has the best solution, if [PostingDate] is NULL, then none of the recommended values will work, so if you have any NULLs in that field, you should try something like:

TruePostingDate: IIF(ISNULL(A.PostingDate), NULL, CDate(Format(A.PostingDate, "@@@@\/@@\/@@")
Avatar of zimmer9

ASKER

When I try:
TruePostingDate: CDate(Format(a.PostingDate, "@@@@\/@@\/@@"))
I get the folllowing error message:

Syntax error (missing operator) in query expression 'TruePostingDate: CDate(Format(a.PostingDate, "@@@@\/@@\/@@"))'.
Avatar of zimmer9

ASKER

When I try:
TruePostingDate: IIF(ISNULL(A.PostingDate), NULL, CDate(Format(A.PostingDate, "@@@@\/@@\/@@")

where would I put the 2 additional closing parens, since it contains 4 open parens and 2 closing parens?
Avatar of zimmer9

ASKER

BTW, I do have NULL values in the field. Thanks for noting that possibility.
What did this tell you:

First, in the VBA editor, go to menu Tools, References and resolve any reference marked MISSING.

> .. where would I put the 2 additional closing parens ..

Are you serious? Can't you really figure this out?

/gustav
Avatar of zimmer9

ASKER

When I try:
TruePostingDate: IIF(ISNULL(A.PostingDate), NULL, CDate(Format(a.PostingDate, "@@@@\/@@\/@@")))

If get the same error:

Syntax error (missing operator) in query expression 'TruePostingDate: IIF(ISNULL(a.PostingDate), NULL, CDate(Format(a.PostingDate,"@@@@\/@@\/@@")))' .
What did this tell you:

First, in the VBA editor, go to menu Tools, References and resolve any reference marked MISSING.

/gustav
Avatar of zimmer9

ASKER

Sorry for not answering your question earlier.

In Tools, References

Available References:

The following are checked:

Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft Excel 11.0 Object Library
Microsoft Office 11.0 Object Library
Microsoft Outlook 11.0 Object Library
Microsoft ActiveX Data Objects 2.5 Library
PDF Creator

The next line is deselected:

IAS Helper COM Component 1.0 Type Library
It should work.
Try a simple select query where you only use table OI_Intell:

Select *,
CDate(Format(PostingDate, "@@@@\/@@\/@@")) As TruePostingDate
From OI_Intell
Where PostingDate Is Not Null

/gustav
Avatar of zimmer9

ASKER

Select *, CDate(Format(PostingDate, "@@@@\/@@\/@@")) As TruePostingDate
From OI_Intell
Where PostingDate Is Not Null

-------------------------------------------
Result works:

TruePostingDate
---------------------
8/6/2013
8/6/2013
OK, then rebuild from scratch your original query step by step but as a select query only. First with table OI_Intell only, then with the where-join to the other table.

When that works, modify it to the insert query. This will probably be where you will meet the error.

/gustav
SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zimmer9

ASKER

(I gave the incorrect table name before, it should be OI_Intell_Mod)

The following SELECT statement works fine

Select *, CDate(Format(PostingDate, "@@@@\/@@\/@@")) As TruePostingDate
From OI_Intell_Mod
Where PostingDate Is Not Null

-----------------------------------------------------------------------------------------------------

The following 2 SELECT statements generate an error:
Syntax error (missing operator) in query expression


Select *, TruePostingDate: IIF(ISNULL(A.PostingDate), NULL, CDate(Format(a.PostingDate, "@@@@\/@@\/@@")))
From OI_Intell_Mod
Where PostingDate Is Not Null

Select *, TruePostingDate: CDate(Format(a.PostingDate, "@@@@\/@@\/@@"))
From OI_Intell_Mod
Where PostingDate Is Not Null
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial