Results 1 to 12 of 12
  1. #1
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74

    Run-time error '3075': Syntax error in string in query expression

    Hello,

    Having a problem running a query. My initial problem is that in the licensing database that I created last year when printing a license it is not bringing up the last master driver and issuing officer on the license. To be more clear I will put the tables and structures below along with the SQL for the license query. I am trying to use a top 1 of group query from an article http://support.microsoft.com/kb/153747 (method two) which was suggested on utteraccess.com and this is when I get the run-time error when I try to run the query.

    TblEmpInfo:
    EmpBadgeNum Txt (PK)
    EmpLastName Txt
    EmpFirstName Txt
    EmpMidInital Txt


    EmpDOB Date/Time-Medium
    EmpMyID Txt
    EmpActive Yes/No
    EmpCompany Txt
    EmpSubcontractor Yes/No
    EmpDivision Txt
    EmpSectionNum Number Double-General

    TblEmpLicInfo:
    EmpBadgeNum Txt (PK)
    EmpAACDate Date/Time-Medium
    EmpStateLicExpDate Date/Time-Medium
    EmpStateLicClass Txt
    EmpClassAReq Yes/No
    EmpBPEndorsmentReq Yes/No
    EmpNGLicIssDate Date/Time-Medium
    EmpMedExamDate Date/Time-Medium
    IOBadgeNum Number (FK from TblIssueOfficialInfo) (this isthe clerk inputting info)
    MasterDriver Yes/No

    TblEmpTraining:
    ID Autonumber (PK)
    EmpBadgeNum Txt (FK from TblEmpInfo)
    EquipmentType Number (FK from TblEquip)
    Forklift Yes/No
    EmpTrainBypass Yes/No
    TrainHrsCompleted Number
    TrainDateComplete Date/Time-Medium
    MDBadgeNum Number (FK from TblMasterDriver)
    TrainArchive Yes/No
    TrainArchiveDate Date/Time-Medium

    TblEquip:
    ID Autonumber (PK)
    InputDate Date/Time-Medium
    EquipmentType Txt
    TrainingHoursReq Number
    Remarks Txt
    Archive Yes/No
    ArchiveDate Date/Time-Medium

    TblIssueOfficialInfo:
    ID Autonumber (PK)
    IOBadgeNum Txt
    IOFirstName Txt
    IOLastName Txt
    IOMidInital Txt
    IODOB Date/Time-Medium
    IOMyID Txt
    IOActive Yes/No
    IOCompany Txt
    IOSubcontractor Yes/No
    IODivision Txt
    IOSectionNumb Number

    TblMasterDriver:
    ID Autonumber (PK)
    MDBadgeNum Txt
    MDFirstName Txt
    MDLastName Txt
    MDMidInital Txt
    MDDOB Date/Time-Medium
    MDMyID Txt
    MDActive Yes/No
    MDCompany Txt
    MDSubcontractor Yes/No
    MDDivision Txt
    MDSectionNumb Number
    QryOperatorsIDCard SQL:
    SELECT TblEmpInfo.EmpBadgeNum, TblEmpInfo.EmpLastName,TblEmpInfo.EmpFirstName, [EmpLastName] & ", " &[EmpFirstName] AS EmpNameCombined, TblEmpInfo.EmpMidInital, TblEmpInfo.EmpDOB,TblEmpInfo.EmpMyID, TblEmpLicInfo.EmpAACDate, QryNGLicExpDate.EmpAACExpDate,TblEmpLicInfo.EmpNGL icIssDate, QryNGLicExpDate.NGLicExpDate,TblEmpLicInfo.EmpStat eLicExpDate, TblEmpLicInfo.EmpStateLicClass,TblEmpLicInfo.EmpCl assAReq, TblEmpLicInfo.EmpBPEndorsmentReq,TblIssueOfficialI nfo.IOBadgeNum, TblIssueOfficialInfo.IOFirstName,TblIssueOfficialI nfo.IOLastName, [IOLastName] & ", " &[IOFirstName] AS IONameCombined, TblMasterDriver.MDBadgeNum,TblMasterDriver.MDFirst Name, TblMasterDriver.MDLastName, [MDLastName] &", " & [MDFirstName] AS MDNameCombined,TblEmpTraining.EquipmentType, TblEmpTraining.TrainArchive,TblEmpInfo.EmpActive, TblEmpInfo.EmpSubcontractor, TblEmpInfo.EmpSectionNum,TblEmpLicInfo.MasterDrive r
    FROM TblMasterDriver INNER JOIN (TblIssueOfficialInfo INNERJOIN (TblEquip INNER JOIN ((((TblEmpInfo INNER JOIN QryNGLicExpDate ONTblEmpInfo.EmpBadgeNum = QryNGLicExpDate.EmpBadgeNum) INNER JOINQryForkliftTrainExpDate ON TblEmpInfo.EmpBadgeNum =QryForkliftTrainExpDate.EmpBadgeNum) INNER JOIN TblEmpLicInfo ONTblEmpInfo.EmpBadgeNum = TblEmpLicInfo.EmpBadgeNum) INNER JOIN TblEmpTrainingON TblEmpInfo.EmpBadgeNum = TblEmpTraining.EmpBadgeNum) ON TblEquip.ID =TblEmpTraining.EquipmentType) ON TblIssueOfficialInfo.ID = TblEmpLicInfo.IOBadgeNum)ON TblMasterDriver.ID = TblEmpTraining.MDBadgeNum
    GROUP BY TblEmpInfo.EmpBadgeNum, TblEmpInfo.EmpLastName,TblEmpInfo.EmpFirstName, [EmpLastName] & ", " &[EmpFirstName], TblEmpInfo.EmpMidInital, TblEmpInfo.EmpDOB, TblEmpInfo.EmpMyID,TblEmpLicInfo.EmpAACDate, QryNGLicExpDate.EmpAACExpDate,TblEmpLicInfo.EmpNGL icIssDate, QryNGLicExpDate.NGLicExpDate,TblEmpLicInfo.EmpStat eLicExpDate, TblEmpLicInfo.EmpStateLicClass,TblEmpLicInfo.EmpCl assAReq, TblEmpLicInfo.EmpBPEndorsmentReq,TblIssueOfficialI nfo.IOBadgeNum, TblIssueOfficialInfo.IOFirstName,TblIssueOfficialI nfo.IOLastName, [IOLastName] & ", " &[IOFirstName], TblMasterDriver.MDBadgeNum, TblMasterDriver.MDFirstName,TblMasterDriver.MDLast Name, [MDLastName] & ", " & [MDFirstName],TblEmpTraining.EquipmentType, TblEmpTraining.TrainArchive,TblEmpInfo.EmpActive, TblEmpInfo.EmpSubcontractor, TblEmpInfo.EmpSectionNum,TblEmpLicInfo.MasterDrive r
    HAVING (((TblEmpTraining.TrainArchive) Like False) AND((TblEmpInfo.EmpActive) Like True))
    ORDERBY [EmpLastName] & ", " & [EmpFirstName];

    Here is the SQL for the query that will not run:

    SELECT TblEmpInfo.EmpBadgeNum, TblEmpInfo.EmpLastName, TblEmpInfo.EmpFirstName, TblEmpTraining.TrainDateComplete, TblEmpTraining.MDBadgeNum
    FROM TblEmpInfo INNER JOIN TblEmpTraining ON TblEmpInfo.EmpBadgeNum = TblEmpTraining.EmpBadgeNum
    WHERE (((TblEmpTraining.TrainDateComplete)>=NthInGroup([TblEmpTraining].[MDBadgeNum],1)))
    ORDER BY TblEmpInfo.EmpLastName, TblEmpTraining.TrainDateComplete DESC;

    When I click debug it brings up the VBA:

    Option Explicit

    Function NthInGroup(GroupID, N)
    ' Returns the Nth Item in GroupID for use as a Top N per group
    ' query criteria.
    Static LastGroupId, LastNthInGroup
    Dim ItemName, GroupIDName, GDC, SearchTable
    Dim SQL As String, rs As Recordset, db As Database

    If (LastGroupId = GroupID) Then
    ' Returned saved result if function is called with the
    ' same GroupID more than once in a row.
    NthInGroup = LastNthInGroup
    Else
    ' Set the SQL statement parameters. These are the only items
    ' that need to be customized in this function.
    ' Set to Item field name.
    ItemName = "TrainDateComplete"
    ' Set to Group ID field name.
    GroupIDName = "MDBadgeNum"
    ' GroupID Delimiter Character:
    ' For Text use "'" (Note that this is a quotation mark, a space,
    ' an apostrophe, a space, and then a quotation mark. The spaces
    ' are necessary for SQL statements), Date "#", Numeric ""
    GDC = "'"
    ' Set to search table.
    SearchTable = "TblEmpTraining"
    ' Build a Top N SQL statement dynamically given N and
    ' GroupID as parameters. Note that the sort is by the
    ' item in descending order, in order to get the Top N
    ' largest items.
    SQL = "Select Top " & N & " [" & ItemName & "] "
    SQL = SQL & "From [" & SearchTable & "] "
    SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC _
    & " ' "
    SQL = SQL & "Order By [" & ItemName & "] Desc"
    ' Open up recordset on Top N SQL statement and read the
    ' last record to get the smallest item in the Top N.
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(SQL)
    If (rs.BOF And rs.EOF) Then
    ' No matches found, return a null.
    LastNthInGroup = Null
    LastGroupId = GroupID
    NthInGroup = LastNthInGroup
    Else
    ' Return the smallest Top N item in the group.
    rs.MoveLast
    LastNthInGroup = rs(ItemName)
    LastGroupId = GroupID
    NthInGroup = LastNthInGroup
    End If
    End If

    End Function
    (This is what I used from the microsoft link). (The bold line is where it takes me when I click debug).

    I would appreciate any help with this.

    Attaching relationship pdf

    Relationships for Licensing Database.pdf



    Last edited by FranCorona; 05-01-2014 at 10:04 AM. Reason: Attaching Relationships pdf

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use this to see how the SQL string is ending up:

    BaldyWeb-Immediate window

    What is the data type of the field in the criteria? If it's numeric, you don't want this line:

    GDC = "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Wow, a lot of information. Have you looked carefully at your syntax in the query? What is the exact syntax for the field in your query that is giving the problem?

  4. #4
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74
    Hi Paul,

    It is actually numeric in TblEmpTraining, it is a field key from TblMasterDriver. I changed it to GDC = "" and now getting same syntax error but with:

    Syntax error (missing operator) in query expression '[MDBadgeNum]='.

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can that field be Null? It appears that the SQL isn't seeing the value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    WHERE (((TblEmpTraining.TrainDateComplete)>=NthInGroup([TblEmpTraining].[MDBadgeNum],1)))

    What field type is TrainDateComplete? What data type is returned by the function?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74
    TrainDateComplete is a date field-medium and MDBadgeNum in TbleEmpTraining is numeric-long. When a license is updated and printed the training date is input in a subform of the main input form.

  8. #8
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74
    And Paul, no MDBadgeNum isn't null but I think I may have figured out the possible problem. In the license query I have the MDBadgeNum pulling from the TblMasterDriver and not the TblEmpTraining..... I am going test it out now and see if that's the problem.

  9. #9
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74
    Ok it worked for the initial training but if it's a reresher training then it isn't pulling the correct name obviouly because the reresher training is from a different table....so now how do get it to pull the most recent master driver if it was a refresher training?

  10. #10
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74
    Ok, I just talked to the licensing administrator and she said I don't need to incorporate the refresher training so that means I am good! Thank you so much for your help! Have a great day!

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why separate tables for initial and refresher? Are the data/fields really different?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    FranCorona is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    May 2013
    Location
    California
    Posts
    74
    Because she wanted to be able to see who the master driver was for the initial training and if we add the reresher training to that table and if it was a different master driver that does the refresher training then the name would change. The refresher training isn't tied to the license at all, it is just to show that they did get the refresher training on equipment that require it and who did the training. It was a duh moment. I didn't even need the top 1 of group query at all, just needed to correct the actual query. Thanks again for your guys' time!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Error 3075 Syntax Error
    By jbailey4545 in forum Queries
    Replies: 8
    Last Post: 03-11-2014, 01:34 PM
  2. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  3. Replies: 4
    Last Post: 07-25-2012, 04:01 AM
  4. Replies: 6
    Last Post: 05-30-2012, 12:32 PM
  5. Replies: 5
    Last Post: 03-27-2012, 01:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums