Run-time error 3141 - Access Conversion from 2003 to 2010

[mrcs4life]

I've been stuck on this same sql statement for way too long and I'm about to throw my hands in the air and walk away. I'm just learning this database stuff. This original database was 2003 and I converted it to 2010. Nothing is working for me. For example, I'm trying to view a report and I keep receiving an error message: The SELECT statement includes a reserved word or an argument that is misspelled or missing, or the punctuation is incorrect. I got to the module and debug and I've been stuck here for weeks.

"Option Compare Database
Const folder_Name = "\\waufs1-cluster\shared\Claims Res. Wausau\Access Files\"

Function Age_Status()
    Dim sql_criteria As String
    Dim rst_report
    Dim sql_LessAge, sql_GreaterAge As String
    Dim sql_LessLAge, sql_GreaterLAge As String
    Dim sql_Location As String
    Dim contract, contract_name, contract_file As String
    Dim tmp_report As String
    Dim strcount As Long

    contract = call_Contract
    If (contract = "allexcel") Then
        contract_name = "Tdefic"
    Else
        contract_name = UCase(Left(contract, 1)) & Right(contract, Len(contract) - 1)
    End If
    contract_file = Check_Files(contract)
    If (contract_file = "") Then
        Exit Function
    End If
    File_Time = FileDateTime(folder_Name & contract_file & ".txt")
    tmp_report = fosusername
    
    If (Nz(Forms![frm_Main]![LESSTHANAGE], "") <> "") Then
        sql_LessAge = "(([AGE])<[Forms]![frm_Main]![LESSTHANAGE]) AND "
    End If
    If (Nz(Forms![frm_Main]![greaterthanage], "") <> "") Then
        sql_GreaterAge = "(([AGE])>[Forms]![frm_Main]![greaterthanage]) AND "
    End If
    If Nz(Forms![frm_Main]![combolocation], "") <> "" Then
        sql_loc = "((tbl_" & contract_file & ".[LOC]) = [Forms]![frm_Main]![combolocation]) AND "
    End If
        sql_criteria = "SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non, Count(IIf([N/I]='I','YES')) AS Inst, Count(IIf([N/I]='O','YES')) AS Outpt, Count(ICN) AS Total, '" & contract_name & "' AS CONTRACT, '" & File_Time & "' AS [TIME] into " & tmp_report & _
"FROM tbl_" & contract_file & " INNER JOIN " & contract_name & "_LOCS ON tbl_" & contract_file & ".LOC = " & contract_name & "_LOCS.LOC" & _
"WHERE ((" & sql_loc & sql_LessAge & sql_GreaterAge & "(" & contract_name & "_LOCS.DEPT) = [Forms]![frm_Main]![combodepartment]))" & _
"GROUP BY AGE" & _
"ORDER BY AGE DESC;"



    DoCmd.SetWarnings False
    DoCmd.RunSQL (sql_criteria)<----(this is highlighed yellow after debugging)

    DoCmd.OpenReport "rpt_AgeStatus", acViewPreview

    DoCmd.SetWarnings True
End Function"

[zmbd]

What is is telling you is that your string on line 36...40 is malformed

Insert a

debug.print sql_criteria

on line 41
Run your code.
Once it errors, press <ctrl><g> to open the immediates window.

You will find therein the resolved SQL string you are attempting to use.

Please review it, if you do not see the error then post back here the resolved string... when you do so:

Please, click on the [CODE/] button in the post toolbar and then cut and paste your script between the [CODE] [/CODE] tags.

[zmbd]

I can tell you right now that I've found one missed double quote and "AS [TIME]" Time is a reserved token.

Still reveiwing sql_criteria; however, you should take a look at this information:

-It is best practice when naming fields, tables, and files to avoid the use of anything other than alphanumeric characters and the underscore (spaces although allowed are problematic from a programing point of view and best avoided) and it is VERY importaint to avoid all reserved words and tokens:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

[zmbd]

Refering to the trouble shooting section of:
> Before Posting (VBA or SQL) Code

Uncomment:Option Compare Database
Add:Option Explicit
Think I fixed the typo in the following. I still hightly advise the debug.print sql_criteria to check the resolved string.

I also recommend following the troubleshooting instructions in the above link. You may have to repeat the compile step several times as the compiler stops on the first line of error found.

sql_criteria = _
     "SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non" & _
         ", Count(IIf([N/I]='I','YES')) AS Inst" & _
         ", Count(IIf([N/I]='O','YES')) AS Outpt" & _
         ", Count(ICN) AS Total, '" & contract_name & "' AS CONTRACT" & _
         ", '" & File_Time & "' AS [TIME]" & _
         " into " & tmp_report & _
     " FROM tbl_" & contract_file & _
         " INNER JOIN " & contract_name & "_LOCS" & _
             " ON tbl_" & contract_file & ".LOC = " & contract_name & "_LOCS.LOC" & _
      " WHERE ((" & _
         sql_loc & sql_LessAge & sql_GreaterAge & _
         "(" & contract_name & "_LOCS.DEPT) =" & _
         [Forms]![frm_Main]![combodepartment] & "))" & _
      " GROUP BY AGE" & _
      " ORDER BY AGE DESC;"

Please read thru the links provided about naming... using "/" token words, etc... may cause you serious headaches. Especially if you might upsize the database at any point in the future.

-z

[mrcs4life]

Thank you! I believe that I followed your instructions correctly but now I'm getting a "Variable not defined" error message. Here is the code that I have now:

ption Compare Database
Option Explicit
Const folder_Name = "\\waufs1-cluster\shared\Claims Res. Wausau\Access Files\"

Function Age_Status()
    Dim sql_criteria As String
    Dim rst_report
    Dim sql_LessAge, sql_GreaterAge As String
    Dim sql_LessLAge, sql_GreaterLAge As String
    Dim sql_Location As String
    Dim contract, contract_name, contract_file As String
    Dim tmp_report As String
    Dim strcount As Long

    contract = call_Contract
    If (contract = "allexcel") Then
        contract_name = "Tdefic"
    Else
        contract_name = UCase(Left(contract, 1)) & Right(contract, Len(contract) - 1)
    End If
    contract_file = Check_Files(contract)
    If (contract_file = "") Then
        Exit Function
    End If
    File_Time = FileDateTime(folder_Name & contract_file & ".txt")
    tmp_report = fosusername
    
    If (Nz(Forms![frm_Main]![LESSTHANAGE], "") <> "") Then
        sql_LessAge = "(([AGE])<[Forms]![frm_Main]![LESSTHANAGE]) AND "
    End If
    If (Nz(Forms![frm_Main]![greaterthanage], "") <> "") Then
        sql_GreaterAge = "(([AGE])>[Forms]![frm_Main]![greaterthanage]) AND "
    End If
    If Nz(Forms![frm_Main]![combolocation], "") <> "" Then
        sql_loc = "((tbl_" & contract_file & ".[LOC]) = [Forms]![frm_Main]![combolocation]) AND "
    End If
        sql_criteria = "SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non, Count(IIf([N/I]='I','YES')) AS Inst, Count(IIf([N/I]='O','YES')) AS Outpt, Count(ICN) AS Total, '" & contract_name & "' AS CONTRACT, '" & File_Time & "' "AS [TIME]" into " & tmp_report & _
"FROM tbl_" & contract_file & " INNER JOIN " & contract_name & "_LOCS ON tbl_" & contract_file & ".LOC = " & contract_name & "_LOCS.LOC" & _
"WHERE ((" & sql_loc & sql_LessAge & sql_GreaterAge & "(" & contract_name & "_LOCS.DEPT) = [Forms]![frm_Main]![combodepartment]))" & _
"GROUP BY AGE" & _
"ORDER BY AGE DESC;"
Debug.Print sql_criteria


    DoCmd.SetWarnings False
    DoCmd.RunSQL (sql_criteria)

    DoCmd.OpenReport "rpt_AgeStatus", acViewPreview

    DoCmd.SetWarnings True
End Function

Also, I'm not certain by what you mean of "resolved" sql. The lines that appeared in Immediate doesn't show me where my error is? or does it? I've tried it multiple times.

SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non, Count(IIf([N/I]='I','YES')) AS Inst, Count(IIf([N/I]='O','YES')) AS Outpt, Count(ICN) AS Total, 'West' AS CONTRACT, '5/22/2014 6:34:43 AM' AS [TIME] into FROM tbl_west INNER JOIN West_LOCS ON tbl_west.LOC = West_LOCS.LOCWHERE ((((tbl_west.[LOC]) = [Forms]![frm_Main]![combolocation]) AND (West_LOCS.DEPT) = [Forms]![frm_Main]![combodepartment]))GROUP BY AGEORDER BY AGE DESC;
SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non, Count(IIf([N/I]='I','YES')) AS Inst, Count(IIf([N/I]='O','YES')) AS Outpt, Count(ICN) AS Total, 'Tdefic' AS CONTRACT, '5/22/2014 6:59:36 AM' AS [TIME] into FROM tbl_allexcel INNER JOIN Tdefic_LOCS ON tbl_allexcel.LOC = Tdefic_LOCS.LOCWHERE ((((tbl_allexcel.[LOC]) = [Forms]![frm_Main]![combolocation]) AND (Tdefic_LOCS.DEPT) = [Forms]![frm_Main]![combodepartment]))GROUP BY AGEORDER BY AGE DESC;
SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non, Count(IIf([N/I]='I','YES')) AS Inst, Count(IIf([N/I]='O','YES')) AS Outpt, Count(ICN) AS Total, 'Foreign' AS CONTRACT, '5/22/2014 7:00:26 AM' AS [TIME] into FROM tbl_foreign INNER JOIN Foreign_LOCS ON tbl_foreign.LOC = Foreign_LOCS.LOCWHERE ((((tbl_foreign.[LOC]) = [Forms]![frm_Main]![combolocation]) AND (Foreign_LOCS.DEPT) = [Forms]![frm_Main]![combodepartment]))GROUP BY AGEORDER BY AGE DESC;
SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non, Count(IIf([N/I]='I','YES')) AS Inst, Count(IIf([N/I]='O','YES')) AS Outpt, Count(ICN) AS Total, 'Foreign' AS CONTRACT, '5/22/2014 7:00:26 AM' AS [TIME] into FROM tbl_foreign INNER JOIN Foreign_LOCS ON tbl_foreign.LOC = Foreign_LOCS.LOCWHERE ((((tbl_foreign.[LOC]) = [Forms]![frm_Main]![combolocation]) AND (Foreign_LOCS.DEPT) = [Forms]![frm_Main]![combodepartment]))GROUP BY AGEORDER BY AGE DESC;

[jimatqsi]

mrcs4life,
I've seen folks get away with using keywords like "Time" for column names but it is surely not a good idea. If you can, change that column name before proceeding. But it might not be a deal breaker.

Is the field you are calling Time a string? Your SQL code is treating it like a string. If it is actually a date/time field then you need to replace ' with # before and after the Time value.

When I tried to compile your code I got a complaint about Call_Contract in line 15 of your code shown above. That is not dimmed anywhere.

Jim

[zmbd]

In the posted strings:

The placement of the reserved word "into" is wrong. So even if we get the rest of the string resolved this will not work in Access... just noticed this in the resolved string.

actualy it looks like OP is attempting call a field named as as date time.

also in the posted string, the name of the form control and not the value of the form control is given, surely that was not the intent

Compare your code in post#5 with the sql_criteria in post #4
See the difference? Spacing and punctuation works in the SQL world.

as for the error:

As Jim said the error refers to an undefined/declared variable.
Please read the trouble shooting section of the link I gave in Post#4. You will need to repeat the debug\compile step until you have no errors to fix.

Please read the two links provided about using reserved words and tokens.

[NeoPa]

You may find Require Variable Declaration a helpful link as it explains why it makes so much sense to use Option Explicit in your code. It usually does end up complaining about undeclared variables. That's good. Even if it seems a nuisance.

Furthermore, unfortunately for you and all of us I believe sometimes, Dimming a variable as String on a line effects only that single variable explicitly defined. All others on the line, if not typed explicitly, default to Variant. So for instance, from your original post, line #7 types sql_GreaterAge correctly as a String, but leaves sql_LessAge as a Variant. I'm guessing that wasn't your intention ;-)

These are concepts that are often misunderstood by new users so I hope you don't mind my calling them to your attention. The understanding should help you in future.

[mrcs4life]

I'm not sure what's wrong with line #7 sql_GreaterAge? NeoPa stated that I have sql_GreaterAge correctly as a string but I left sql_LessAge as a Variant. They look the same to me. I can't get passed that point now and my sql criteria seems completely messed up now. I'm so frustrated.

[jimatqsi]

What Neopa is saying is that you should change

Dim sql_LessAge, sql_GreaterAge As String
    Dim sql_LessLAge, sql_GreaterLAge As String

to

Dim sql_LessAge as string
dim sql_GreaterAge As String
Dim sql_LessLAge as strng
dim sql_GreaterLAge As String

His advice is that you explicitly declare each variable. When you try to do two on one line you may not get the result you think you will get.

Change your declares as NeoPa suggested and then let us know what the next problem is. It can be very frustrating for those new to any language. We're here to help.

Jim

[mrcs4life]

Thank you, jimatqsi. Something so simple in NeoPa's post but also so much going on at the same time. I understand now what he was saying after you commented.
I talked with the end user and I can remove the time/date stamp that is coded in this module. I've tried commenting it it out but that doesn't seem to work. I'm also not sure how to eliminate it in my sql criteria. Here is my current vba:

Option Compare Database
Option Explicit
Const folder_Name = "\\waufs1-cluster\shared\Claims Res. Wausau\Access Files\"

Function Age_Status()
    Dim sql_criteria As String
    Dim rst_report
    Dim sql_LessAge As String
    Dim sql_GreaterAge As String
    Dim sql_LessLAge As String
    Dim sql_GreaterLAge As String
    Dim sql_Loc As String
    Dim contract, contract_name, contract_file As String
    Dim tmp_report As String
    Dim strcount As Long

    contract = call_Contract
    If (contract = "allexcel") Then
        contract_name = "Tdefic"
    Else
        contract_name = UCase(Left(contract, 1)) & Right(contract, Len(contract) - 1)
    End If
    contract_file = Check_Files(contract)
    If (contract_file = "") Then
        Exit Function
    End If
    File_Time = FileDateTime(folder_Name & contract_file & ".txt")
    tmp_report = fosusername
    
    If (Nz(Forms![frm_Main]![LESSTHANAGE], "") <> "") Then
        sql_LessAge = "(([AGE])<[Forms]![frm_Main]![LESSTHANAGE]) AND "
    End If
    If (Nz(Forms![frm_Main]![greaterthanage], "") <> "") Then
        sql_GreaterAge = "(([AGE])>[Forms]![frm_Main]![greaterthanage]) AND "
    End If
    If Nz(Forms![frm_Main]![combolocation], "") <> "" Then
        sql_Loc = "((tbl_" & contract_file & ".[LOC]) = [Forms]![frm_Main]![combolocation]) AND "
    End If
        sql_criteria = _
     "SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non" & _
         ", Count(IIf([N/I]='I','YES')) AS Inst" & _
         ", Count(IIf([N/I]='O','YES')) AS Outpt" & _
         ", Count(ICN) AS Total, '" & contract_name & "' AS CONTRACT" & _
         ", '" & File_Time & "' AS [TIME]" & _
         " into " & tmp_report & _
     " FROM tbl_" & contract_file & _
         " INNER JOIN " & contract_name & "_LOCS" & _
             " ON tbl_" & contract_file & ".LOC = " & contract_name & "_LOCS.LOC" & _
      " WHERE ((" & _
         sql_Loc & sql_LessAge & sql_GreaterAge & _
         "(" & contract_name & "_LOCS.DEPT) =" & _
         [Forms]![frm_Main]![combodepartment] & "))" & _
      " GROUP BY AGE" & _
      " ORDER BY AGE DESC;"

Debug.Print sql_criteria
      
    DoCmd.SetWarnings False
    DoCmd.RunSQL (sql_criteria)

    DoCmd.OpenReport "rpt_AgeStatus", acViewPreview

    DoCmd.SetWarnings True
End Function

[jimatqsi]

But what is the error? What is wrong with this code?

[mrcs4life]

I'm sorry! Line 27 - Variable not defined.

Option Compare Database
Option Explicit
Const folder_Name = "\\waufs1-cluster\shared\Claims Res. Wausau\Access Files\"

Function Age_Status()
    Dim sql_criteria As String
    Dim rst_report
    Dim sql_LessAge As String
    Dim sql_GreaterAge As String
    Dim sql_LessLAge As String
    Dim sql_GreaterLAge As String
    Dim sql_Loc As String
    Dim contract, contract_name, contract_file As String
    Dim tmp_report As String
    Dim strcount As Long

    contract = call_Contract
    If (contract = "allexcel") Then
        contract_name = "Tdefic"
    Else
        contract_name = UCase(Left(contract, 1)) & Right(contract, Len(contract) - 1)
    End If
    contract_file = Check_Files(contract)
    If (contract_file = "") Then
        Exit Function
    End If
    File_Time = FileDateTime(folder_Name & contract_file & ".txt")
    tmp_report = fosusername
    
    If (Nz(Forms![frm_Main]![LESSTHANAGE], "") <> "") Then
        sql_LessAge = "(([AGE])<[Forms]![frm_Main]![LESSTHANAGE]) AND "
    End If
    If (Nz(Forms![frm_Main]![greaterthanage], "") <> "") Then
        sql_GreaterAge = "(([AGE])>[Forms]![frm_Main]![greaterthanage]) AND "
    End If
    If Nz(Forms![frm_Main]![combolocation], "") <> "" Then
        sql_Loc = "((tbl_" & contract_file & ".[LOC]) = [Forms]![frm_Main]![combolocation]) AND "
    End If
        sql_criteria = _
     "SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non" & _
         ", Count(IIf([N/I]='I','YES')) AS Inst" & _
         ", Count(IIf([N/I]='O','YES')) AS Outpt" & _
         ", Count(ICN) AS Total, '" & contract_name & "' AS CONTRACT" & _
         ", '" & File_Time & "' AS [TIME]" & _
         " into " & tmp_report & _
     " FROM tbl_" & contract_file & _
         " INNER JOIN " & contract_name & "_LOCS" & _
             " ON tbl_" & contract_file & ".LOC = " & contract_name & "_LOCS.LOC" & _
      " WHERE ((" & _
         sql_Loc & sql_LessAge & sql_GreaterAge & _
         "(" & contract_name & "_LOCS.DEPT) =" & _
         [Forms]![frm_Main]![combodepartment] & "))" & _
      " GROUP BY AGE" & _
      " ORDER BY AGE DESC;"

Debug.Print sql_criteria
      
    DoCmd.SetWarnings False
    DoCmd.RunSQL (sql_criteria)

    DoCmd.OpenReport "rpt_AgeStatus", acViewPreview

    DoCmd.SetWarnings True
End Function

[NeoPa]

File_Time = FileDateTime(folder_Name & contract_file & ".txt")

File_Time has not been declared anywhere.

As a follow-up to what I was saying, which Jim explained further for you, I'd just like to clarify what might be a misunderstandin g.

I generally DO declare multiple variables on the same line. However, only variables of the same type and each variable has the type set explicitly.

So, for example, my version of your line #13 would be :

Dim contract As String, contract_name As String, contract_file As String

instead of :

Dim contract, contract_name, contract_file As String

Each named variable that isn't followed by an " As ..." is created as a Variant. This is true even if other variables within the same Dim statement do have " As ..." after them.
Typically I limit my lines to 80 characters too, but that's another story.

[mrcs4life]

Thank you, NeoPa. That makes much more sense. I've changed some things in my code and now I'm receiving a syntax error. I've attached that as well as my code. When it errors out, it highlights in yellow "DoCmd.RunS QL (sql_criteria)

Option Compare Database
Option Explicit
Const folder_Name = "\\waufs1-cluster\shared\Claims Res. Wausau\Access Files\"

Function Age_Status()
    Dim sql_criteria As String
    Dim rst_report
    Dim sql_LessAge As String
    Dim sql_GreaterAge As String
    Dim sql_LessLAge As String
    Dim sql_GreaterLAge As String
    Dim sql_Loc As String
    Dim contract, contract_name, contract_file As String
    Dim tmp_report As String
    Dim strcount As Long
    Dim File_Time As String
    

    contract = call_Contract
    If (contract = "allexcel") Then
        contract_name = "Tdefic"
    Else
        contract_name = UCase(Left(contract, 1)) & Right(contract, Len(contract) - 1)
    End If
    contract_file = Check_Files(contract)
    If (contract_file = "") Then
        Exit Function
    End If
    File_Time = FileDateTime(folder_Name & contract_file & ".txt")
'    tmp_report = fosusername
    
    If (Nz(Forms![frm_Main]![LESSTHANAGE], "") <> "") Then
        sql_LessAge = "(([AGE])<[Forms]![frm_Main]![LESSTHANAGE]) AND "
    End If
    If (Nz(Forms![frm_Main]![greaterthanage], "") <> "") Then
        sql_GreaterAge = "(([AGE])>[Forms]![frm_Main]![greaterthanage]) AND "
    End If
    If Nz(Forms![frm_Main]![combolocation], "") <> "" Then
        sql_Loc = "((tbl_" & contract_file & ".[LOC]) = [Forms]![frm_Main]![combolocation]) AND "
    End If
        sql_criteria = _
     "SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non" & _
         ", Count(IIf([N/I]='I','YES')) AS Inst" & _
         ", Count(IIf([N/I]='O','YES')) AS Outpt" & _
         ", Count(ICN) AS Total, '" & contract_name & "' AS CONTRACT" & _
         ", '" & File_Time & _
         " into " & tmp_report & _
     " FROM tbl_" & contract_file & _
         " INNER JOIN " & contract_name & "_LOCS" & _
             " ON tbl_" & contract_file & ".LOC = " & contract_name & "_LOCS.LOC" & _
      " WHERE ((" & _
         sql_Loc & sql_LessAge & sql_GreaterAge & _
         "(" & contract_name & "_LOCS.DEPT) =" & _
         [Forms]![frm_Main]![combodepartment] & "))" & _
      " GROUP BY AGE" & _
      " ORDER BY AGE DESC;"
      
Debug.Print sql_criteria
      
    DoCmd.SetWarnings False
    DoCmd.RunSQL (sql_criteria)

    DoCmd.OpenReport "rpt_AgeStatus", acViewPreview

    DoCmd.SetWarnings True
End Function

When I get the immediate error by pressing Ctrl+G, I get this:

SELECT AGE, Count(IIf([N/I]='N','YES')) AS Non, Count(IIf([N/I]='I','YES')) AS Inst, Count(IIf([N/I]='O','YES')) AS Outpt, Count(ICN) AS Total, 'Tdefic' AS CONTRACT, '6/3/2014 7:02:56 AM' AS [TIME] into  FROM tbl_allexcel INNER JOIN Tdefic_LOCS ON tbl_allexcel.LOC = Tdefic_LOCS.LOC WHERE ((((tbl_allexcel.[LOC]) = [Forms]![frm_Main]![combolocation]) AND (Tdefic_LOCS.DEPT) =CLMS RES)) GROUP BY AGE ORDER BY AGE DESC;