filter form by specific date from text box

[johnnychow]

Hi: Please tell me what's wrong on the following, the beging date is ok but the end date is error. If put actual date instead, the statement will be ok. why?

Dim LSQL As String

LSQL = "Select * from [Offering] where [Offering].[OfferDate]" & _
"between #" & Format(Me("TxtBeginDate").Value, "mm/dd/yyyy")" & _
"& "# And #" & Format(Me("TxtEndDate").Value, "mm/dd/yyyy") & "#"

[Form_Statement Subform].RecordSource = LSQL

or

LSQL = "select * from [Offering] where [Offering].[OfferDate] between" & _
"#" & Me.TxtBeginDate & "# and #" & Me.TxtEndDate & "#"

 

[boblarson]

Why change the record source? Just use the Filter property:

Dim strWhere As String
 
strWhere = "[OfferDate] Between " & _
"Format(Me.TxtBeginDate, "\#mm\/dd\/yyyy\#)" & _
" And " & Format(Me.TxtEndDate, "\#mm\/dd\/yyyy\#)"
 
 
Me.Filter = strWhere
Me.FilterOn = True

Then to set it back just use

Me.Filter = vbNullString
Me.FilterOn = False

by the way the \ part in the format tells it to use the explicit character, which can help if regional settings are messing things up.

 

[johnnychow]

Why change the record source? Just use the Filter property:

Dim strWhere As String
 
strWhere = "[OfferDate] Between " & _
"Format(Me.TxtBeginDate, "\#mm\/dd\/yyyy\#)" & _
" And " & Format(Me.TxtEndDate, "\#mm\/dd\/yyyy\#)"
 
 
Me.Filter = strWhere
Me.FilterOn = True

Then to set it back just use

Me.Filter = vbNullString
Me.FilterOn = False

by the way the \ part in the format tells it to use the explicit character, which can help if regional settings are messing things up.

Hi! has a syntax error, can you help me most.

 

[boblarson]

Sorry, got a few things out of place, it should be:

strWhere = "[OfferDate] Between [COLOR=red][B]" &[/B][/COLOR] Format(Me.TxtBeginDate, "\#mm\/dd\/yyyy\#[B][COLOR=red]")[/COLOR][/B] & _
" And " & Format(Me.TxtEndDate, "\#mm\/dd\/yyyy\#[B][COLOR=red]")[/COLOR][/B]