mvdwal
asked on
Need help with a ms acces query
I tried it all day but it is just to difficult for me to solve it.
I have a search form with unbound fields. The code of this form works fine for the search function. I want the results in my report and save this in batches on my network.
But then to save the report in batches to the network has failed all day
This code generates a pdf and saves it in batches on my network. It stores the data in a tempfolder and saves it on my network.
I have a search form with unbound fields. The code of this form works fine for the search function. I want the results in my report and save this in batches on my network.
Const cInvalidDateError As String = "You have entered an invalid date."
Dim str1Where As String
Dim strError As String
str1Where = "1=1"
str1Where = "1=1"
' If datum
If Not IsNull(Me.FromDate) And (Me.ToDate) Then
'Add the predicate
str1Where = "tblFolderInventory.[Date] > #" & Format(Me.FromDate, "yy-mm-dd")
str1Where = str1Where & "# AND tblFolderInventory.[Date] <#" & Format(Me.ToDate, "yy-mm-dd") & "#"
End If
' If Customer
If Not IsNull(Me.CustomerID) Then
'Add the predicate
str1Where = str1Where & " AND " & "tblFolderInventory.[CustomerID] = " & Me.CustomerID & ""
End If
' If formule
If Not IsNull(Me.StoreFormuleID) Then
'Add the predicate
str1Where = str1Where & " AND " & "tblFolderInventory.[StoreFormuleID] = " & Me.StoreFormuleID & ""
End If
' If formule
If Not IsNull(Me.supplier) Then
'Add the predicate
str1Where = str1Where & " AND " & "tblFolderInventory.[Vermoedelijk bedrijf] = " & Me.supplier & ""
End If
' If ItemID
If Nz(Me.ItemID) <> "" Then
' Add it to the predicate - match on leading characters
str1Where = str1Where & " AND " & "tblFolderInventory.ItemID Like '*" & Me.ItemID & "*'"
End If
If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Products", acFormDS, , strWhere, acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Folder_lijst_sub.Form.Filter = str1Where
Me.Folder_lijst_sub.Form.FilterOn = True
End If
But then to save the report in batches to the network has failed all day
This code generates a pdf and saves it in batches on my network. It stores the data in a tempfolder and saves it on my network.
Private Sub Knop76_Click()
Const lngBatchSize = 30 ' labels to be printed in one go
Dim YValue As String
YValue = Format(Date, "yy")
Dim strPath As String
strPath = "W:\001_Product foto's\020_FOLDERS\" ' path to export to, including trailing backslash.
Dim strSQL As String
Dim strWhere As String
Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim lngSeqNo As Long
Dim i As Long
Dim n As Long
Dim strFile As String
Dim strFilebr As String
Dim LValue As String
LValue = Format(Date, "yymmdd")
Set dbs = CurrentDb
strSQL = "DELETE * FROM tblTempfolder"
dbs.Execute strSQL, dbFailOnError
strSQL = "SELECT * FROM qryAHEBfolder WHERE AanmaakDatum Between Date()-(Weekday(Date())-1)-7 And Date()-Weekday(Date())+7 "
Set rst1 = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
Set rst2 = dbs.OpenRecordset("tblTempfolder", dbOpenDynaset)
Do While Not rst1.EOF
rst2.AddNew
lngSeqNo = lngSeqNo + 1
rst2!SeqNo = lngSeqNo
For i = 0 To rst1.Fields.Count - 1
rst2.Fields(rst1.Fields(i).Name) = rst1.Fields(i)
Next i
rst2.Update
rst1.MoveNext
Loop
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
Set dbs = Nothing
For i = 1 To lngSeqNo Step lngBatchSize
strWhere = "SeqNo Between " & i & " And " & (i + lngBatchSize - 1)
DoCmd.OpenReport ReportName:="newrptInventory", View:=acViewPreview, WhereCondition:=strWhere
n = n + 1 ' Increase number for file name
strFile = LValue & "Export" & n & ".pdf"
DoCmd.OutputTo acOutputReport, , acFormatPDF, strPath & strFile
MsgBox "Press OK to continue.", vbInformation
' ***** New line below: close the report *****
DoCmd.Close acReport, "newrptInventory", acSaveNo
Next i
End Sub
I'm confused.
What does your report display?
Clearly define what you are calling a "Batch"
What does your report display?
Clearly define what you are calling a "Batch"
This isn't your problem (we are waiting for more info) but I noticed a potential problem with how you are formatting a date.
If you live in a part of the world where the standard date format is NOT mm/dd/yyyy, then when passing strings to SQL Server, you MUST format your string dates. The format you are using will be ambiguous. How should 12-05-06 be interpreted? May 6, 2012, Dec 5, 2006, or May 12, 2006? Change your format to include a 4-digit year.
Format(Me.FromDate, "yyyy-mm-dd") - will work because SQL will assume ymd order
But
Format(Me.FromDate, "mm/dd/yyyy") - is technically more correct since that is what SQL Server expects.
If you live in a part of the world where the standard date format is NOT mm/dd/yyyy, then when passing strings to SQL Server, you MUST format your string dates. The format you are using will be ambiguous. How should 12-05-06 be interpreted? May 6, 2012, Dec 5, 2006, or May 12, 2006? Change your format to include a 4-digit year.
Format(Me.FromDate, "yyyy-mm-dd") - will work because SQL will assume ymd order
But
Format(Me.FromDate, "mm/dd/yyyy") - is technically more correct since that is what SQL Server expects.
ASKER
Sorry. The 1st code is from an button on my search form
the 2nd code is the code wich generates a pdf file on my network. Both codes work!
The problem is: I want to generate a report with the unbound search input fields from the 1st code.
I have to change this
but don't know how to change the strSQL with the below code
the 2nd code is the code wich generates a pdf file on my network. Both codes work!
The problem is: I want to generate a report with the unbound search input fields from the 1st code.
I have to change this
strSQL = "SELECT * FROM qryAHEBfolder WHERE AanmaakDatum Between Date()-(Weekday(Date())-1)-7 And Date()-Weekday(Date())+7 "
but don't know how to change the strSQL with the below code
' If datum
If Not IsNull(Me.FromDate) And (Me.ToDate) Then
'Add the predicate
str1Where = "tblFolderInventory.[Date] > #" & Format(Me.FromDate, "yy-mm-dd")
str1Where = str1Where & "# AND tblFolderInventory.[Date] <#" & Format(Me.ToDate, "yy-mm-dd") & "#"
End If
' If Customer
If Not IsNull(Me.CustomerID) Then
'Add the predicate
str1Where = str1Where & " AND " & "tblFolderInventory.[CustomerID] = " & Me.CustomerID & ""
End If
' If formule
If Not IsNull(Me.StoreFormuleID) Then
'Add the predicate
str1Where = str1Where & " AND " & "tblFolderInventory.[StoreFormuleID] = " & Me.StoreFormuleID & ""
End If
' If formule
If Not IsNull(Me.supplier) Then
'Add the predicate
str1Where = str1Where & " AND " & "tblFolderInventory.[Vermoedelijk bedrijf] = " & Me.supplier & ""
End If
' If ItemID
If Nz(Me.ItemID) <> "" Then
' Add it to the predicate - match on leading characters
str1Where = str1Where & " AND " & "tblFolderInventory.ItemID Like '*" & Me.ItemID & "*'"
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It is not working
I think I can leave out
Const lngBatchSize = 30 ' labels to be printed in one go
Dim YValue As String
Dim str1Where As String
Dim strError As String
YValue = Format(Date, "yy")
Dim strPath As String
strPath = "W:\001_Product foto's\020_FOLDERS\" ' path to export to, including trailing backslash.
Dim strSQL As String
Dim strWhere As String
Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim lngSeqNo As Long
Dim i As Long
Dim n As Long
Dim strFile As String
Dim strFilebr As String
Dim LValue As String
LValue = Format(Date, "yymmdd")
Set dbs = CurrentDb
strSQL = "DELETE * FROM tblTempfolder"
dbs.Execute strSQL, dbFailOnError
'strSQL = "SELECT * FROM qryAHEBfolder WHERE AanmaakDatum Between Date()-(Weekday(Date())-1)-7 And Date()-Weekday(Date())+7 "
str1Where = "1=1"
' If datum
If Not IsNull(Me.vanafdatum) And (Me.totdatum) Then
'Add the predicate
str1Where = "tblFolderInventory.[AanmaakDatum] > #" & Format(Me.vanafdatum, "yy-mm-dd")
str1Where = str1Where & "# AND tblFolderInventory.[AanmaakDatum] <#" & Format(Me.totdatum, "yy-mm-dd") & "#"
End If
' If Customer
If Not IsNull(Me.CustomerID) Then
'Add the predicate
str1Where = str1Where & " AND " & "tblFolderInventory.[CustomerID] = " & Me.CustomerID & ""
End If
' If formule
If Not IsNull(Me.WinkelFormuleID) Then
'Add the predicate
str1Where = str1Where & " AND " & "tblFolderInventory.[WinkelFormuleID] = " & Me.WinkelFormuleID & ""
End If
' If formule
If Not IsNull(Me.Leverancier) Then
'Add the predicate
str1Where = str1Where & " AND " & "tblFolderInventory.[Vermoedelijk bedrijf] = " & Me.Leverancier & ""
End If
' If ItemID
If Nz(Me.ItemID) <> "" Then
' Add it to the predicate - match on leading characters
str1Where = str1Where & " AND " & "tblFolderInventory.ItemID Like '*" & Me.ItemID & "*'"
End If
If Len(str1Where) > 0 Then
strSQL = "SELECT * FROM qryAHEBfolder WHERE AanmaakDatum Between Date()-(Weekday(Date())-1)-7 And Date()-Weekday(Date())+7 AND " & str1Where
End If
Set rst1 = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
Set rst2 = dbs.OpenRecordset("tblTempfolder", dbOpenDynaset)
Do While Not rst1.EOF
rst2.AddNew
lngSeqNo = lngSeqNo + 1
rst2!SeqNo = lngSeqNo
For i = 0 To rst1.Fields.Count - 1
rst2.Fields(rst1.Fields(i).Name) = rst1.Fields(i)
Next i
rst2.Update
rst1.MoveNext
Loop
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
Set dbs = Nothing
For i = 1 To lngSeqNo Step lngBatchSize
strWhere = "SeqNo Between " & i & " And " & (i + lngBatchSize - 1)
DoCmd.OpenReport ReportName:="newrptInventory", View:=acViewPreview, WhereCondition:=strWhere
n = n + 1 ' Increase number for file name
strFile = LValue & "Export" & n & ".pdf"
DoCmd.OutputTo acOutputReport, , acFormatPDF, strPath & strFile
MsgBox "Press OK to continue.", vbInformation
' ***** New line below: close the report *****
DoCmd.Close acReport, "newrptInventory", acSaveNo
Next i
End Sub
I think I can leave out
WHERE AanmaakDatum Between Date()-(Weekday(Date())-1)-7 And Date()-Weekday(Date())+7
in If Len(str1Where) > 0 Then
strSQL = "SELECT * FROM qryAHEBfolder WHERE AanmaakDatum Between Date()-(Weekday(Date())-1)-7 And Date()-Weekday(Date())+7 AND " & str1Where
End If
It is not workingThat's pretty much meaningless.
What's not working? Do you get errors? Or does it not return data?
I'm not sure what you can or cannot leave out, so can't really say.
Note also the WHERE argument in the OpenReport method is basically a WHERE clause without the word "WHERE". So if you would normally filter like this:
WHERE FIeld1=2 AND Field2=4
Then your "WHERE" argument in the OpenReport method would be simply:
Field1=2 AND Field2=4
WHERE FIeld1=2 AND Field2=4
Then your "WHERE" argument in the OpenReport method would be simply:
Field1=2 AND Field2=4
Error 3061 is "Too Few Parameters", which often means you've not spelled something correctly. Check the names of your Table, Columns and any Form Fields you're using.
ASKER
Thanks you very much Scott. I'll check them. I will let you know!
You should use this:
Format(Me.ToDate, "yyyy-mm-dd")
"yy-mm-dd" will fail when it can be read as mm-dd-yy.
/gustav
Format(Me.ToDate, "yyyy-mm-dd")
"yy-mm-dd" will fail when it can be read as mm-dd-yy.
/gustav
ASKER
Dear Scott Mc Daniel, It runs! Many thanks!
I had to changed the code from
Dear Gustav, Many thanks for your help! I have changed it.
I had to changed the code from
'strSQL = "SELECT * FROM qryAHEBfolder WHERE AanmaakDatum Between Date()-(Weekday(Date())-1)-7 And Date()-Weekday(Date())+7 AND " & str1Where
To
strSQL = "SELECT * FROM qryAHEBfolder WHERE AanmaakDatum AND " & str1Where
Is that the right way?Dear Gustav, Many thanks for your help! I have changed it.
ASKER
Many Thanks!
WHERE AanmaakDatum AND " & str1WhereThe "WHERE AanmaakDatum" is meaningless, since you're not "filtering" on AanmaakDatum. I'd think it should be:
strSQL = "SELECT * FROM qryAHEBfolder WHERE " & str1Where
This assumes the strlWhere variable is properly configured, of course
ASKER
Great! Thanks a lot!
One thing to trips a lot of people up is the Path where you save, especially when you're building one up like that. IT's often a good idea to place a breakpoint in the code just before the OutputTo line and verify that strPath and strFile contain the expected values, and that the directory you're using actually exists, and you can get to it through Windows Explorer.