date query by passing the value of the month

[Fred1981]

I have a date field that and the format of the Field is in

05-Dec-10

I want to write the query that will give all the records between 01-May-10 and 31-May-10 I can do that in normal query

I have I wan to do it via a print button

What I mean is have a List with the Months ( May, Dec, Mar....) and when you select the month the value gets passed to the Prin button and the report of that month willbe printed out

 

[WillShakespeare]

Just had some great help, so attempting to give a little back to the community.

I am not sure if your typing is a bit off or you may speak English as 2nd language, but your request is a little unclear.

I am thinking you have a list of months as buttons? That someone clicks and you want to run a query to send to Print?

What about:

SELECT chosen,fields FROM table WHERE Month(table.date) = Month(control value)

or...

SELECT chosen,fields FROM table WHERE Month(table.date) = "control value" [asuming the buttons are labelled for the Month names]

Or maybe DLookUp?

hth,

Will

 

[Fred1981]

the list should have the values

Mar
Dec
.... etc

you slect the value (Dec) then the value get passed to print Preview Button
the report will display all the records of ( dec)

however how do you extract Dec from 01-Dec-10
because the date filed stores the value 01-Dec-10
and how do you pass the Dec into the query to display all the records of december

 

[randy700]

Private Sub cmdPrint_Click()
    Dim strsql As String
    strsql = "SELECT * FROM YourTable "
    strsql = strsql & "WHERE DateField >= #01/" & Me.cboMonth & "/" & Year(Now()) & "# "
    strsql = strsql & "AND DateField < #01/" & IIf(Me.cboMonth = 12, 1, Me.cboMonth + 1) & "/"
    strsql = strsql & IIf(Me.cboMonth = 12, Year(Now()) + 1, Year(Now())) & "#"
    Debug.Print strsql
End Sub

Randy

 

[dhookom]

You should have provided:
1) the name of the date field in the report's record source
2) the name of the month "list" on your form
3) the code to open the report

The print button code might look like:

    Dim strWhere as String
    strWhere = "Format([YourDateFieldName],'mmm') = '" & _
        Me.lboMonth & "'"
    DoCmd.OpenReport "rptYourReportName", acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[WillShakespeare]

Well, someone may well come up with a better idea, but I just tried to following to test passing the avlue between controls.

1. I created a small table called "Month_Names" which has 2 columns:
Month_ID (Byte) (prime key)
Month_Nme (Text - 3 chars)

2. In ID I listed 1-12

3. In Name: an, Feb, Mar, etc.

4. I ceated a list on a form (drop-down, but I am sure any would do) and pointed it to the table, hiding the D column.

5. I named the DD-List cmbMonths

6. I created a textbox with the following:
=MonthName([cmbMonths],True)

On the form, when I select the drop down, the value is passed to the text box as wanted.

Now, I then added a button to run a query and added the following query:

SELECT Dates.Dates
FROM Dates
WHERE Month(Dates.Dates)=[Forms]![Date_Test]![cmbChooseDate];

Notes that Dates is a Table and Dates is a column inthe table containing various dates. I know the naming is not great, but I was just doing a test to see fi I could do this.

I select Jul from the DD-List, then click the button and a table pops up with 3 reults which are all July dates from my Dates table which has 5 different months with various days in them.

Again, there may be some easier way to do this, but if you want to get working at least you can experiment... of course having done this and typed up my response, someone else has probably already given you a solution in the meantime!

hth,

Will

 

[WillShakespeare]

knew it...

 

[dhookom]

The other issue I forgot to mention was the month will not limit the year. What happens if you have Mar records for 2009 and 2010? Do you want to report them together?

Duane
Hook'D on Access
MS Access MVP

 

[Fred1981]

Please assit thisis the DB access link

 

[dhookom]

Fred1981,
You zipped a file that when extracted is 46 MB. If you had compacted the MDB it would have been only 1.8 MB. Your file makes use of OCX controls that many of use won't have referenced as well as using the ENVIRON() function which also may not work.

In the future, create a new blank MDB and import in only the required objects (tables/forms/queries/reports/modules). Compact prior to zipping. Then provided your questions and references to the forms/reports etc either in the mdb or in your posting.

Duane
Hook'D on Access
MS Access MVP