Error 3075 sql string

[namliam]

When opening a recordset from code I dont think you can refer to forms, dont have access right now to actually test.

Instead of doing this

    strSQL = strSQL & " And ((IIf(IsNull([Forms]![frmDocumentRegister]![FilterBy])  , True, [tblDocs].[Section]           = [Forms]![frmDocumentRegister]![FilterBy]))   <> False) "

try something like
If not IsNull([Forms]![frmDocumentRegister]![FilterBy]) then
strSQL = strSQL = "[tblDocs].[Section] = " & [Forms]![frmDocumentRegister]![FilterBy]
endif [/code]
This should work if your section is a number.
For text:

If not IsNull([Forms]![frmDocumentRegister]![FilterBy])  then
    strSQL = strSQL = "[tblDocs].[Section]           = """ & [Forms]![frmDocumentRegister]![FilterBy] & """"
endif

And dates:

If not IsNull([Forms]![frmDocumentRegister]![FilterBy])  then
    strSQL = strSQL = "[tblDocs].[Section]           = #" & [Forms]![frmDocumentRegister]![FilterBy] & "#"
endif

Remember that dates must be entered in US format ( MM/DD/YYYY )

 

[namliam]

Hi Namilam, I dont know how to do that as I believe I would have to rewrite the whole access database. I am only using domain functions as I feel there is no easy way around what I am trying to acheive.

These DLookups look to me to be completely expendable

    strSQL = strSQL & " , IIf([tblDocs].[Active]=0 "
    strSQL = strSQL & "              , ""Withdrawn"" "
    strSQL = strSQL & "              , IIf([tblDocs].[Section]=""Group"" "
    strSQL = strSQL & "                         ,             DLookUp(""[Intranet Prefix]"",""tblDocumentType"", ""[Document Type] = """"Quality Manual"""" "") & [tblDocs].[Document Code] & [Intranet Suffix] "
    strSQL = strSQL & "                         , IIf([tblDocs].[Section]=""HSE"" "
    strSQL = strSQL & "                                     , DLookUp(""[Intranet Prefix]"",""tblDocumentType"",""[Document Type] = """"LWI"""" "")             & [tblDocs].[Document Code] & [Intranet Suffix] "
    strSQL = strSQL & "                                     ,          ([Intranet Prefix]                                                                       & [tblDocs].[Document Code] & [Intranet Suffix])))) AS iLink  "

In each case you end up fetching [Intranet Prefix] based on your [Document Type] in the table tblDocumentType
So either you join structure is wrong
Your database is flawed
or you should simply be able to remove the entire Dlookups.