date query

  • Thread starter Thread starter Lauren B
  • Start date Start date
L

Lauren B

I am attempting to write a query where the user can chose to view entries
that were created between a "start" date and an "end" date. The only way
that I know to do this is put "Between [Start Date] And [End Date]" in a
query and prompt the user for the two dates.

Is there any other way to do this? I would ideally like to have drop-down
menus on a form--have start date with a Month drop-down, day drop-down, and
year drop-down (I would populate the fields) and do the same for end date.
Is this even a possibly for input?

Thank you in advance for any suggestions.

LB
 
Lauren said:
I am attempting to write a query where the user can chose to view entries
that were created between a "start" date and an "end" date. The only way
that I know to do this is put "Between [Start Date] And [End Date]" in a
query and prompt the user for the two dates.

Is there any other way to do this? I would ideally like to have drop-down
menus on a form--have start date with a Month drop-down, day drop-down, and
year drop-down (I would populate the fields) and do the same for end date.
Is this even a possibly for input?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, it is possible. It would be better if you had the complete date in
one TextBox (let the user type in the date). If you want you can use
calendar controls so the user can select the date w/ a visual aid. Then
use form referencing in the query. E.g.:

PARAMETERS Forms!form_name!control_name1 Date,
Forms!form_name!control_name2 Date;
SELECT ...
FROM ...
WHERE date_column BETWEEN Forms!form_name!control_name1 And
Forms!form_name!control_name2

Substitute the form's name for "form_name" and the controls' names for
"control_name1" and "control_name2."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi9rvoechKqOuFEgEQJfBACfbwfjmXCcR8rof2z6NSzOva0QTFEAoMsM
o+3iRhp5WAdA676l4lQ/TPIE
=haGb
-----END PGP SIGNATURE-----
 
Lauren said:
I am attempting to write a query where the user can chose to view entries
that were created between a "start" date and an "end" date. The only way
that I know to do this is put "Between [Start Date] And [End Date]" in a
query and prompt the user for the two dates.

Is there any other way to do this? I would ideally like to have drop-down
menus on a form--have start date with a Month drop-down, day drop-down, and
year drop-down (I would populate the fields) and do the same for end date.
Is this even a possibly for input?

Thank you in advance for any suggestions.

LB
Click to expand...

You can put up a combobox for year, month and day and populate each of them using 'Value List' for the Row Source Type property of the combobox.

For the day combobox use one column and enter the day numbers in the 'Row Source' property (comma separated list: 1,2,3,4,5,...,31).
For the month combobox use two columns and enter month numbers and names: 1,Jan,2,Feb,3,Mar,4,Apr, ... ,12,Dec
For the year combobox use the same method as for days.

Now, for one (or all) of your comboboxes, you can use the Exit Event to change the Recordsource or Filter property of your form, like the following (day combobox exit):

'Using Recordsource:
Private Sub cboDay_Exit(Cancel As Integer)
Me.RecordSource = "SELECT * FROM table WHERE start_date > #" & cboYear.Value & "/" & cboMonth.Value & "/" & cboDay.Value & "#"
End Sub


'Using Filter:
Private Sub cboDay_Exit(Cancel As Integer)
Me.Filter = "start_date > #" & cboYear.Value & "/" & cboMonth.Value & "/" & cboDay.Value & "#"
Me.FilterOn = True
End Sub


The example only shows date greater than (not between, which is left to yourself to solve).

hth
PerL
 
Back
Top