Query Using Form Troubles

[EVP]

Hi all,

Any ideas on why my form is not working to run a parameter query?
In my query criteria I have:

Between [Forms]![HUSubmitDteFrm]![SubmitAfterDate] And Date()

The unbound text box in the form is named SubmitAfterDate.

However, when I type in a date in the form, nothing happens (even if I have Me.requery in the AfterUpdate control of the unbound txt box).
Any ideas?

 

[doran_doran]

I think you can create a text box on your form called txtTodaysDate then

Between [Forms]![HUSubmitDteFrm]![SubmitAfterDate] And [Forms]![HUSubmitDteFrm]![txtTodaysDate]

===========================================

If it's behind a form meaning behind a button:
"WHERE (table.datefield Between # " & Me!submitafterdate & " # AND # " & Me!txttodaysDate & "# )"
===========================================

If it's on a query

Between [Forms]![formname]![submitafterdate] And [Forms]![formname]![txttodaysDate]

===========================================

Good Luck.
Dianna

 

[EVP]

Thanks for your help!

However, it's still not working for me

I now have two text boxes in my form exactly as you suggested, with this in my query criteria:
Between [Forms]![HUSubmitDteFrm]![SubmitAfterDate] And [Forms]![HUSubmitDteFrm]![txtTodaysDate]

Ideally I'd like to have the user enter only one parameter (submitted after date) and use the =Date() functon as the range.

 

[RayH]

How is the query being called?
Are you clicking on a button?

 

[EVP]

Well, this may be the problem

I tried using the command button, but that pulled up the query in data sheet view - Not what I wanted.

Is it possible to type these values in a txt box in the form and simply refresh the form to run this query (in form view)??

 

[RayH]

I am trying to understand what you need and reading between the lines are you saying that you want to setup a 'filter' to show only those records based on a range of dates?

If you are not saying that then more details please.

 

[EVP]

Correct.
This was an evolution from a parameter query between a start date and present date that would pop-up when opening the form. However, I could never get the form to 'refresh' and allow me to change the criteria (without closing and re-opening the form).
I've since realized all is better when I use the form to run this query, except I can't get it to work!

 

[RayH]

Using a query as the recordsource to your form.
Add a field called txtMyDate
Add a command button called FilterButton

Private Sub FilterButton_Click()

Me.FilterOn = True
Me.Filter = "([SubmitAfterDate]>=" & "#" & txtMyDate & "#)"

End Sub

 

[EVP]

Thank you, Thank you!!

That works, I just have to get the kinks out (after applying filter of two dates it still prompts me to enter the first date??)

I will spend more time with the access books

 

[RayH]

what do you have now?

 

[EVP]

I have two text boxes in my form:
SubmitAfterDate and,
TxtMyDate

A filter command button:

Private Sub FilterButton_Click()

Me.FilterOn = True
Me.Filter = "([SubmitAfterDate]>=" & "#" & txtMyDate & "#)"


End Sub

And in my query the criteria is:
Between [Forms]![HUSubmitDteFrm]![SubmitAfterDate] And [Forms]![HUSubmitDteFrm]![txtMyDate]

When I input dates in the two text boxes and click the command button, I am prompted with a parameter value box for "SubmitAfterDate" (again, this is the 1st box with info. entered). Then the query works, but I'd like to get rid of the extra data entry.

 

[RayH]

You don't need two date fields on the form (only need the field you are using in the filter) and theres no need for the criteria in the query the filter will do that for you.

 

[EVP]

I'm trying to filter between two dates (previous and todays date). Don't I need two values in the filter? Is there a way to code the filter button so that the 2nd date is always todays date?

Also, I got rid of the first text box (SubmitAfterDate) but the parameter value box for "SubmitAfterDate" is still popping up (where do you think the code is for this, it's not coming from the query?).

 

[RayH]

Any chance you could post the db?

 

[EVP]

I think I've come up with a 'quick and dirty' solution. I can't believe I didn't see this before... I'm simply creating a command button that will close the query (bringing the user back to the main menu) and when they reopen the query (by clicking on the command button that opens the form) they will again be prompted for parameter criteria to run the query. Not a developers dream, but it'll work.

I'll hold off on posting the db for now (it has confidential information) Unless you'll lose sleep , then I can delete the content.