Query using date filter from textbox

[Wilse]

Hi guys,

My mind has gone blank with this one,

I have a textbox on the main form called monthtext. I want to use the data from this to filter a query to display records from only the month selected. I have tried a few ways but for the life of me I cannot work this out.

Any help would be great

 

[TallMan]

Hey Wilse,

I would go to your query and in the "where criteria" right click. click build > Forms > loaded forms > select your form > select your monthtext text box and hit ok. Once you change the date on the actual form text box it should relate to the underlying query.

 

[Wilse]

I have tried to do it that way but I have no joy so far, only if the data is from the same date of the month as today. I think it is because the textbox shows the day month and year

 

[TallMan]

I am a little confused. What is the date format in the table the query is running off of?

 

[Wilse]

03/01/2010 is the format in the table

 

[TallMan]

That is strange. If you are entering the date in the text box on your form in the same format that it is within the underlying table you should be pulling data.

Silly question but is there data for that particular date in the underlying table? Try putting double quotes around the date in the text box.

 

[Wilse]

Maybe not,

the textbox displays todays date as on the forms load

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

The user then changes the month using command buttons one to go back and month and one to go forward. I though it would be the same format but im no expert.

 

[TallMan]

once the user selects the correct month, what do they do? hit a submit button? What I would do first is look in the underlying table you query is working off of. Find a recordset with a date. Then try that date in the form text box so that you know you are working with a legit record. If the user clicks a button or if there is an after update function can you send me the code?

 

[Wilse]

I had managed to do it before using two textboxes with

Between DateSerial([forms]![main]![cboYear],[forms]![main]![cboMonth],1) And DateSerial([forms]![main]![cboYear],[forms]![main]![cboMonth]+1,0)

placed in the Cirteria part of the query, I cant get it to use one textbox.

The plan was the form was kept open in the background to allow the queries and filters to run off.

I managed to get a form to filter by using

DoCmd.OpenForm "01 Contam View", acNormal, , "[Date Damaged]>=#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext), 0) & "# AND " & _
"[Date Damaged]<=#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext) + 1, 0) & "#"

when a command button is clicked.

Have I made things clearer or shall i attach the database?

 

[TallMan]

Please attach. I may not have time to look at it today though.