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
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbbishopCommented:
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")
0
zimmer9Author Commented:
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
0
Gustav BrockCIOCommented:
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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Dale FyeCommented:
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, "@@@@\/@@\/@@")
0
zimmer9Author Commented:
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, "@@@@\/@@\/@@"))'.
0
zimmer9Author Commented:
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?
0
zimmer9Author Commented:
BTW, I do have NULL values in the field. Thanks for noting that possibility.
0
Gustav BrockCIOCommented:
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
0
zimmer9Author Commented:
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,"@@@@\/@@\/@@")))' .
0
Gustav BrockCIOCommented:
What did this tell you:

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

/gustav
0
zimmer9Author Commented:
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
0
Gustav BrockCIOCommented:
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
0
zimmer9Author Commented:
Select *, CDate(Format(PostingDate, "@@@@\/@@\/@@")) As TruePostingDate
From OI_Intell
Where PostingDate Is Not Null

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

TruePostingDate
---------------------
8/6/2013
8/6/2013
0
Gustav BrockCIOCommented:
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
0
Dale FyeCommented:
But that will only get the records inserted into the destination table where [PostingDate] IS NOT NULL.  What about those records where it is NULL, you may need insert those with a second query.

I also realized what might be happening with the IIF() statement.  When you use IIF() Access actually evaluates both the true and false arguments, so you would not be able to use the formatting that gustav references in the IIF() for records where [PostingDate] IS NULL.  If you wanted to do this all in a single step, I believe you would need to write a function, something like:

Public Function fnTruePostingDate(PostingDate as variant) as Variant

    IF ISNULL(PostingDate) Then
        fnTruePostingDate = NULL
    Else
        fnTruePostingDate = CDate(Format(PostingDate, "@@@@\/@@\/@@"))
    End If

End Function
0
zimmer9Author Commented:
(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
0
Gustav BrockCIOCommented:
You need either an alias or nothing, and you must use proper SQL syntax as in the first SQL that works:

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

Next:

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

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.