filtering report by entered name

[eww]

I've got a time sheet report that I'm creating that as of right now displays all the time sheet related info for all employees. I want to have a filter on so that only the employee you want shows up on the report.

The problem I'm running into is that I want the ability to type in an employees name and the filter runs. Right now I know how to stick a filter on in an OpenReport macro, and understand that the Where Condition is where I need to enter the employee info, but I want to make this pop up to be entered when the report is opened. Any suggestions? Thanks.

[eww]

Right now I'm stuck trying to get this code to work. I've got a form for my time sheet entry called frmTimeSheet. In it there is a combo box with all the employees names available called "cboEmployeeName". The report I have (named "rptSAPTimesheet") shows the names entered, job numbers, hours, etc.

Going back to the form I have a button for right now that ideally you would click on it and it would filter the report by the Employee's name that is currently listed on the form. The code on the button I have reads:

Private Sub Command47_Click()
DoCmd.OpenReport "rptSAPTimesheet", acViewPreview, , "EmployeeName = " & cboEmployeeName
End Sub

However everytime I click the button it gives me an error:

Run-time error '3075':

Syntax error (missing operator) in query expression 'EmployeeName = (the names are actually showing up here)'.

I'm going off of this link http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

If anyone has any suggestions I more than welcome them.

[pbaldy]

This should help:

BaldyWeb wherecondition

[eww]

Sir, you have been a great help, that is exactly what I needed. Thank you very much.

I have one more question if you don't mind, I see that this would be used to filter based on the date that was on the form I was jumping from:

DoCmd.OpenForm "SecondFormName", , , "FieldName = #" & Me.ControlName & "#"

but what if I wanted to enter a range of dates, say between whatever dates needed to encompass this week, what would I need to change to be able to enter those dates upon clicking the button to go to the report.
Thanks again for all your help.

[pbaldy]

Along the lines of:

DoCmd.OpenForm "SecondFormName", , , "FieldName Between #" & Me.ControlName & "# And #" & Me.OtherControlName & "#"

[eww]

So that works if I have two control names for, say, the first date of the week, and the last date of the week, for the week that I want to capture the employees time sheet data.

Do you have any recommendations on what to do if I don't really want to store the dates the employees choose to filter for their week start and week end? What comes to mind right now are the parameter dialog boxes that come up when something isn't defined, but I don't know how to get that right now (of course it only wants to come up when I'm doing something wrong). I'm not even sure if this makes sense as the right way to go about it, but hopefully it makes sense.

Also what would be the best way to go about combining the two filtering methods? What I want is a particular Employee's logged time, between the dates of such and such.

Thank you for your help.

[pbaldy]

Most of us use forms to gather user input; you have a lot more control over the process. They don't have to be bound to a table (ie stored). In any case, the way to get a prompt on purpose is to substitute bracketed text for the form references:

[Enter start date]

[eww]

This makes sense to me. However I just tried this:

DoCmd.OpenReport "rptSAPTimesheet", acViewReport, , "Work Week Between = #" & [txtEnterStartDate] & "# And #" & [txtEnterEndDate] & "#"

and I'm getting a syntax error that says "...(missing operator) in query expression 'Work Week Between = ## And ##'

[pbaldy]

I never actually tried using that technique in a wherecondition. It's normally used directly in query criteria. In code you'll probably need to use InputBox():

DoCmd.OpenReport "rptSAPTimesheet", acViewReport, , "Work Week Between = #" & InputBox(...) & "# And #" & InputBox(...) & "#"

More info on the syntax for InputBox() in VBA help. I'd consider using textboxes on the form though. You can test what the user enters before trying to open the report. I suppose you could drop the InputBox() results into variables and test those, but the form is easier, and the user can see what they've entered before clicking your button.

[weekend00]

DoCmd.OpenReport "rptSAPTimesheet", acViewReport, , "Work Week Between = #" & InputBox(...) & "# And #" & InputBox(...) & "#"

remove the "=", and bracket around "Work Week":

DoCmd.OpenReport "rptSAPTimesheet", acViewReport, , "[Work Week] Between #" & InputBox(...) & "# And #" & InputBox(...) & "#"

[pbaldy]

Duh! I didn't notice either one of those.

[eww]

Forgive me but I'm not too familiar with the InputBox yet. Here's what I managed to put together but of course it's not working:

DoCmd.OpenReport "rptSAPTimesheet", acViewReport, , "[Work Week] Between #" & InputBox("Enter Start Date", "Week Begin", , , , , ) & "# And #" & InputBox("Enter End Date", "Week End", , , , , ) & "#"

[eww]

I just figured how to get what I need. It's a partial mix between what pbaldy recommended and something I found elsewhere on filtering.

On the Timesheet form where the information is entered I have a button to go to the timesheet report. That button is what I wanted to have all the filtering contained on. What I settled for right now is a filter by employee name (related to the one that is being entered on the form) which is the filter that pbaldy originally recommended.

Then when the report is opened I have a button that filters between two dates so that the employees can enter the dates themselves and get a report for the work week they want. For this I just used a simple ApplyFilter macro.

Thanks for the help guys, I'm marking this as solved finally.