Right Function

[dhebert]

I get an error message that I am missing an operator. Can somene please help me on this. I have tried using the SQL keyword LTRIM instead and keep getting the same error message. I have highlighted the problem area. Below is the code:

'Delcare variables to change the start date back 180 days in order to 'match all returns to all issues.
MyDate = txtStartDate
MyDate = DateSerial(Left((MyDate), 4), Mid((MyDate), 5, 2), Right((MyDate), 2))
ConDate = DateAdd("d", -180, (MyDate))
CovDate = Format((ConDate), "yyyymmdd")
txtStartDate = CStr(CovDate)
DoCmd.RunSQL "SELECT b.FACILITY, " & _
"a.REFERENCE_NBR AS WORK_ORDER_NBR, " & _
"a.REFERENCE_SUB_NBR AS WORK_ORDER_TASK, " & _
"a.MATERIAL_REQUEST, " & _
"b.CATALOG_ID, " & _
"b.QTY_MTL_RQST, " & _
"Sum(a.TRANS_QTY) AS ISS_TRANS_QTY, " & _
"Sum(a.TRANS_AMT) AS ISS_TRANS_AMT, " & _
"a.TRANS_TYPE, RIGHT([TRANS_TYPE],3) AS TYPE " & _
"INTO mtblIssueDetail " & _
"FROM KWODS_ODS_TIDPBOOK a LEFT JOIN KWODS_ODS_TIDMRDTL b " & _
"ON a.MATERIAL_REQUEST = b.MATERIAL_REQUEST " & _
"AND a.Q_LEVEL = b.Q_LEVEL " & _
"AND a.CATALOG_ID = b.CATALOG_ID " & _
"WHERE a.TRANS_DATE Between (txtStartDate) And (txtEndDate) " & _
"GROUP BY b.FACILITY, a.REFERENCE_NBR, a.REFERENCE_SUB_NBR, " & _
"a.MATERIAL_REQUEST, b.CATALOG_ID, b.QTY_MTL_RQST, a.TRANS_TYPE, RIGHT([TRANS_TYPE],3) AS TYPE " & _
"HAVING b.FACILITY In ('AB1','ABQ','CL1','CLA','DE1','DEM','SF1','SFE','91','92','93') " & _
"AND a.TRANS_TYPE In ('ISSDIR','ISSSTK') " & _
"ORDER BY a.REFERENCE_NBR, a.MATERIAL_REQUEST, b.CATALOG_ID;"

 

[BamaColtsFan]

First look - you should not have functions in your group by clause of any SQL statement (at least to my knowledge). Try taking them out and see how it runs...

 

[dhebert]

That's the answer!!!!! I have been struggling with that for hours.

Thanks for your quick response. Your help is appreciated!!!!!

Dave

 

[SOS]

You can have the group by Right([FieldX],3) in the Group By but you don't include the AS TYPE bit.

Also, you shouldn't name your columns Access Reserved Words. TYPE is an Access Reserved Word.

 

[dhebert]

Yes, I see that worked also. Thanks to both of your for your help with this. It's great to be able to go to resources that can solve simple problems for us beginners.

Thanks again,

Dave