Report Sort/Filter 1

[Drake12]

I made a couple of new reports and need to know how to print out date ranges? I need to pull out one week of data at a time and print it out. My other report is just a daily report of all the data entered that day. Where would select just todays date and print that data out?

Thanks

 

[dhookom]

I would create a form with text boxes for the beginning and ending dates. Then, in your report's record source query, add a criteria against the date field like:
Between Forms!frmYours!txtStart And Forms!fmrYours!txtEnd

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Drake12]

So I would need to create two fields in the table called something like startdate and enddate to store the data that will be input through the form created with two text boxes? Not really clear on where record source query is found. I see where I can build an expression, but not sure if that is correct or not.

 

[obli]

Maybe this will help. Here is a link to a resource I used for a similar project a year ago and it worked like a charm.

http://members.iinet.net.au/~allenbrowne/casu-08.html

 

[ineedyourhelp]

Obli,
I have a similar question about date ranges and used this link as you suggested. I followed perfectly, but when I enter the dates and hit OK, I get the following...

Run Time Error 3075
Syntax error (missing operator) in query expression '(date/time completed Between #06/04/2004# And #07/04/2004#)'.

When I hit Debug, the following line of code is highlighted....

DoCmd.OpenReport strReport, acViewPreview, , strWhere

I entered the dates as followed..
6/04/2004
7/04/2004

Not sure what the problem is..

Thanks

 

[Drake12]

Thanks Obli, that does work for me. I just need the dates in order, but I am sure that has to do with the way my primary key and index's are setup. I just started with Access so I need to read more about those issues now. Thank you dhookom as well for your reply. Doing that proceedure helped me understand more what you are talking about.

 

[ineedyourhelp]

Obli,
Scratch my problem. I had my strField named incorrectly. I would now like to know how to have the form close immediately after I hit OK. Right now, it stays open on top of the report.

Thanks

 

[dhookom]

Set the visible property of the form to False.
Me.Visible = False

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Drake12]

Hello again, another quick question that has come about. I have a field that is called "Block". The data is A1,A2,B1,B2,C1,C2 etc...on down the line. I really like the code earlier mentioned that enabled us to put in date ranges and was trying to figure out a way to do the same type of search here with my Block field. What I will need to do is print out a report from each Block. So I would like to ask the user for the Block ie..A1 and have the report print out all records that are in Block..A1.

Thanks

 

[dhookom]

How are you implementing the date range or which earlier code did you like? You can create a combo box of all the different blocks and then use this in either the saved query or in a Where string in DoCmd.OpenReport.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Drake12]

Well using

http://members.iinet.net.au/~allenbrowne/casu-08.html

is how I was using the date ranges. I would like to do the same with block. Just ask the user what block they want a report on.

Thanks

 

[dhookom]

Allen has two methods on his web page. I would use the form method and add a combo box to select a block.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Drake12]

Ok, I understand the combo box methed but how do I go about telling the report to use the data? Use the Where string like you suggested earlier?

 

[dhookom]

Add something like this (after the date code) using Allen's code
If Not IsNull(Me.cboBlock) Then 'block combo box
strWhere = " AND [Block] = """ & Me.cboBlock & """"
End If


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Drake12]

Ok, so keep the same form just add this data to the already existing code? I need to report everything in Ax,Bx,Cx etc individually. It works, but it gives me all the blocks in the table instead of all the A's, B's, C's etc individually.

 

[dhookom]

Can you please provide your control names as well as your code. It is near impossible to tell what you have done when we are here and you are there.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Drake12]

Well first of all this block form does not need to rely on date ranges it depends on block only. The control names or different blocks will be A1,A2,A3,A4,A5,A6, B1-B6, etc on through to the letter P. So there will be quite a few to choose from. So instead of dates to be entered I would like the user to choose from a list of all the blocks. So when a user chooses A1 a report generates all the blocks corresponding to block A1. Here is the code I have now, but it is just what you listed earlier with the date code taken out.

Private Sub Command0_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.

strReport = "Block Summary Report"
strField = "BLOCK"



' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Me.Visible = False
If Not IsNull(Me.cboBlock) Then 'block combo box
strWhere = " AND [BLOCK] = """ & Me.cboBlock & """"
End If
End Sub

I hope this helps .. thank you again for you help.

 

[dhookom]

You must set the strWhere value prior to using it in the OpenReport line. Also, if there are no other

Private Sub Command0_Click()
Dim strReport As String     'Name of report to open.
    Dim strField As String      'Name of your date field.
    Dim strWhere As String      'Where condition for OpenReport.
    
    strReport = "Block Summary Report"
    strField = "BLOCK"
    strWhere = "1=1 "
    If Not IsNull(Me.cboBlock) Then   'block combo box
        strWhere = strWhere & " AND [BLOCK] = """ & Me.cboBlock & """"
    End If  

    ' Debug.Print strWhere                   'For debugging purposes only.
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
    Me.Visible = False

End Sub

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Drake12]

dhookom, if you could tell me that once more .. I can only see "You must set the strWhere value prior to using it in the OpenReport line. Also, if there are no other" that much.

Thanks

 

[dhookom]

Ignore the "also...". I had noticed there was no other criteria such as date range in you where clause. I added code to cover this situation.

Did you try the code as provided?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.