Need help with Code Please!!!

[Lisa]

This code works great in one of my databases and I am tring to change it up a
bit to work in another database. I am have trouble, can anyone help here is a
copy of the code.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean


'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1

Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates" &
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub

 

[Klatuu]

It would be helpful if you gave more information that "I am have trouble".
Describe what is not working correctly, what results you are getting, and
what you would like to get. If you are getting an error, show which line
gets the error and what the error is.

This code is incorrect:

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

The problem is that you are trying to move before you know if you have any
records in the recordset. If there are none, you will get an error on the
MoveLast.

It is not just to update the record counter. It causes the entire recordset
to be retrived. Here is how it should be:

If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'Populate the recordset and reposition to the beginning
rs.MoveLast
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If


I do see one problem you should correct.
--
Dave Hargis, Microsoft Access MVP

This code works great in one of my databases and I am tring to change it up a
bit to work in another database. I am have trouble, can anyone help here is a
copy of the code.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean


'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1

Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates" &
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub

 

[Lisa]

When the code is run nothing happens and I get an error message . It
highlights this in the code as not working.

Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)
--
Lisa S.

This code works great in one of my databases and I am tring to change it up a
bit to work in another database. I am have trouble, can anyone help here is a
copy of the code.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean


'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1

Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates" &
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub

 

[Klatuu]

I can't see the error message from here. What does it say?
Does it have a number, If so, what is the number?
--
Dave Hargis, Microsoft Access MVP

When the code is run nothing happens and I get an error message . It
highlights this in the code as not working.

Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)
--
Lisa S.

This code works great in one of my databases and I am tring to change it up a
bit to work in another database. I am have trouble, can anyone help here is a
copy of the code.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean


'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1

Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates" &
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub

 

[Klatuu]

I think this may be the problem:

myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"

You need to put a space in front of the FROM so it would be & " FROM It is
create a string like this:

Missing space here --v
SELECT DISTINCTROW tblVendors.Company, tblItemListVendor.USFSItemNoFROM
tblItemListVendor INNER JOIN tblVendors ON tblItemListVendor.Company =
tblVendors.ID

Let me know if that does it.
--
Dave Hargis, Microsoft Access MVP

When the code is run nothing happens and I get an error message . It
highlights this in the code as not working.

Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)
--
Lisa S.

This code works great in one of my databases and I am tring to change it up a
bit to work in another database. I am have trouble, can anyone help here is a
copy of the code.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean


'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1

Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates" &
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub

 

[Lisa]

That worked for that but now it is say Error 3075 syntax error missing
operation tnlInvoiceHistAll.USF Product Number. It highlights sQ1 = sQ1
--
Lisa S.

I think this may be the problem:

myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"

You need to put a space in front of the FROM so it would be & " FROM It is
create a string like this:

Missing space here --v
SELECT DISTINCTROW tblVendors.Company, tblItemListVendor.USFSItemNoFROM
tblItemListVendor INNER JOIN tblVendors ON tblItemListVendor.Company =
tblVendors.ID

Let me know if that does it.
--
Dave Hargis, Microsoft Access MVP

When the code is run nothing happens and I get an error message . It
highlights this in the code as not working.

Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)
--
Lisa S.

This code works great in one of my databases and I am tring to change it up a
bit to work in another database. I am have trouble, can anyone help here is a
copy of the code.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean


'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1

Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates" &
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub

 

[Klatuu]

Which of the lines that contain SQ1 = SQ1 ?
--
Dave Hargis, Microsoft Access MVP

That worked for that but now it is say Error 3075 syntax error missing
operation tnlInvoiceHistAll.USF Product Number. It highlights sQ1 = sQ1
--
Lisa S.

I think this may be the problem:

myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"

You need to put a space in front of the FROM so it would be & " FROM It is
create a string like this:

Missing space here --v
SELECT DISTINCTROW tblVendors.Company, tblItemListVendor.USFSItemNoFROM
tblItemListVendor INNER JOIN tblVendors ON tblItemListVendor.Company =
tblVendors.ID

Let me know if that does it.
--
Dave Hargis, Microsoft Access MVP

When the code is run nothing happens and I get an error message . It
highlights this in the code as not working.

Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)
--
Lisa S.


:

This code works great in one of my databases and I am tring to change it up a
bit to work in another database. I am have trouble, can anyone help here is a
copy of the code.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean


'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1

Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates" &
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub

 

[Steve Sanford]

PMFJI,


Because you used spaces in the field names, you need to have brackets around
"USF Product Number":

sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF Product
Number, "

should be

sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.[USF Product
Number], "



HTH

 

[Steve Sanford]

Brackets are also needed in the last line of the WHERE clause for the same
field.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

PMFJI,


Because you used spaces in the field names, you need to have brackets around
"USF Product Number":

sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF Product
Number, "

should be

sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.[USF Product
Number], "



HTH

 

[Dale Fye]

I would also recommend that you replace your DISTINCTROW with DISTINCT.

DISTINCTROW looks at all of the fields in the joined table, so it takes
longer than DISTINCT, which only looks at the fields in the query.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

That worked for that but now it is say Error 3075 syntax error missing
operation tnlInvoiceHistAll.USF Product Number. It highlights sQ1 = sQ1
--
Lisa S.

I think this may be the problem:

myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"

You need to put a space in front of the FROM so it would be & " FROM It is
create a string like this:

Missing space here --v
SELECT DISTINCTROW tblVendors.Company, tblItemListVendor.USFSItemNoFROM
tblItemListVendor INNER JOIN tblVendors ON tblItemListVendor.Company =
tblVendors.ID

Let me know if that does it.
--
Dave Hargis, Microsoft Access MVP

When the code is run nothing happens and I get an error message . It
highlights this in the code as not working.

Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)
--
Lisa S.


:

This code works great in one of my databases and I am tring to change it up a
bit to work in another database. I am have trouble, can anyone help here is a
copy of the code.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean


'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1

Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & "FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates" &
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub

 

[Stuart McCall]

I would also recommend that you replace your DISTINCTROW with DISTINCT.

DISTINCTROW looks at all of the fields in the joined table, so it takes
longer than DISTINCT, which only looks at the fields in the query.

<SNIP>

But DISTINCT returns unique values, unlike DISTINCTROW.

 

[Dale Fye]

I was going to say that given that her first query that defines her recordset
only selects Company and USFSItemNo, that I think DISTINCT is what she
probably needed.

Then I copied the SQ1 code and pasted it into Word, and found that she never
references either of those recordsets fields in the query that she is
building inside the loop, so I don't know what she is doing. The only reason
I can think of for building the other query inside of the loop is to
reference the values in the recordset that is being looped through.

Additionally:
1. She has numerous lines where she needs to insert a space before the
"AND" or "WHERE" so that the spacing will be correct.

2. She has referenced the controls for txtBeginningDate and txtEndingDate
inside of the quotes in here WHERE clause, so that won't work. Should
probably look like:

SQ1 = SQ1 & " WHERE tblInvoiceHistAll.[Ship Date] BETWEEN #" _
& Forms![frmReportRebateParameters]!txtBeginningDate & "# AND #"
& Forms![frmReportRebateParameters]! & "# "

3. She also refers to a value 'e-mail' in her WHERE clause that is not a
declared variable, so it probably needs to be wrapped in quotes, like:

SQ1 = SQ1 & " AND tblItemListVendor.BillBackDelivMethod = 'e-mail'))"

Lisa, I hope some of these comments will help.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Lisa]

I made a typo yeaterday. The error highlights
qd.SQL = sQ1
I don't really know if that makes a difference. I have tried and corrected
some of you comments and am still have the same error message.
--
Lisa S.

I was going to say that given that her first query that defines her recordset
only selects Company and USFSItemNo, that I think DISTINCT is what she
probably needed.

Then I copied the SQ1 code and pasted it into Word, and found that she never
references either of those recordsets fields in the query that she is
building inside the loop, so I don't know what she is doing. The only reason
I can think of for building the other query inside of the loop is to
reference the values in the recordset that is being looped through.

Additionally:
1. She has numerous lines where she needs to insert a space before the
"AND" or "WHERE" so that the spacing will be correct.

2. She has referenced the controls for txtBeginningDate and txtEndingDate
inside of the quotes in here WHERE clause, so that won't work. Should
probably look like:

SQ1 = SQ1 & " WHERE tblInvoiceHistAll.[Ship Date] BETWEEN #" _
& Forms![frmReportRebateParameters]!txtBeginningDate & "# AND #"
& Forms![frmReportRebateParameters]! & "# "

3. She also refers to a value 'e-mail' in her WHERE clause that is not a
declared variable, so it probably needs to be wrapped in quotes, like:

SQ1 = SQ1 & " AND tblItemListVendor.BillBackDelivMethod = 'e-mail'))"

Lisa, I hope some of these comments will help.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

<SNIP>

But DISTINCT returns unique values, unlike DISTINCTROW.

 

[Dale Fye]

Lisa,

Can you copy your current code and paste it into the thread again?

Also, post the error number, the text of the error message, the line that is
highlighted when the error occurs.

Lastly, post the text of the SQL string after the complete build. You can
do this by adding a Debug.Print SQ1 line immediately after the last line that
builds SQ1.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I made a typo yeaterday. The error highlights
qd.SQL = sQ1
I don't really know if that makes a difference. I have tried and corrected
some of you comments and am still have the same error message.
--
Lisa S.

I was going to say that given that her first query that defines her recordset
only selects Company and USFSItemNo, that I think DISTINCT is what she
probably needed.

Then I copied the SQ1 code and pasted it into Word, and found that she never
references either of those recordsets fields in the query that she is
building inside the loop, so I don't know what she is doing. The only reason
I can think of for building the other query inside of the loop is to
reference the values in the recordset that is being looped through.

Additionally:
1. She has numerous lines where she needs to insert a space before the
"AND" or "WHERE" so that the spacing will be correct.

2. She has referenced the controls for txtBeginningDate and txtEndingDate
inside of the quotes in here WHERE clause, so that won't work. Should
probably look like:

SQ1 = SQ1 & " WHERE tblInvoiceHistAll.[Ship Date] BETWEEN #" _
& Forms![frmReportRebateParameters]!txtBeginningDate & "# AND #"
& Forms![frmReportRebateParameters]! & "# "

3. She also refers to a value 'e-mail' in her WHERE clause that is not a
declared variable, so it probably needs to be wrapped in quotes, like:

SQ1 = SQ1 & " AND tblItemListVendor.BillBackDelivMethod = 'e-mail'))"

Lisa, I hope some of these comments will help.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I would also recommend that you replace your DISTINCTROW with DISTINCT.

DISTINCTROW looks at all of the fields in the joined table, so it takes
longer than DISTINCT, which only looks at the fields in the query.
<SNIP>

But DISTINCT returns unique values, unlike DISTINCTROW.

 

[Lisa]

Here is the module I have created
The error is Run time error 3075
The message says Syntax error (missing operation) in query expression
'tblInvoiceHistAll.USF Procuct Number'
When I debug it highlights the qd.SQL = sQ1

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

I hope this helps you help me.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean
'### v 7.0

'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1
Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & " FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'Populate the recordset and reposition to the beginning
rs.MoveLast
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates" &
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub




--
Lisa S.

Lisa,

Can you copy your current code and paste it into the thread again?

Also, post the error number, the text of the error message, the line that is
highlighted when the error occurs.

Lastly, post the text of the SQL string after the complete build. You can
do this by adding a Debug.Print SQ1 line immediately after the last line that
builds SQ1.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I made a typo yeaterday. The error highlights
qd.SQL = sQ1
I don't really know if that makes a difference. I have tried and corrected
some of you comments and am still have the same error message.
--
Lisa S.

I was going to say that given that her first query that defines her recordset
only selects Company and USFSItemNo, that I think DISTINCT is what she
probably needed.

Then I copied the SQ1 code and pasted it into Word, and found that she never
references either of those recordsets fields in the query that she is
building inside the loop, so I don't know what she is doing. The only reason
I can think of for building the other query inside of the loop is to
reference the values in the recordset that is being looped through.

Additionally:
1. She has numerous lines where she needs to insert a space before the
"AND" or "WHERE" so that the spacing will be correct.

2. She has referenced the controls for txtBeginningDate and txtEndingDate
inside of the quotes in here WHERE clause, so that won't work. Should
probably look like:

SQ1 = SQ1 & " WHERE tblInvoiceHistAll.[Ship Date] BETWEEN #" _
& Forms![frmReportRebateParameters]!txtBeginningDate & "# AND #"
& Forms![frmReportRebateParameters]! & "# "

3. She also refers to a value 'e-mail' in her WHERE clause that is not a
declared variable, so it probably needs to be wrapped in quotes, like:

SQ1 = SQ1 & " AND tblItemListVendor.BillBackDelivMethod = 'e-mail'))"

Lisa, I hope some of these comments will help.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I would also recommend that you replace your DISTINCTROW with DISTINCT.

DISTINCTROW looks at all of the fields in the joined table, so it takes
longer than DISTINCT, which only looks at the fields in the query.
<SNIP>

But DISTINCT returns unique values, unlike DISTINCTROW.

 

[Dale Fye]

The fix for this was mentioned early on, I think by Dave.

Whenever you put spaces in your field names (I never do this, I always
separate words with an underscore), you must wrap the field name in [ ]'s
every time you use the field name. You appear to have done that for most of
your fields, but not for this one [USF Product Number], so go through your
code and do that for every field that contains a space.

I can tell by a cursory glance that there is still a major problem with the
WHERE clause. These lines, which currently look like:

sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

Should look something like:

sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" _
& [Forms]![frmReportRebateParameters]![txtBeginningDate] _
& "# And #" _
& [Forms]![frmReportRebateParameters]![txtEndingDate]) _
& "# AND tblItemListVendor.BillBackDelivMethood='e-mail'"

As I posted in my previous post, the references to the controls on the form
for beginning and ending date have to be outside the quotes, and need to be
wrapped in #'s. Also, I assume that the field [BillBackDelivMethod] is a
text field, and that it contains some records where the value in that field
is 'e-mail'. If that is correct, then the code above should work.

I'm not sure what the last line in your original code is meant to do, but it
is definately not part of the WHERE clause, unless it is supposed to look
like:

sQ1 = sQ1 _
& " AND tblVendors.Company = '" & rs("Company") & "'" _
& " AND tblInvoiceHistALL.[USF Product Number] = " &
rs("USFSItemNo")

Addition of this last part to the SQL would make sense, as there are no
other references in your SQL string to the Company and USFSItemNo values that
show up in your original query.

Make these changes, and try it again. We'll get there eventually. If it is
still not working, post back with the modified code, and if it gets that far,
the actual string that gets built at the end of the SQ1 build process.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Here is the module I have created
The error is Run time error 3075
The message says Syntax error (missing operation) in query expression
'tblInvoiceHistAll.USF Procuct Number'
When I debug it highlights the qd.SQL = sQ1

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

I hope this helps you help me.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean
'### v 7.0

'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1
Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & " FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset, dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'Populate the recordset and reposition to the beginning
rs.MoveLast
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND ((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates" &
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub




--
Lisa S.

Lisa,

Can you copy your current code and paste it into the thread again?

Also, post the error number, the text of the error message, the line that is
highlighted when the error occurs.

Lastly, post the text of the SQL string after the complete build. You can
do this by adding a Debug.Print SQ1 line immediately after the last line that
builds SQ1.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I made a typo yeaterday. The error highlights
qd.SQL = sQ1
I don't really know if that makes a difference. I have tried and corrected
some of you comments and am still have the same error message.
--
Lisa S.


:

I was going to say that given that her first query that defines her recordset
only selects Company and USFSItemNo, that I think DISTINCT is what she
probably needed.

Then I copied the SQ1 code and pasted it into Word, and found that she never
references either of those recordsets fields in the query that she is
building inside the loop, so I don't know what she is doing. The only reason
I can think of for building the other query inside of the loop is to
reference the values in the recordset that is being looped through.

Additionally:
1. She has numerous lines where she needs to insert a space before the
"AND" or "WHERE" so that the spacing will be correct.

2. She has referenced the controls for txtBeginningDate and txtEndingDate
inside of the quotes in here WHERE clause, so that won't work. Should
probably look like:

SQ1 = SQ1 & " WHERE tblInvoiceHistAll.[Ship Date] BETWEEN #" _
& Forms![frmReportRebateParameters]!txtBeginningDate & "# AND #"
& Forms![frmReportRebateParameters]! & "# "

3. She also refers to a value 'e-mail' in her WHERE clause that is not a
declared variable, so it probably needs to be wrapped in quotes, like:

SQ1 = SQ1 & " AND tblItemListVendor.BillBackDelivMethod = 'e-mail'))"

Lisa, I hope some of these comments will help.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I would also recommend that you replace your DISTINCTROW with DISTINCT.

DISTINCTROW looks at all of the fields in the joined table, so it takes
longer than DISTINCT, which only looks at the fields in the query.
<SNIP>

But DISTINCT returns unique values, unlike DISTINCTROW.

 

[Dale Fye]

Just noticed a mistake in my replacement WHERE clause, forgot to delete one
of the ) at the end. I've incorporated that part about the company and
product number in this correction.

sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" _
& [Forms]![frmReportRebateParameters]![txtBeginningDate] _
& "# And #" _
& [Forms]![frmReportRebateParameters]![txtEndingDate] _
& "# AND tblItemListVendor.BillBackDelivMethood='e-mail' " _
& " AND tblVendors.Company = '" & rs("Company") & "'" _
& " AND tblInvoiceHistALL.[USF Product Number] = " _
& rs("USFSItemNo")

This assumes that [Company] is a text field and that [USF Product Number] is
not.

HTH
Dale

The fix for this was mentioned early on, I think by Dave.

Whenever you put spaces in your field names (I never do this, I always
separate words with an underscore), you must wrap the field name in [ ]'s
every time you use the field name. You appear to have done that for most
of
your fields, but not for this one [USF Product Number], so go through your
code and do that for every field that contains a space.

I can tell by a cursory glance that there is still a major problem with
the
WHERE clause. These lines, which currently look like:

sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND
((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product
Number"

Should look something like:

sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" _
& [Forms]![frmReportRebateParameters]![txtBeginningDate] _
& "# And #" _
& [Forms]![frmReportRebateParameters]![txtEndingDate]) _
& "# AND tblItemListVendor.BillBackDelivMethood='e-mail'"

As I posted in my previous post, the references to the controls on the
form
for beginning and ending date have to be outside the quotes, and need to
be
wrapped in #'s. Also, I assume that the field [BillBackDelivMethod] is a
text field, and that it contains some records where the value in that
field
is 'e-mail'. If that is correct, then the code above should work.

I'm not sure what the last line in your original code is meant to do, but
it
is definately not part of the WHERE clause, unless it is supposed to look
like:

sQ1 = sQ1 _
& " AND tblVendors.Company = '" & rs("Company") & "'" _
& " AND tblInvoiceHistALL.[USF Product Number] = " &
rs("USFSItemNo")

Addition of this last part to the SQL would make sense, as there are no
other references in your SQL string to the Company and USFSItemNo values
that
show up in your original query.

Make these changes, and try it again. We'll get there eventually. If it
is
still not working, post back with the modified code, and if it gets that
far,
the actual string that gets built at the end of the SQ1 build process.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Here is the module I have created
The error is Run time error 3075
The message says Syntax error (missing operation) in query expression
'tblInvoiceHistAll.USF Procuct Number'
When I debug it highlights the qd.SQL = sQ1

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

I hope this helps you help me.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean
'### v 7.0

'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1
Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & " FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset,
dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'Populate the recordset and reposition to the beginning
rs.MoveLast
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location
as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS
Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND
((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product
Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide
the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded
the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the
export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates"
&
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub




--
Lisa S.

Lisa,

Can you copy your current code and paste it into the thread again?

Also, post the error number, the text of the error message, the line
that is
highlighted when the error occurs.

Lastly, post the text of the SQL string after the complete build. You
can
do this by adding a Debug.Print SQ1 line immediately after the last
line that
builds SQ1.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I made a typo yeaterday. The error highlights
qd.SQL = sQ1
I don't really know if that makes a difference. I have tried and
corrected
some of you comments and am still have the same error message.
--
Lisa S.


:

I was going to say that given that her first query that defines her
recordset
only selects Company and USFSItemNo, that I think DISTINCT is what
she
probably needed.

Then I copied the SQ1 code and pasted it into Word, and found that
she never
references either of those recordsets fields in the query that she
is
building inside the loop, so I don't know what she is doing. The
only reason
I can think of for building the other query inside of the loop is
to
reference the values in the recordset that is being looped through.

Additionally:
1. She has numerous lines where she needs to insert a space before
the
"AND" or "WHERE" so that the spacing will be correct.

2. She has referenced the controls for txtBeginningDate and
txtEndingDate
inside of the quotes in here WHERE clause, so that won't work.
Should
probably look like:

SQ1 = SQ1 & " WHERE tblInvoiceHistAll.[Ship Date] BETWEEN #" _
& Forms![frmReportRebateParameters]!txtBeginningDate & "#
AND #"
& Forms![frmReportRebateParameters]! & "# "

3. She also refers to a value 'e-mail' in her WHERE clause that is
not a
declared variable, so it probably needs to be wrapped in quotes,
like:

SQ1 = SQ1 & " AND tblItemListVendor.BillBackDelivMethod =
'e-mail'))"

Lisa, I hope some of these comments will help.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I would also recommend that you replace your DISTINCTROW with
DISTINCT.

DISTINCTROW looks at all of the fields in the joined table, so
it takes
longer than DISTINCT, which only looks at the fields in the
query.
<SNIP>

But DISTINCT returns unique values, unlike DISTINCTROW.

 

[Lisa]

I made the changes except for the spacing, That was done before I can here
and those fields are in so many places I cannot take out the spaces. I am
putting the query SQL itself in here maybe it will help you understand what I
am trying to run, because i am still getting the same error the 3045

SELECT tblVendors.Company, tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District,
tblInvoiceHistALL.[Customer Name], tblInvoiceHistALL.[Customer Number],
tblInvoiceHistALL.[Address Line 1], tblInvoiceHistALL.City,
tblInvoiceHistALL.State, tblInvoiceHistALL.Zip, tblInvoiceHistALL.[Class
Description], tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],
tblInvoiceHistALL.[USF Product Number], tblInvoiceHistALL.[Product
Description], tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],
tblInvoiceHistALL.[Eaches Ordered], tblInvoiceHistALL.[Cases Shipped],
tblInvoiceHistALL.[Eaches Shipped], tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]="case",(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,
IIf([RebatePdPer]="case",(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood
FROM ((tblItemListLedo INNER JOIN (tblVendors INNER JOIN tblItemListVendor
ON tblVendors.ID = tblItemListVendor.Company) ON tblItemListLedo.LIN =
tblItemListVendor.VListLedoItemDescription) INNER JOIN tblInvoiceHistALL ON
tblItemListVendor.USFSItemNo = tblInvoiceHistALL.[USF Product Number]) INNER
JOIN tblRebateExpanded ON (tblInvoiceHistALL.[Ship Date] =
tblRebateExpanded.RebateDate) AND (tblInvoiceHistALL.[USF Product Number] =
tblRebateExpanded.USFSItemNo)
WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] And
[Forms]![frmReportRebateParameters]![txtEndingDate]) AND
((tblItemListVendor.BillBackDelivMethood)="e-mail"));

--
Lisa S.

Just noticed a mistake in my replacement WHERE clause, forgot to delete one
of the ) at the end. I've incorporated that part about the company and
product number in this correction.

sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" _
& [Forms]![frmReportRebateParameters]![txtBeginningDate] _
& "# And #" _
& [Forms]![frmReportRebateParameters]![txtEndingDate] _
& "# AND tblItemListVendor.BillBackDelivMethood='e-mail' " _
& " AND tblVendors.Company = '" & rs("Company") & "'" _
& " AND tblInvoiceHistALL.[USF Product Number] = " _
& rs("USFSItemNo")

This assumes that [Company] is a text field and that [USF Product Number] is
not.

HTH
Dale

The fix for this was mentioned early on, I think by Dave.

Whenever you put spaces in your field names (I never do this, I always
separate words with an underscore), you must wrap the field name in [ ]'s
every time you use the field name. You appear to have done that for most
of
your fields, but not for this one [USF Product Number], so go through your
code and do that for every field that contains a space.

I can tell by a cursory glance that there is still a major problem with
the
WHERE clause. These lines, which currently look like:

sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND
((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product
Number"

Should look something like:

sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" _
& [Forms]![frmReportRebateParameters]![txtBeginningDate] _
& "# And #" _
& [Forms]![frmReportRebateParameters]![txtEndingDate]) _
& "# AND tblItemListVendor.BillBackDelivMethood='e-mail'"

As I posted in my previous post, the references to the controls on the
form
for beginning and ending date have to be outside the quotes, and need to
be
wrapped in #'s. Also, I assume that the field [BillBackDelivMethod] is a
text field, and that it contains some records where the value in that
field
is 'e-mail'. If that is correct, then the code above should work.

I'm not sure what the last line in your original code is meant to do, but
it
is definately not part of the WHERE clause, unless it is supposed to look
like:

sQ1 = sQ1 _
& " AND tblVendors.Company = '" & rs("Company") & "'" _
& " AND tblInvoiceHistALL.[USF Product Number] = " &
rs("USFSItemNo")

Addition of this last part to the SQL would make sense, as there are no
other references in your SQL string to the Company and USFSItemNo values
that
show up in your original query.

Make these changes, and try it again. We'll get there eventually. If it
is
still not working, post back with the modified code, and if it gets that
far,
the actual string that gets built at the end of the SQ1 build process.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Here is the module I have created
The error is Run time error 3075
The message says Syntax error (missing operation) in query expression
'tblInvoiceHistAll.USF Procuct Number'
When I debug it highlights the qd.SQL = sQ1

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

I hope this helps you help me.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean
'### v 7.0

'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1
Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & " FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset,
dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'Populate the recordset and reposition to the beginning
rs.MoveLast
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location
as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS
Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND
((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product
Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide
the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded
the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the
export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates"
&
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub




--
Lisa S.


:

Lisa,

 

[Lisa]

I have rewritten the code and now am getting an error 3141 the statements has
reserved word spelling error or punctuation error


sQ1 = "SELECT tblVendors.Company, tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number],"
sQ1 = sQ1 & "tblInvoiceHistALL.District, tblInvoiceHistALL.[Customer
Name], tblInvoiceHistALL.[Customer Number],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Address Line 1], tblInvoiceHistALL.City,
tblInvoiceHistALL.State,"
sQ1 = sQ1 & "tblInvoiceHistALL.Zip, tblInvoiceHistALL.[Class
Description], tblInvoiceHistALL.[Order Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Ship Date], tblInvoiceHistALL.[USF
Product Number], tblInvoiceHistALL.[Product Description],"
sQ1 = sQ1 & "tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],
tblInvoiceHistALL.[Eaches Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Cases Shipped],
tblInvoiceHistALL.[Eaches Shipped], tblRebateExpanded.RebatePdPer,"
sQ1 = sQ1 & "tblRebateExpanded.RebateAmount,
IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 &
"IIf([RebatePdPer]=case,(([CasesShipped])+([EachesShipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood,"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER JOIN
tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company)"
sQ1 = sQ1 & "ON tblItemListLedo.LIN =
tblItemListVendor.VListLedoItemDescription) INNER JOIN tblInvoiceHistALL ON"
sQ1 = sQ1 & "tblItemListVendor.USFSItemNo = tblInvoiceHistALL.[USF
Product Number]) INNER JOIN tblRebateExpanded ON"
sQ1 = sQ1 & "(tblInvoiceHistALL.[Ship Date] =
tblRebateExpanded.RebateDate)"
sQ1 = sQ1 & "AND (tblInvoiceHistALL.[USF Product Number] =
tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" &
[Forms]![frmReportRebateParameters]![txtBeginningDate] & "# And #" &
[Forms]![frmReportRebateParameters]![txtEndingDate] & "# AND
tblItemListVendor.BillBackDelivMethood='e-mail' " & " AND tblVendors.Company
= '" & rs("Company") & "'" & " AND tblInvoiceHistALL.[USF Product Number] = "
& rs("USFSItemNo")
--
Lisa S.

Just noticed a mistake in my replacement WHERE clause, forgot to delete one
of the ) at the end. I've incorporated that part about the company and
product number in this correction.

sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" _
& [Forms]![frmReportRebateParameters]![txtBeginningDate] _
& "# And #" _
& [Forms]![frmReportRebateParameters]![txtEndingDate] _
& "# AND tblItemListVendor.BillBackDelivMethood='e-mail' " _
& " AND tblVendors.Company = '" & rs("Company") & "'" _
& " AND tblInvoiceHistALL.[USF Product Number] = " _
& rs("USFSItemNo")

This assumes that [Company] is a text field and that [USF Product Number] is
not.

HTH
Dale

The fix for this was mentioned early on, I think by Dave.

Whenever you put spaces in your field names (I never do this, I always
separate words with an underscore), you must wrap the field name in [ ]'s
every time you use the field name. You appear to have done that for most
of
your fields, but not for this one [USF Product Number], so go through your
code and do that for every field that contains a space.

I can tell by a cursory glance that there is still a major problem with
the
WHERE clause. These lines, which currently look like:

sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND
((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product
Number"

Should look something like:

sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" _
& [Forms]![frmReportRebateParameters]![txtBeginningDate] _
& "# And #" _
& [Forms]![frmReportRebateParameters]![txtEndingDate]) _
& "# AND tblItemListVendor.BillBackDelivMethood='e-mail'"

As I posted in my previous post, the references to the controls on the
form
for beginning and ending date have to be outside the quotes, and need to
be
wrapped in #'s. Also, I assume that the field [BillBackDelivMethod] is a
text field, and that it contains some records where the value in that
field
is 'e-mail'. If that is correct, then the code above should work.

I'm not sure what the last line in your original code is meant to do, but
it
is definately not part of the WHERE clause, unless it is supposed to look
like:

sQ1 = sQ1 _
& " AND tblVendors.Company = '" & rs("Company") & "'" _
& " AND tblInvoiceHistALL.[USF Product Number] = " &
rs("USFSItemNo")

Addition of this last part to the SQL would make sense, as there are no
other references in your SQL string to the Company and USFSItemNo values
that
show up in your original query.

Make these changes, and try it again. We'll get there eventually. If it
is
still not working, post back with the modified code, and if it gets that
far,
the actual string that gets built at the end of the SQ1 build process.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Here is the module I have created
The error is Run time error 3075
The message says Syntax error (missing operation) in query expression
'tblInvoiceHistAll.USF Procuct Number'
When I debug it highlights the qd.SQL = sQ1

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

I hope this helps you help me.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean
'### v 7.0

'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1
Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & " FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset,
dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'Populate the recordset and reposition to the beginning
rs.MoveLast
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location
as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS
Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND
((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product
Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide
the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded
the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the
export
requires the
'#### form to be open and have focus.

'DoCmd.OpenReport myReport, acViewNormal
myFileName = Replace(Replace(myPath & myCompany & "_" & "Rebates"
&
"_" & ".rtf", "/", ""), "'", "")
DoCmd.OutputTo acReport, myReport, "RichTextFormat(*.rtf)",
myFileName, False, ""
'DoCmd.Close acReport, myReport

'move to the next row of qryCompany\USFNumber data
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
MakeReports = True

End Function

Sub check()

Const Qname1 As String = "qryRebateE-Mail"


Dim qd As QueryDef, qdSource As QueryDef

Set qd = CurrentDb.QueryDefs(Qname1)
Set qdSource = CurrentDb.QueryDefs("original_" & Qname1)
qd.SQL = qdSource.SQL
Set qd = Nothing
Set qdSource = Nothing



End Sub




--
Lisa S.


:

Lisa,

 

[Dale Fye]

Lisa,

You posted two items, and I'm not sure how they relate. The first item you
posted in response to my last post was some SQL, but this is not the SQL that
should show up after the code changes I recommended, and still discusses the
3045 error.

The second post, the one I am responding to, appears to have the code
changes, and is now giving you a new error 3141 (do us a favor and add the
"exact" text of the error message when you post here) which implies that a
reserved word, spelling error, or punction error has occurred.

Insert a line immediately below the last line of SQ1 code:

Debug.print SQ1

try to run the code, then copy the text from the debug window and paste it
back in a reply, so I can see exactly what Access/Jet is trying to run.


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have rewritten the code and now am getting an error 3141 the statements has
reserved word spelling error or punctuation error


sQ1 = "SELECT tblVendors.Company, tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number],"
sQ1 = sQ1 & "tblInvoiceHistALL.District, tblInvoiceHistALL.[Customer
Name], tblInvoiceHistALL.[Customer Number],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Address Line 1], tblInvoiceHistALL.City,
tblInvoiceHistALL.State,"
sQ1 = sQ1 & "tblInvoiceHistALL.Zip, tblInvoiceHistALL.[Class
Description], tblInvoiceHistALL.[Order Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Ship Date], tblInvoiceHistALL.[USF
Product Number], tblInvoiceHistALL.[Product Description],"
sQ1 = sQ1 & "tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],
tblInvoiceHistALL.[Eaches Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Cases Shipped],
tblInvoiceHistALL.[Eaches Shipped], tblRebateExpanded.RebatePdPer,"
sQ1 = sQ1 & "tblRebateExpanded.RebateAmount,
IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS Rebate,"
sQ1 = sQ1 &
"IIf([RebatePdPer]=case,(([CasesShipped])+([EachesShipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood,"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER JOIN
tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company)"
sQ1 = sQ1 & "ON tblItemListLedo.LIN =
tblItemListVendor.VListLedoItemDescription) INNER JOIN tblInvoiceHistALL ON"
sQ1 = sQ1 & "tblItemListVendor.USFSItemNo = tblInvoiceHistALL.[USF
Product Number]) INNER JOIN tblRebateExpanded ON"
sQ1 = sQ1 & "(tblInvoiceHistALL.[Ship Date] =
tblRebateExpanded.RebateDate)"
sQ1 = sQ1 & "AND (tblInvoiceHistALL.[USF Product Number] =
tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" &
[Forms]![frmReportRebateParameters]![txtBeginningDate] & "# And #" &
[Forms]![frmReportRebateParameters]![txtEndingDate] & "# AND
tblItemListVendor.BillBackDelivMethood='e-mail' " & " AND tblVendors.Company
= '" & rs("Company") & "'" & " AND tblInvoiceHistALL.[USF Product Number] = "
& rs("USFSItemNo")
--
Lisa S.

Just noticed a mistake in my replacement WHERE clause, forgot to delete one
of the ) at the end. I've incorporated that part about the company and
product number in this correction.

sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" _
& [Forms]![frmReportRebateParameters]![txtBeginningDate] _
& "# And #" _
& [Forms]![frmReportRebateParameters]![txtEndingDate] _
& "# AND tblItemListVendor.BillBackDelivMethood='e-mail' " _
& " AND tblVendors.Company = '" & rs("Company") & "'" _
& " AND tblInvoiceHistALL.[USF Product Number] = " _
& rs("USFSItemNo")

This assumes that [Company] is a text field and that [USF Product Number] is
not.

HTH
Dale

The fix for this was mentioned early on, I think by Dave.

Whenever you put spaces in your field names (I never do this, I always
separate words with an underscore), you must wrap the field name in [ ]'s
every time you use the field name. You appear to have done that for most
of
your fields, but not for this one [USF Product Number], so go through your
code and do that for every field that contains a space.

I can tell by a cursory glance that there is still a major problem with
the
WHERE clause. These lines, which currently look like:

sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND
((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product
Number"

Should look something like:

sQ1 = sQ1 & "WHERE tblInvoiceHistALL.[Ship Date] Between #" _
& [Forms]![frmReportRebateParameters]![txtBeginningDate] _
& "# And #" _
& [Forms]![frmReportRebateParameters]![txtEndingDate]) _
& "# AND tblItemListVendor.BillBackDelivMethood='e-mail'"

As I posted in my previous post, the references to the controls on the
form
for beginning and ending date have to be outside the quotes, and need to
be
wrapped in #'s. Also, I assume that the field [BillBackDelivMethod] is a
text field, and that it contains some records where the value in that
field
is 'e-mail'. If that is correct, then the code above should work.

I'm not sure what the last line in your original code is meant to do, but
it
is definately not part of the WHERE clause, unless it is supposed to look
like:

sQ1 = sQ1 _
& " AND tblVendors.Company = '" & rs("Company") & "'" _
& " AND tblInvoiceHistALL.[USF Product Number] = " &
rs("USFSItemNo")

Addition of this last part to the SQL would make sense, as there are no
other references in your SQL string to the Company and USFSItemNo values
that
show up in your original query.

Make these changes, and try it again. We'll get there eventually. If it
is
still not working, post back with the modified code, and if it gets that
far,
the actual string that gets built at the end of the SQ1 build process.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Here is the module I have created
The error is Run time error 3075
The message says Syntax error (missing operation) in query expression
'tblInvoiceHistAll.USF Procuct Number'
When I debug it highlights the qd.SQL = sQ1

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

I hope this helps you help me.

Option Compare Database
Option Explicit


Public Function MakeReports() As Boolean
'### v 7.0

'Const myPath As String = "C:\tmpWork\"
Const myPath As String = "S:\Product Info\REBATES\Rebates 2008\"
Const Qname1 As String = "qryRebateE-Mail"


'#### Should be correct - pasted this in from your email
Const myReport As String = "rptRebateE-Mail"

Dim rs As Recordset 'object ref to qryCompany\USFNumber
Dim qd As QueryDef 'object ref to query item
Dim myDataSource As String

Dim sQ1 As String 'query SQL text 1
Dim myFileName As String 'the string var we target with the
'concatenated full path and file name
'of the export file

Dim myCompany As String 'current row Company name



myDataSource = "SELECT DISTINCTROW tblVendors.Company,
tblItemListVendor.USFSItemNo"
myDataSource = myDataSource & " FROM tblItemListVendor INNER JOIN
tblVendors ON tblItemListVendor.Company = tblVendors.ID"




Set rs = CurrentDb.OpenRecordset(myDataSource, dbOpenDynaset,
dbReadOnly)

'movelast moves to the last record in the recordset
'as a side effect this updates the RecordCount property
'which is why we are doing it
rs.MoveLast

'move back to row one, the first record before we start working
rs.MoveFirst

If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'Populate the recordset and reposition to the beginning
rs.MoveLast
rs.MoveFirst

'make sure we have at least one row / store to work with
If rs.RecordCount = 0 Then
MsgBox "The qryCompany\USFNumber query returned no records?",
vbCritical, "NOTHING TO REPORT"
MakeReports = False
Exit Function
End If

'build the batch export timestamp string


'keep doing this section until qryCompany\USFNumber runs out of data
Do While Not rs.EOF

'collect the store name of the current row
myCompany = rs.Fields("Company")

'#### QUERY ONE ####
'make the altered query SQL strings with the current row location
as
criteria
sQ1 = "SELECT DISTINCT tblVendors.Company,tblInvoiceHistALL.USF
Product Number, "
sQ1 = sQ1 & "tblItemListLedo.LedoItemDescription,
tblInvoiceHistALL.[Invoice Number], tblInvoiceHistALL.District, "
sQ1 = sQ1 & "tblInvoiceHistALL.[Customer Name],
tblInvoiceHistALL.[Customer Number], tblInvoiceHistALL.[Address Line 1] "
sQ1 = sQ1 & "tblInvoiceHistALL.City, tblInvoiceHistALL.State,
tblInvoiceHistALL.Zip,"
sQ1 = sQ1 & "tblInvoiceHistALL.[Class Description],
tblInvoiceHistALL.[Order Date], tblInvoiceHistALL.[Ship Date],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Product Description],
tblInvoiceHistALL.Brand, tblInvoiceHistALL.[Cases Ordered],"
sQ1 = sQ1 & "tblInvoiceHistALL.[Eaches Ordered],
tblInvoiceHistALL.[Cases Shipped], tblInvoiceHistALL.[Eaches Shipped],"
sQ1 = sQ1 & "tblRebateExpanded.RebatePdPer,
tblRebateExpanded.RebateAmount, IIf([RebatePdPer]=case,(([Cases
Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount]))*[RebateAmount],(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))*[RebateAmount]) AS
Rebate,"
sQ1 = sQ1 & "IIf([RebatePdPer]=case,(([Cases Shipped])+([Eaches
Shipped]/[tblItemListVendor]![CaseEachCount])),(([Cases
Shipped]*[tblItemListVendor]![RebateCaseWgtLBS])+([Eaches
Shipped]*[tblItemListVendor]![RebateEachWgtLBS]))) AS [Total CS/LBS],
tblItemListVendor.BillBackDelivMethood"
sQ1 = sQ1 & "FROM ((tblItemListLedo INNER JOIN (tblVendors INNER
JOIN tblItemListVendor ON tblVendors.ID = tblItemListVendor.Company) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription),"
sQ1 = sQ1 & "INNER JOIN tblInvoiceHistALL "
sQ1 = sQ1 & " ON tblItemListVendor.USFSItemNo =
tblInvoiceHistALL.[USF Product Number]) "
sQ1 = sQ1 & " INNER JOIN tblRebateExpanded ON
(tblInvoiceHistALL.[Ship Date] = tblRebateExpanded.RebateDate) AND
(tblInvoiceHistALL.[USF Product Number] = tblRebateExpanded.USFSItemNo)"
sQ1 = sQ1 & "WHERE (((tblInvoiceHistALL.[Ship Date]) Between
[Forms]![frmReportRebateParameters]![txtBeginningDate] "
sQ1 = sQ1 & " And
[Forms]![frmReportRebateParameters]![txtEndingDate]) "
sQ1 = sQ1 & "AND
((tblItemListVendor.BillBackDelivMethood)=e-mail))"
sQ1 = sQ1 & "tblVendors.Company,tblInvoiceHistALL.USF Product
Number"

'


'#### Now we get to work using our new strings ####
'We rewrite the underlying SQL of the four queries that provide
the
report recordsets
'with our newly constructed and slightly altered query strings -
1,2,3,4

Set qd = CurrentDb.QueryDefs(Qname1)
qd.SQL = sQ1

qd.Close
Set qd = Nothing

'#### NOTE!!! I don't know if we have to open the report when we
export?
'#### I'm guessing that we don't have to, since we've hard-coded
the
query changes.
'#### You can easily uncomment the docmd report open and closing
statements below
'#### if you want to see the reports as they go by, or if the