Help with query

[Wilse]

Hi guys,

I am trying to run a query that will only display data which dates are within the month and year selected by a text box on the main screen. I am having so many issues with this.

I have tried google but no joy so far. The textbox is on a form called MainMenu and the textbox is called monthtext.

Can anyone help me?

I have set up on the main menu with visual basic so the textbox displays the month and I have two command buttons which change the month back or forward months. Do I need any coding to run the query each time I change the month to update the query?

 

[ghudson]

Yes, you need to re-run your query with the date parameters selected from your form. The date criteria in the form must build a Between #01/01/2010# And #03/31/2010# as an example of a valid date parameter in the criteria field of your query. Which must reference the text boxes [combo boxes?] in your form.

 

[Wilse]

I only have one text box that displays the current month when the form is loaded. I have two buttons then will then change this month. That works and I have managed to get it to filter the data when I wanna view it by the month. I just need the query to filter data by what month and year is in the textbox. I need the expression to put into the criteria field to filter by what is in the textbox on the mainmenu form.

 

[Wilse]

Can anyone help me please?

 

[vbaInet]

What have you done so far? Show some example expressions.

 

[Wilse]

I have been through google trying ones I have come across. I also have used a table as the control source with the default value as Now() formatted mmmm yy and and went to use that to filter the results but then I lose the command button. I have got other forms to filter using the textbox but I have no idea on how to use the textbox to filter the query.

 

[Wilse]

I can get it to work with the Between #01/01/2010# And #03/31/2010# type critiera but i have used different variations of forms!mainmenu!monthtext as search google and forums. Sorry for being vauge, im not sure what ones I have used and I really dont know what I am doing

 

[ghudson]

I can get it to work with the Between #01/01/2010# And #03/31/2010# type critiera but i have used different variations of forms!mainmenu!monthtext as search google and forums. Sorry for being vauge, im not sure what ones I have used and I really dont know what I am doing

You have that right forms!mainmenu!monthtext syntax but you have to turn that into a true date that can be queried. You either need to have the user select a date mm/dd/yyyy or create the date if they only have to select a month. Without seeing your form we do not know what we are working with to provide you an answer.

 

[Wilse]

Heres the database,

The textbox is on the mainmenu form and the query I want to filter is Data Query

 

[ghudson]

How can you build a between dates criteria for your query if there is only one text box for a date? Also, your month function is not allowing the user to select the day of the month. You need to give the user a beginning date text box and a ending date text box so that you can build your between dates criteria for your query. Format the text boxes so the user can see the complete mm/dd/yyyy date.

 

[Wilse]

I dont know mate, I am new to this. I just want it to select everything for that month and only that month.

 

[vbaInet]

How can you build a between dates criteria for your query if there is only one text box for a date? Also, your month function is not allowing the user to select the day of the month. You need to give the user a beginning date text box and a ending date text box so that you can build your between dates criteria for your query. Format the text boxes so the user can see the complete mm/dd/yyyy date.

I had even given the OP an expression using DateSerial() to get the first day of the month for start date and the last day of the month for end date.

 

[vbaInet]

Wilse: Have a look in the help files for Month() function and checkout that DateSerial() function I gave you in your db.

 

[Wilse]

guys I am going around in circles here

 

[vbaInet]

You do realise that we're trying to help?

 

[Wilse]

I know mate but two bloody days on this. I know im a pain and im sorry,

This is to find the first and last days of the month..........

"[Date]>=#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext), -1) & "# AND " & _
"[Date]<=#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext) + 1, 0) & "#"

 

[vbaInet]

The 2 days spent on your problem was your fault though, not ours If we're working and busy we can't get to answering all questions. It's a free service that we offer.

So, right that gets the first and last days of the month. What next?

 

[Wilse]

cheeky, and I appreciate your time and help guys.

Private Sub Form_Load()
DoCmd.Maximize
monthtext = Now()
End Sub

Is that correct on the form load to get the date for the textbox

 

[SOS]

Have a combo box to select the month (use month number as the value) and a combo box to select the year. Then you can simply use:

For dates between the first of THIS month and the last day of THIS month:
Between DateSerial(Year(Me.cboYear), Month(Me.cboMonth),1) And DateSerial(Year(Me.cboYear), Month(Me.cboMonth)+1, 0)

For dates between the first of LAST month and the last day of LAST month:
Between DateSerial(Year(Me.cboYear), Month(Me.cboMonth) +1 ,1) And DateSerial(Year(Me.cboYear), Month(Me.cboMonth)+2, 0)

 

[vbaInet]

Also, if you're storing a true Date field why not just compare the month value?

... WHERE Month([Date_Field]) = " & Month(Me.cboDate)