RE: Need help with Code Please!!!

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.


"Klatuu" wrote:

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


"Lisa" wrote:

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.


"Lisa" wrote:

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 S.
.

Relevant Pages

  • RE: Need help with Code Please!!!
    ... "Lisa" wrote: ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ... Dim myDataSource As String ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... putting the query SQL itself in here maybe it will help you understand what I ... the actual string that gets built at the end of the SQ1 build process. ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... I believe Case is a reserved word. ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)