Microsoft Access 2000 - Function Is Not Available...

[JackSapsford]

Hi All,

I am by no means an Access expert. I know very little infact. However I am getting pressure to look at an error message that about 20% of our users are getting. We have an Access database that was written in access 2000. This is still to this day mission critical. The issue I have is that now some users are getting the below error. I cannot for the life of me figure out why this is. I am hoping that it is something straight forward. Please also see below for a quick summary of how it is setup.

Error: Function is not available in expression in query expression 'IIF(IsNull([CONTACT ADDRESS]),[ADDRESS] & Chr(13) & Chr(10) & [COUNTY] & Chr(13) & Chr(10) & [POSTCODE],[CONTACT ADDRESS]'

We have the MDB file running on a network share and then each user has a copy of the mde file on their local machine. All users are on windows 10 and all are using access runtime 2000.

 

[Andrzejek]

You may simply need a right parenthesis ) at the end of this [tt]IIF[/tt] expression.



---- Andy

There is a great need for a sarcasm font.

 

[JackSapsford]

Thanks for the response Andrzejek, I'm afraid that was nothing but a Typo in me copying over the error message. I wish it were that simple!

 

[Andrzejek]

Error says: "Function is not available in expression in query expression "
Could you share your query?

You evaluate CONTACT ADDRESS field, if it is NULL, you get other fields, otherwise you get the original CONTACT ADDRESS, which is NULL. Maybe NULL is not allowed in your query?


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Jack,
I would try the Nz()

Nz([CONTACT ADDRESS],[ADDRESS] & Chr(13) & Chr(10) & [COUNTY] & Chr(13) & Chr(10) & [POSTCODE])

This could be totally unrelated to your expression and possibly caused by a broken reference. It is quite difficult to test in runtime.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JackSapsford]

Hi All,

This is the full sql query at the moment:

"INSERT INTO tbl_CORRESPONDENCE ( TO, FULL_ADDRESS, DEAR, CONTACT, FAX_NO, [JOB NUMBER], JOBTITLE) SELECT IIf([CONTACT MASTER],'',IIf(IsNull([CONTACT TITLE]),'',[CONTACT TITLE] & ' ') & IIf(IsNull([CONTACT INITIALS]),'',[CONTACT INITIALS] & ' ') & [CONTACT SURNAME]) AS TO, IIF(IsNull([CONTACT ADDRESS]),[ADDRESS] & Chr(13) & Chr(10) & [COUNTY] & Chr(13) & Chr(10) & [POSTCODE],[CONTACT ADDRESS]) AS FULL_ADDRESS, IIf([CONTACT MASTER],'Sir',IIf(IsNull([CONTACT FORENAME]),IIf(IsNull([CONTACT TITLE]),'',[CONTACT TITLE]) & ' ' & IIf(IsNull([CONTACT INITIALS]),'',[CONTACT INITIALS] & ' ' & [CONTACT SURNAME]),[CONTACT FORENAME])) AS DEAR, tbl_CONTACTS.[CONTACT NAME] AS CONTACT, [tbl_CONTACT NAMES].[CONTACT FAX] AS FAX_NO, FORMS![frm_GENERAL MENU]![NUM CONTROL] AS Expr1,'" & PrepareSQL([Form_frm_FIN SUMMARY].[JOB TITLE])"

I have also now tried it with NZ() but get the same message just with NZ instead now, see below for that full sql query:

"INSERT INTO tbl_CORRESPONDENCE ( TO, FULL_ADDRESS, DEAR, CONTACT, FAX_NO, [JOB NUMBER], JOBTITLE) SELECT IIf([CONTACT MASTER],'',IIf(IsNull([CONTACT TITLE]),'',[CONTACT TITLE] & ' ') & IIf(IsNull([CONTACT INITIALS]),'',[CONTACT INITIALS] & ' ') & [CONTACT SURNAME]) AS TO, NZ([CONTACT ADDRESS],[ADDRESS] & Chr(13) & Chr(10) & [COUNTY] & Chr(13) & Chr(10) & [POSTCODE]) AS FULL_ADDRESS, IIf([CONTACT MASTER],'Sir',IIf(IsNull([CONTACT FORENAME]),IIf(IsNull([CONTACT TITLE]),'',[CONTACT TITLE]) & ' ' & IIf(IsNull([CONTACT INITIALS]),'',[CONTACT INITIALS] & ' ' & [CONTACT SURNAME]),[CONTACT FORENAME])) AS DEAR, tbl_CONTACTS.[CONTACT NAME] AS CONTACT, [tbl_CONTACT NAMES].[CONTACT FAX] AS FAX_NO, FORMS![frm_GENERAL MENU]![NUM CONTROL] AS Expr1,'" & PrepareSQL([Form_frm_FIN SUMMARY].[JOB TITLE])"

Hopefully this helps towards sorting this?

Thanks, Jack

 

[combo]

Seems that you try to execute sql query string, you have three double quote signs, at the beginning and at the end: [tt]...AS Expr1,'" & PrepareSQL([Form_frm_FIN SUMMARY].[JOB TITLE])"[/tt]. Is it ok?
What about PrepareSQL function?

combo

 

[Andrzejek]

If you have the INSERT statement in the string, I would assume you assign it to some variable, like:

[pre]
strSQL = "INSERT INTO tbl_CORRESPONDENCE ( TO, FULL_ADDRESS, ...
[/pre]
If so, could you share what you get if you do:

[pre]
Debug.Print strSQL[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[strongm]

As combo says, I#'d be looking really hard at

[tt]'" & PrepareSQL([Form_frm_FIN SUMMARY].[JOB TITLE])"[/tt]

 

[JackSapsford]

Hi All,

So I managed to solve that error by recompiling the MDE on a diferent PC. The issue I now have is that this SQL query was part of building a word document however I now have some users who can't create word documents, it doesn't error it just doesn't open word at all that I can see, see below the code to create a word document from a template.

Public Sub PRINT_DOC(TEMPLAT As String)

    Dim oword As Word.Application
    
'   Array bound increased from 20 to 100 [v1.1]
    Dim FIELDNAME(100) As String
    Dim strSQL As String
    Dim FILE_STR As String
    Dim dbs As Database
    Dim COUNTER As Integer
    Dim COUNTA As Integer
    On Error GoTo notloaded
    
    Dim theError As Integer
   
    Err.NUMBER = 0
    Set oword = New Word.Application

notloaded:
    If Err.NUMBER = 429 Then
        Set oword = New Word.Application
        theError = Err.NUMBER
    End If
    
On Error GoTo Err_PRINT_DOC
    DoCmd.Hourglass True
    

    oword.Documents.Add TEMPLAT
    
    oword.Visible = True
    oword.Activate
    
    
    COUNTER = oword.ActiveDocument.FormFields.COUNT
    For COUNTA = 1 To COUNTER
        FIELDNAME(COUNTA) = oword.ActiveDocument.FormFields(COUNTA).Name
    Next
    
    For COUNTA = 1 To COUNTER
        Select Case FIELDNAME(COUNTA)
            Case "CONTACT": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![CONTACT]
            Case "ADDRESS": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![FULL_ADDRESS]
            Case "REFERENCE": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![REFERENCE]
            Case "FAX": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![FAX_NO]
            Case "SUBJECT": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![JOBTITLE] & " - " & Forms![frm_correspondence]![SUBJECT]
            Case "JOB": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![JOBTITLE]
            Case "REPORT": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![SUBJECT]
            Case "DATE": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![DATE]
            Case "SIGNED": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![SIGNED]
            Case "FROM": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![FROM]
            Case "TO": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![TO]
            Case "CC": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![CC]
            Case "DEAR": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![DEAR]
            
            Case "INVOICE_SUM": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![INVOICE SUM]
            Case "INVOICE_VAT": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![INVOICE VAT]
            Case "INVOICE_NOTES": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![INVOICE NOTES]
             Case "INVOICE_NUMBER": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![INVOICE NO]
            Case "INVOICE_TOTAL": oword.ActiveDocument.FormFields(COUNTA).SELECT
                            oword.Selection.InsertBefore Forms![frm_correspondence]![INVOICE TOTAL]
        End Select
    Next
 
    oword.Visible = True
    oword.Activate
    
    If Forms![frm_correspondence]![COR PATH] <> "" And Not IsNull(Forms![frm_correspondence]![COR PATH]) Then
        FILE_STR = Forms![frm_correspondence]![COR PATH] & "\" & Forms![frm_correspondence]![FILE REF]
        oword.ActiveDocument.SaveAs FILENAME:=FILE_STR
    End If
    DoCmd.Hourglass False
    Set oword = Nothing

Exit_PRINT_DOC:
    Exit Sub

Err_PRINT_DOC:
    MsgBox Error$, vbExclamation, APP_NAME
    Resume Exit_PRINT_DOC
End Sub

 

[combo]

[tt]oword.Documents.Add TEMPLAT[/tt]
Do they have new document from valid template? Later in the code you have [tt]COUNTER = oword.ActiveDocument.FormFields.COUNT[/tt], if you assign 0, the loop won't run.
Why you try to create word again in:

[pre] Set oword = New Word.Application

notloaded:
If Err.NUMBER = 429 Then
Set oword = New Word.Application
theError = Err.NUMBER
End If[/pre]

combo

 

[JackSapsford]

Hi Combo,

The templates are valid and working for other people.

As for creating word again, I'm not sure why that is there, this is code that I have inherrited from the last guy who created it. I'm also not at all familiar with VBA.

Thanks, Jack

 

[combo]

Valid" means the same path, the same name, the same extension as you pass as an argument. Is it still ok?

combo