dates and combo box (1 Viewer)

[asp_learner]

I am trying to do a query that would time periods from a combo box but I can't get this to work.

I have a table for quarters. With Quarter 1 set between #1/1/01# and #3/31/01#. I want the user to be able to chose 1st quarter from a combo box and for the report to query during those dates.

When using a form for my combo box, I can't get my report to query for 1st quarter from my report.

While writing this, I thought I could write an IIF statement but it would be quite long.

Thanks,
Eva

 

[dakcg]

Two questions:

Do you have a field in the table that signifies the quarter the field is in? And are you calling to that field to pick from in your combo box? And are you using the combo box in the criteria of your query? I guess that is 3 questions! I am just trying to narrow down where the problem is.

DAK

 

[asp_learner]

Yes to all three questions.

When I run the report, it will open up a form on which the combo box is used.

combo box has a field for Quarter and the second has a field for Between [the first Date] and [The second Date]. The query in the report runs off the field in the combo box.

Thanks,
Eva

 

[Pat Hartman]

There is a simpler solution. Have the user enter the quarter he wants to filter on - 1,2,3, or 4 and use the DatePart() function in the query.

Select ...
From ....
Where DatePart("q",YourDate) = Forms!YourForm!YourControl;

 

[asp_learner]

Hi Pat,

Your suggestion won't work for me because I am using various time fiscal time periods.

I basically want to have different periods in a table for them to chose from.

Thanks,
Eva

 

[David R]

Do you have a lookup table with entries like this for your form to reference?
1 2/15/2001 5/14/2001
2 5/15/2001 8/14/2001
3 8/15/2001 11/14/2001
4 11/15/2001 2/14/2002

If so, you should be able to create a button on your form to use this value as the selection criteria for your report.

Where Category = Forms!YourFormName!YourComboboxName;

Thanks to Pat Hartman for the original solution to this in http://www.access-programmers.co.uk/ubb/Forum3/HTML/000351.html

HTH
David R

 

[asp_learner]

Hi,

Thank you for those who responded but I am still having a problem. basically there are three values in my combo box that I want to be able to use. The first one is Quarter, the second is start date and the third is end date.

I can't figure out how to bound to all three values.

For example, in my combo box, user chooses first Quarter. I want to be able to use the second column (start date) and the third column (end date) so that I can query between start date and end date.

Thanks,
Eva

 

[simongallop]

My turn for questions!!

I presume that you have a form where the user selects a period to view and then clicks a button. What do they see? Report? Query? etc.

If it is a report, then you can put code into the OnClick event of the button that takes the data from the combo box and places the dates into a SQL string which in turn is used in a query for the report to output the information. If you want some code then will happily supply

HTH

 

[asp_learner]

Harry,

If you could give me the code I would be thrilled. I am using the form in a report which is based on a query. The query will be using values from the combo box. Between forms!whateverName!combobox [StartDate] and forms!whateverName!combobox [EndDate].

For example, if I choose 1st Quarter from my combo box, I need the value for StartDate and the value for EndDate.

The table that my combo box looks like this
Quarter in the first column, StartDate in the second column and EndDate in the 3rd column.
Thanks,
Eva

 

[simongallop]

You will need to do the following.

In your query, where the date field is that you will be limiting, in the criteria section type in Between #01/01/01# And # 03/01/01#

Having done that, view the query in SQL view. This is the string that you will need in the code. Copy it and paste it into the code where you see MyStr = "...

CODE:
Dim MyStr as string
Dim MyQD as QueryDef
Dim StDate as Date
Dim EndDate as date
Set MyQD = CurrentDB.QueryDefs("QueryName")

StDate = me.cmbBox.column(1)
EndDate = me.cmbBox.column(2)
MyStr = "SELECT .....FROM.....WHERE...Between #" & StDate & "# And #" & EndDate & "#....;"
MyQD.SQL = MyStr


The query should now show the correct results. This code can either be used in the OnClick event of a button or even the OnChange event of your combobox. You can then have DoCmd.OpenReport "ReportName" or whatever you want.

HTH

 

[asp_learner]

Thank you Harry and everyone who answered.

I finally got it to work in an individual report. I also got it to work in subreports by putting code in the on open event for each subreport

Thanks again and have a wonderful day
Eva


[This message has been edited by asp_learner (edited 12-18-2001).]

[This message has been edited by asp_learner (edited 12-18-2001).]