Link to home
Start Free TrialLog in
Avatar of mvdwal
mvdwalFlag for Netherlands

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.

 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

Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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.
I'm confused.
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.
Avatar of mvdwal

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

ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mvdwal

ASKER

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

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.
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
Avatar of mvdwal

ASKER

3061 mistake ?

User generated image
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.
Avatar of mvdwal

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
Avatar of mvdwal

ASKER

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.
Avatar of mvdwal

ASKER

Many Thanks!
 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
Avatar of mvdwal

ASKER

Great! Thanks a lot!