Search using Date function

[beanbeanbean]

Hi, im trying to do a search fuction using a calendar. This search function allow the user to select and extract out all the records in the DB between a Start Date(xx/xx/xxx) and an End Date(xx/xx/xxxx). Can any one help me with this =).

Thanks alot

 

[CyberLynx]

I personally always (well...not always) dynamically create a SELECT query in code for this sort of thing:

Me.[I][COLOR="Red"]MyFormName[/COLOR][/I].RecordSource = "SELECT * FROM [COLOR="Red"][I]MyTableName[/I][/COLOR] WHERE [[I][COLOR="Red"]MyTableDateField[/COLOR][/I]] " & _
                             "BETWEEN #" & Me.[COLOR="Red"][I]MyFormsStartDateTextBox[/I][/COLOR] & "# AND #" & _
                             Me.[COLOR="Red"][I]MyFormsEndDateTextBox[/I][/COLOR] & "#;"

Or use a Filter

Dim FilterClause As String

FilterClause = "([[COLOR="Red"][I]MyTableDateField[/I][/COLOR]] BETWEEN #" & Me.[COLOR="Red"][I]MyFormsStartDateTextBox[/I][/COLOR] & _
               "# AND #" & Me.[COLOR="Red"][I]MyFormsEndDateTextBox[/I][/COLOR] & "#)"

Me.Filter = FilterClause
Me.FilterOn = True

You will of course need to ensure that the names in the code above which are red italic are changed to the names you actually have in your Database.

.

 

[beanbeanbean]

Hey CyberLynx, Thank alot for your code, the first code work, but there are still question that i need to ask.

Me.[I][COLOR=red]MyFormName[/COLOR][/I].RecordSource = "SELECT * FROM [COLOR=red][I]MyTableName[/I][/COLOR] WHERE [[I][COLOR=red]MyTableDateField[/COLOR][/I]] " & _
                             "BETWEEN #" & Me.[COLOR=red][I]MyFormsStartDateTextBox[/I][/COLOR] & "# AND #" & _
                             Me.[COLOR=red][I]MyFormsEndDateTextBox[/I][/COLOR] & "#;"
 
This code work well if im only using this field in my form, but i have other field such as using listbox and combo box, it does nt fit in. 
Can the "Me.[I][COLOR=red]MyFormName[/COLOR][/I].RecordSource" be change to others, rather then FormName. The reason is because i have a code which is 
 
 Private Sub btnSearch_Click()
    
    ' Update the record source
    Me.frmsubRecords.Form.RecordSource = "SELECT * FROM qryrecords " & BuildFilter
    
    ' Requery the subform
    Me.frmsubRecords.Requery

 

[CyberLynx]

In short....Yes.

For your Combo Boxes and List Boxes you can use the same method but in the Row Source property rather than the Record Source property which the Combox and List Boxes do not have. You also want to make sure that the Columns are going to be set up properly according to your SELECT query.

For your SubForm....Yes you can apply this sort of Query to the Record Source property of your SubForm Form as well but...if there are linked Master and Child Fields within the SubForm Control then you need to ensure that your Query does not jeopardize that link.

.

 

[Lady Gragor]

Alternatively, you could build a query and in the date field criteria put something like this:

Between [Forms]![YourFormName]![cbo_StartDate] And [Forms]![YourFormName]![cbo_EndDate]