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.

 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

Open in new window


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

Open in new window

mvdwalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
save the report in batches to the network has failed all day
Can you be more specific? For example, do you get any error messages? If so, please post them. From what I can see your code should work, assuming it "fits" with your datasource (and there's no way for us to tell that unless you upload the database).

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.
Jeffrey CoachmanMIS LiasonCommented:
I'm confused.
What does your report display?
Clearly define what you are calling a "Batch"
PatHartmanCommented:
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.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

mvdwalAuthor Commented:
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
strSQL = "SELECT * FROM qryAHEBfolder  WHERE AanmaakDatum Between Date()-(Weekday(Date())-1)-7 And Date()-Weekday(Date())+7 "

Open in new window


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

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do you mean you want to ADD the criteria that you're creating in the first code block to the strSQL variable you're using in the SECOND block? If so, then do this:

         ' 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 Len(strlWhere)>0 Then
            strSQL = "SELECT * FROM qryAHEBfolder  WHERE AanmaakDatum Between Date()-(Weekday(Date())-1)-7 And Date()-Weekday(Date())+7 AND " & strlWhere
      End If


This assumes that the code is running on the SAME FORM as the first code block. If not, you'll have to change the "Me" references to point to that form.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mvdwalAuthor Commented:
It is not working

 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

Open in new window


I think I can leave out
WHERE AanmaakDatum Between Date()-(Weekday(Date())-1)-7 And Date()-Weekday(Date())+7

Open in new window

 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

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It is not working
That'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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
mvdwalAuthor Commented:
3061 mistake ?

Knipsel.JPG
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
mvdwalAuthor Commented:
Thanks you very much Scott. I'll check them. I will let you know!
Gustav BrockCIOCommented:
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
mvdwalAuthor Commented:
Dear Scott Mc Daniel, It runs! Many thanks!

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

Open in new window

To
 strSQL = "SELECT * FROM qryAHEBfolder  WHERE AanmaakDatum AND " & str1Where

Open in new window

Is that the right way?

Dear Gustav, Many thanks for your help! I have changed it.
mvdwalAuthor Commented:
Many Thanks!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
 WHERE AanmaakDatum AND " & str1Where
The "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
mvdwalAuthor Commented:
Great! Thanks a lot!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.