Start Date and End Date in one prompt window

[data808]

Right now I have this in the query criteria row:



>=[Enter Start Date:] And <=[Enter End Date:]

This makes two prompts pop up one at a time. I was wondering if there was a way to combine both start and end date boxes in one prompt so I can see what dates I decide to type side by side. Is this possible?

Thanks.

[June7]

No.

Use a form for input of the parameters.

[data808]

Hey June7,

long time no see. That would be ideal but from what I remember it was difficult to do. Would you have a template or the code to do this for 2 unbound text boxes?

[June7]

Not that difficult.

Build form with unbound textboxes and a button. Code behind button Click event to open query object.

Build query object that references the textboxes:

SELECT * FROM tablename WHERE [datefield] BETWEEN Nz(Forms!formname!tbxStart, #1/1/1900#) AND Nz(Forms!formname!tbxEnd, #12/31/2900#);

I don't use dynamic parameters in queries. I also would not open query object. I would open a form or report.

DoCmd.OpenReport "report name", , , "[datefield] BETWEEN #" & Nz(Me.tbxStart, "1/1/1900") & "# AND #" & Nz(Me.tbxEnd,"12/31/2900") & "#"

[data808]

Ok June7,

I messed with your suggestion above for a while today and was not able to get it to work. I put the SELECT * FROM tablename.......etc... into the query criteria and it gave back some syntax errors which I did not understand so I tried messing with it a bit and got it to accept it. I think I changed it to something like this

SELECT * (FROM [LICENSE] WHERE [Date Issued:] BETWEEN Nz(Forms![Permit Date Issued]![txtStart], #1/1/1900#) AND Nz(Forms![Permit Date Issued]![txtEnd], #12/31/2900#))

Might have been slightly different but it was something to that effect. I just kept trying to move things or add things until it stopped giving me syntax errors or errors that said it needed brackets or parenthesis to close the expression or something.

Anyway, after I finished this part I couldn't even open my split form anymore with the 2 unbound text boxes with the button that you told me to make. It said something about it can't reference 2 text boxes so to change the expression to refer to only 1 text box or something like that. Then it would have the stop all macros window. So could you explain what is going on here? And direct what to do next?

Thanks.

[hapm]

What June7 gave you was the full query the filter criteria part of [Date Issued:] would be only: BETWEEN Nz(Forms![Permit Date Issued]!txtStart, #1/1/1900#) AND Nz(Forms![Permit Date Issued]!txtEnd, #12/31/2900#)

[June7]

As hapm described, macro just needs the filter criteria part of the SQL. Are you using SearchForRecord macro? Since the macro is behind the form, might be able to simplify without the form name:

Where Condition= ="[Date Issued] BETWEEN #" & Nz([txtStart], "1/1/1900") & "# AND #" & Nz([txtEnd], "12/31/2900") & "#"

I don't use macros. In VBA:

Me.Filter = "[Date Issued] BETWEEN #" & Nz(Me.[txtStart], "1/1/1900") & "# AND #" & Nz(Me.[txtEnd], "12/31/2900") & "#"
Me.FilterOn = True

[data808]

I would prefer to use VBA too so this is what I did.

I put this code behind the filter button:
Private Sub cmdFilter_Click()

Me.Filter = "[Date Issued] BETWEEN #" & Nz(Me.[txtStart], "1/1/1900") & "# AND #" & Nz(Me.[txtEnd], "12/31/2900") & "#"
Me.FilterOn = True

End Sub

Then I entered the start and end dates into the 2 unbound texts boxes I made. Then I clicked the filter button and this prompt comes up that says:
Enter Parameter Value ? for the title of the small window and then it has Date Issued with a field box for me to type in with an ok and cancel button.

So what do I do next?

[pbaldy]

The parameter prompt probably means something is misspelled. Based on your earlier post, should there be a colon at the end?

[Date Issued:]

[data808]

Thank you very much. I will try that.

[data808]

Worked like a charm. Thanks so much guys. I been trying to get this done for a long time.