zimmer9
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
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
ASKER
I got the error:
Function is not available in expressions in query expression 'Format(CDate(Mid(a.Postin gDate,5,2) & "/" & Right(a.PostingDate,2) & "/" & Left(a.PostingDate, 4)), "mm/dd/yyyy")'. See attachment.
FormatDate2.doc
Function is not available in expressions in query expression 'Format(CDate(Mid(a.Postin
FormatDate2.doc
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
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 , "@@@@\/@@\/@@")
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),
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 , "@@@@\/@@\/@@"))'.
TruePostingDate: CDate(Format(a.PostingDate
I get the folllowing error message:
Syntax error (missing operator) in query expression 'TruePostingDate: CDate(Format(a.PostingDate
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?
TruePostingDate: IIF(ISNULL(A.PostingDate),
where would I put the 2 additional closing parens, since it contains 4 open parens and 2 closing parens?
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
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
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 ,"@@@@\/@@ \/@@")))' .
TruePostingDate: IIF(ISNULL(A.PostingDate),
If get the same error:
Syntax error (missing operator) in query expression 'TruePostingDate: IIF(ISNULL(a.PostingDate),
What did this tell you:
First, in the VBA editor, go to menu Tools, References and resolve any reference marked MISSING.
/gustav
First, in the VBA editor, go to menu Tools, References and resolve any reference marked MISSING.
/gustav
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
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
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
ASKER
Select *, CDate(Format(PostingDate, "@@@@\/@@\/@@")) As TruePostingDate
From OI_Intell
Where PostingDate Is Not Null
-------------------------- ---------- -------
Result works:
TruePostingDate
---------------------
8/6/2013
8/6/2013
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
When that works, modify it to the insert query. This will probably be where you will meet the error.
/gustav
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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),
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If so, the following should work:
Format(CDate(Mid(a.Posting