Access VBA docmd where clause

[rstrout]

Hi all,
I am trying to print a pdf from an access form but only the current record that meets date and facility criteria. I have been working on this, it seems like forever, but can't get it to work properly. I found code on this site but still can't get the syntax correct.
Below is the code I found and my version:

 DoCmd.OpenReport "Rpt Form Responses", acViewReport, , "[Facility Number]=" & temp & "' And [Service Date] Between #" & begindate & "# And #" & enddate & "#"
  DoCmd.OpenReport "FORM x", acViewPreview, , "[VendorID]='" & Me.cboVendorID & "' And [Date]        Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

The top code is my version and the bottom code I found on this site from "pbaldy" . Fortunately, it is almost exactly what I need but can't seem to get it to work properly. I got it to work once but it prints all records instead of just the criteria I am looking for and after changing the syntax it just fails.
Any help would be appreciated.
Thanks,


Reggie

[orange]

I think you need a quote (single) after your =

DoCmd.OpenReport "Rpt Form Responses", acViewReport, , "[Facility Number]='" & temp & "' And [Service Date] Between #" & begindate & "# And #" & enddate & "#"

[rstrout]

I think you need a quote (single) after your =

DoCmd.OpenReport "Rpt Form Responses", acViewReport, , "[Facility Number]='" & temp & "' And [Service Date] Between #" & begindate & "# And #" & enddate & "#"

Thanks Orange.. I REALLY appreciate the response. However, that didn't work. The first part of this code actually works without the (single quote) but as soon as I add the second half with the date requirement I get an error or if it does run I get many different variations of the report. ie wrong dates, multiple reports in one file. [Facility number] is a number field and not a string. I think I read in another post on here that text needs a quote but a numeric value doesn't?? I remember testing that part and that was the only syntax that I could get to work.
I also got this variation from pbaldy and still couldn't get it to work..LOL

DoCmd.OpenReport _
    ReportName:="Rpt Form Responses", _
    View:=acViewPreview, _
    WhereCondition:="[Facility Number]=" & temp & "" _
    & " AND [Service Date] Between #" & begindate & "# AND #" & enddate & "#"

I have been working on this off and on for about a month..I thought I had it working until I imported more data which added multiple entries for one facility and that messed up the reports. Then I had to add the date functionality and that is when all HE?? broke loose.. LOL.. anyway.. any other help is appreciated. Thanks, Reg

[orange]

Ok so temp is a number.
So my quote is in error, so remove it.
But in your code after temp it appears you have a double quote followed by a single quote.
If that is true, remove the single quote.
Try it, post back.

[rstrout]

Ok so temp is a number.
So my quote is in error, so remove it.
But in your code after temp it appears you have a double quote followed by a single quote.
If that is true, remove the single quote.
Try it, post back.

Temp is DIM as a string. Although, [Facility Number] is a number? But that piece of code SEEMS to work?? Maybe that is a problem?
If I run the code with no date criteria it will follow the criteria and match [Facility Number] to temp and it works? Temp is a string that
is just set to = [Facility Number]. If I look at it in VBA it shows the result as "4100" with quotes around it. So, can I assume it is a string?
I took out the single quote and got an error of "incorrect date format".
Thanks

[orange]

Can you post your code again re opening the report?
If FacilityNumber is a number why do you have temp dimmed as string??

Is you database suitable for posting (nothing confidential/personal)?
You could post with just a few data records.

*** You might also include some
Debug.Print temp; begindate; enddate just to see what the values are

[rstrout]

Here is the entire code. I have not used debug.print but I will usually cursor over or msgbox the variables to see what the values are. I really appreciate the time.. This version of the code gives me an "incorrect date format" error. Thanks

Public Function CreateCustomerPDF()
On Error GoTo PDF_Err
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim myfacility As String
   Dim mydate As Date
   Dim MyFileName As String
   Dim mypath As String
   Dim temp As String
   Dim begindate As Date
   Dim enddate As Date
   Dim rangedate As Date
   Dim datetrue As String
   
   mypath = "C:\Customer\PDF Files\"
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("SELECT * FROM [tbl Responses]", dbOpenDynaset)
   
   begindate = InputBox("Enter Inspection begin date!(mm/dd/yyyy)", "Inspection start date")
    
   enddate = InputBox("Enter Inspection end date!(mm/dd/yyyy)", "Inspection end date")
   
   Do While Not rs.EOF
   
    temp = rs("Facility Number")
    myfacility = rs("Facility Number")
    mydate = rs("service date")
    MyFileName = myfacility & " " & "Inspection" & " " & Format(mydate, "mm-dd-yyyy") & ".pdf"
    DoCmd.OpenReport "Rpt Form Responses", acViewReport, , "[Facility Number]='" & temp & "' And [Service Date] Between #" & begindate & "# And #" & enddate & "#"
         DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
             DoCmd.Close acReport, "Rpt Form Responses"
    
     
   rs.MoveNext
    Loop
  
PDF_Exit:
    Exit Function
    
PDF_Err:
    MsgBox "Incorrect Date Format, Please try again", vbCritical, "Incorrect Date"
    Resume PDF_Exit

End Function

[orange]

Try this for the date stuff. It should get begindate and enddate to true Date data types.

begindate = CDate(InputBox("Enter Inspection begin date!(mm/dd/yyyy)", "Inspection start date"))

enddate = CDate(InputBox("Enter Inspection end date!(mm/dd/yyyy)", "Inspection end date"))

I'll keep looking, just give it a try.

Also with the DoCmd.OpenReport inside the do loop you will get multiple reports --is that what you intended?

[rstrout]

I will try the date change.
What I am trying to get is a report for each facility based on a date range. When I leave the date portion of the docmd statement out and I only have one month of data, I get exactly what I want which is one report for each facility, with only the data for that facility. If I run this without the where facility = temp, then I get a report for each facility with every facility in every report. So, I think the facility = temp is doing its job and giving me a pdf that has a report for each facility with only the one facility data in it. The problem arose when I added the new inspections from the next month. Now, say 4100 has a record for Feb and Mar. So, when I run the function I get a good report but it now has both Feb and Mar pages in the pdf. The facility = temp seemed to eliminate that when there were not different date records for a specific facility. Now that I have multiple dates, I am once again getting a pdf that has multiple pages instead of just the one facility with the specific date. I didn't totally understand this at first but I think the facility = temp restricts this to working only with the current record, which means that facility should equal temp. Subsequent records don't match so it puts only that one record in the pdf. I think...LOL Thanks

[orange]

I mocked up your table and report and used this code.
Note:
1) I changed temp from string to integer
2) I substituted an "_" for your space in FileName.
3)I commented out the OutputTo line (line 140) as that is giving an error that crashed my Access -- I don't have the pdf add-in installed. I was getting an error msg on 140 saying there was a format issue...
4) You will save yourself lots of debugging time if you do not use embedded spaces or special characters in field and object names. Use only alph-numeric chars and "_" underscore.

Public Function CreateCustomerPDF()
      'On Error GoTo PDF_Err
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim myfacility As String
          Dim mydate As Date
          Dim MyFileName As String
          Dim mypath As String
          Dim temp As Integer
          Dim begindate As Date
          Dim enddate As Date
          Dim rangedate As Date
          Dim datetrue As String

10        On Error GoTo CreateCustomerPDF_Error

20        mypath = "C:\Customer\PDF Files\"
30        Set db = CurrentDb()
40        Set rs = db.OpenRecordset("SELECT * FROM [tbl Responses]", dbOpenDynaset)

50        begindate = CDate(InputBox("Enter Inspection begin date!(mm/dd/yyyy)", "Inspection start date"))

60        enddate = CDate(InputBox("Enter Inspection end date!(mm/dd/yyyy)", "Inspection end date"))

70        Do While Not rs.EOF

80            temp = rs("Facility Number")
90            myfacility = rs("Facility Number")
100           mydate = rs("service date")
110           MyFileName = myfacility & "_" & "Inspection" & " " & Format(mydate, "mm-dd-yyyy") & ".pdf"
              '
120           Debug.Print "temp " & temp; vbCrLf & "  myDate " & mydate & vbCrLf & "   begindate " & begindate & vbCrLf & "  enddate; " & enddate; ""
130           DoCmd.OpenReport "Rpt Form Responses", acViewReport, , "[Facility Number]= " & temp & "  And [Service Date] Between #" & begindate & "# And #" & enddate & "#"
140          ' DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
150           DoCmd.Close acReport, "Rpt Form Responses"


160           rs.MoveNext
170       Loop

PDF_Exit:
180       Exit Function

          'PDF_Err:
          '   MsgBox "Incorrect Date Format, Please try again", vbCritical, "Incorrect Date"
          '  Resume PDF_Exit

190       On Error GoTo 0
200       Exit Function

CreateCustomerPDF_Error:
210       If Err.number = 3464 Then
220           Debug.Print MyFileName
230       End If


240       MsgBox "Error " & Err.number & "  " & Erl & " (" & Err.Description & ") in procedure CreateCustomerPDF of Module AWF_Related"

End Function

It does iterate the recordset. Here's a sample from my test data

temp 1
  myDate 23/02/2014
   begindate 22/02/2014
  enddate; 01/06/2014
temp 101
  myDate 26/02/2014
   begindate 22/02/2014
  enddate; 01/06/2014
temp 219
  myDate 12/10/2013
   begindate 22/02/2014
  enddate; 01/06/2014
temp 222
  myDate 02/03/2014
   begindate 22/02/2014
  enddate; 01/06/2014
temp 233
  myDate 27/09/2014
   begindate 22/02/2014
  enddate; 01/06/2014
temp 244
  myDate 13/08/2014
   begindate 22/02/2014
  enddate; 01/06/2014

[rstrout]

I mocked up your table and report and used this code.
Note:
1) I changed temp from string to integer
2) I substituted an "_" for your space in FileName.
3)I commented out the OutputTo line (line 140) as that is giving an error that crashed my Access -- I don't have the pdf add-in installed. I was getting an error msg on 140 saying there was a format issue...
4) You will save yourself lots of debugging time if you do not use embedded spaces or special characters in field and object names. Use only alph-numeric chars and "_" underscore.

Public Function CreateCustomerPDF()
      'On Error GoTo PDF_Err
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim myfacility As String
          Dim mydate As Date
          Dim MyFileName As String
          Dim mypath As String
          Dim temp As Integer
          Dim begindate As Date
          Dim enddate As Date
          Dim rangedate As Date
          Dim datetrue As String

10        On Error GoTo CreateCustomerPDF_Error

20        mypath = "C:\Customer\PDF Files\"
30        Set db = CurrentDb()
40        Set rs = db.OpenRecordset("SELECT * FROM [tbl Responses]", dbOpenDynaset)

50        begindate = CDate(InputBox("Enter Inspection begin date!(mm/dd/yyyy)", "Inspection start date"))

60        enddate = CDate(InputBox("Enter Inspection end date!(mm/dd/yyyy)", "Inspection end date"))

70        Do While Not rs.EOF

80            temp = rs("Facility Number")
90            myfacility = rs("Facility Number")
100           mydate = rs("service date")
110           MyFileName = myfacility & "_" & "Inspection" & " " & Format(mydate, "mm-dd-yyyy") & ".pdf"
              '
120           Debug.Print "temp " & temp; vbCrLf & "  myDate " & mydate & vbCrLf & "   begindate " & begindate & vbCrLf & "  enddate; " & enddate; ""
130           DoCmd.OpenReport "Rpt Form Responses", acViewReport, , "[Facility Number]= " & temp & "  And [Service Date] Between #" & begindate & "# And #" & enddate & "#"
140          ' DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
150           DoCmd.Close acReport, "Rpt Form Responses"


160           rs.MoveNext
170       Loop

PDF_Exit:
180       Exit Function

          'PDF_Err:
          '   MsgBox "Incorrect Date Format, Please try again", vbCritical, "Incorrect Date"
          '  Resume PDF_Exit

190       On Error GoTo 0
200       Exit Function

CreateCustomerPDF_Error:
210       If Err.number = 3464 Then
220           Debug.Print MyFileName
230       End If


240       MsgBox "Error " & Err.number & "  " & Erl & " (" & Err.Description & ") in procedure CreateCustomerPDF of Module AWF_Related"

End Function

It does iterate the recordset. Here's a sample from my test data

temp 1
  myDate 23/02/2014
   begindate 22/02/2014
  enddate; 01/06/2014
temp 101
  myDate 26/02/2014
   begindate 22/02/2014
  enddate; 01/06/2014
temp 219
  myDate 12/10/2013
   begindate 22/02/2014
  enddate; 01/06/2014
temp 222
  myDate 02/03/2014
   begindate 22/02/2014
  enddate; 01/06/2014
temp 233
  myDate 27/09/2014
   begindate 22/02/2014
  enddate; 01/06/2014
temp 244
  myDate 13/08/2014
   begindate 22/02/2014
  enddate; 01/06/2014

OMG.. that is so close... (thanks for the info on the field names also, I should know better than to use spaces)
Anyway, almost there. Now it creates a pdf for every record(feb and mar) but it puts the feb data in every file.
So, if I have a facility with two entries(one for feb and one for mar) then I get two pdf files. One dated feb with feb data
and one dated Mar with feb data... LOL.. this stuff is so crazy sometimes..
I also checked out the immediate window after running.. pretty cool...
the variable entries seem to be correct so I am not sure why it is still sending pdf files for all dates?
If I run a date range of 2-1 to 2-11 it should only create 7 records but it is creating one for all 76 records in my table.
Thanks

[orange]

The code is set up to output a report for each record in rs.

What exactly do you want in plain English? Why should it only create 7 records?

Perhaps you should asking for the begin/end dates for each report???

[rstrout]

Sorry, I know this can be confusing.. My comments from before only resulted in 7 records because only 7 were within the date range I entered.
We have a customer with 100 locations. We must visit/inspect each location on a monthly basis. So our database gets 100 new records for each month.
The two most important fields are [Facility Number] and [Service Date]. So, at the end of each month after all the inspections are uploaded into the database
I must create a unique report/pdf (think of the report as a 1 page inspection, not a multipage report for multiple facilities) for each [Facility Number] by [Service Date]. So the pdf would be 1 page based on [Facility Number] and the [Service Date] range that I enter. In most cases the date range is an entire month because I have already created the pdf files for the previous month. So each month I create 100 new pdf files, one for each facility for the month. The reason I needed the where clause was because if I don't specify the date and [facility Number = temp then I get 100 new pdf files(one for each facility) with a page for every date occurrence in the database. So if I had been doing this for 6 months, my February process would create a pdf for each facility but it would have 6 pages(one page for every date in the table). I only need the current month's info to be in the pdf.
I hope that helps. If not, I certainly understand and appreciate the help.

[orange]

As the code stands, and as I interpret it

For each record in your table,
Create and print a report for that facility for service dates within your begin/end date
next record

Is that what you expect??

Do you print reports at a specific time of each month??

[rstrout]

As the code stands, and as I interpret it

For each record in your table,
Create and print a report for that facility for service dates within your begin/end date
next record

Is that what you expect??

Do you print reports at a specific time of each month??

Yes, that is what I am looking for. We usually print the 7th or 8th of the month and only print the previous complete month. Once we have completed and printed a month, technically we should not need to print again as the pdf's are stored on a cloud but I still need to keep the raw data in Access.
Thanks