Dim db As Database
Dim rs As Recordset
Set db = DBEngine.Workspaces(0).Databases(0) ' Create database reference.
Dim strSQL As String
strSQL = "SELECT InvoiceOrderEntry.InvoiceID" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.InvoiceType" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.MainID" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.InvoiceNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.OriginalShippingDate" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.ShipDate" & vbCrLf
strSQL = strSQL & " , Products.ProductID" & vbCrLf
strSQL = strSQL & " , Products.ProductModelNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.UnitSerialNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.WarrantyPeriod" & vbCrLf
strSQL = strSQL & " , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),""Expired"",IIf(IsNull([OriginalShippingDate]),""Unknown"",""Active"")) AS WarrantyStatus" & vbCrLf
strSQL = strSQL & " , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),False,IIf(IsNull([OriginalShippingDate]),False,True)) AS UnderWarrantyYN" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.Technician" & vbCrLf
strSQL = strSQL & " , Sum(InvoiceOrderDetails.OrderAmountMGC) AS SumOfOrderAmountMGC" & vbCrLf
strSQL = strSQL & " FROM ((InvoiceOrderEntry " & vbCrLf
strSQL = strSQL & " INNER JOIN RepairsRMAInformation
strSQL = strSQL & " ON InvoiceOrderEntry.InvoiceID = RepairsRMAInformation.InvoiceID) " & vbCrLf
strSQL = strSQL & " INNER JOIN Products " & vbCrLf
strSQL = strSQL & " ON InvoiceOrderEntry.ProductModelNumber = Products.ProductModelNumber) " & vbCrLf
strSQL = strSQL & " INNER JOIN InvoiceOrderDetails " & vbCrLf
strSQL = strSQL & " ON InvoiceOrderEntry.InvoiceID = InvoiceOrderDetails.InvoiceID" & vbCrLf
strSQL = strSQL & " WHERE (((InvoiceOrderEntry.Shipped)=True))" & vbCrLf
strSQL = strSQL & " GROUP BY InvoiceOrderEntry.InvoiceID" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.InvoiceType" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.MainID" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.InvoiceNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.OriginalShippingDate" & vbCrLf
strSQL = strSQL & " , InvoiceOrderEntry.ShipDate" & vbCrLf
strSQL = strSQL & " , Products.ProductID" & vbCrLf
strSQL = strSQL & " , Products.ProductModelNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.UnitSerialNumber" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.WarrantyPeriod" & vbCrLf
strSQL = strSQL & " , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),""Expired"",IIf(IsNull([OriginalShippingDate]),""Unknown"",""Active""))" & vbCrLf
strSQL = strSQL & " , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),False,IIf(IsNull([OriginalShippingDate]),False,True))" & vbCrLf
strSQL = strSQL & " , RepairsRMAInformation.Technician" & vbCrLf
strSQL = strSQL & " HAVING (((InvoiceOrderEntry.InvoiceType)=""CR"") " & vbCrLf
strSQL = strSQL & " AND ((InvoiceOrderEntry.ShipDate) Between [Forms]![RepairSummaryReport_preselect]![FromDate] " & vbCrLf
strSQL = strSQL & " AND [Forms]![RepairSummaryReport_preselect]![ToDate]) " & vbCrLf
strSQL = strSQL & " AND ((Products.ProductID)=[Forms]![RepairSummaryReport_preselect]![ProductModelNumber]))" & vbCrLf
strSQL = strSQL & " ORDER BY InvoiceOrderEntry.ShipDate DESC;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
Me.RecordSource = strSQL
results in an error.ASKER
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
TRUSTED BY
ASKER
Russ :)