Problem with Date Range search Nav Form

[accadacca]

Im attemting to create a search between 2 dates on a navigation form, the particular form is a continuous form using a query.
The VBA code sample I aquired from a Utube Tutorial
The VBA code in the form when not attached to the navigation form works fine , when running the code under the nav form I get the error:'

Run-Time error 2491
The action or method is invalid because the form or reprt isnt bound to a table or query.
Here is the VBA Code in question

Option Compare Database
Option Explicit

Private Sub cmdSearchPC_Click()
' Search Button
Call Search
End Sub

Sub Search()
Dim StrCriteria, task As String
Me.Refresh
If IsNull(Me.txtDateFromPC) Or IsNull(Me.txtDateToPC) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.txtDateFromPC.SetFocus
Else
StrCriteria = "([DateCreated] >= #" & Me.txtDateFromPC & "# And [Datecreated] <= #" & Me.txtDateToPC & "#)"
task = "Select * ProgressClaimList2Q where (" & StrCriteria & ") order by [DateCreated]"
DoCmd.ApplyFilter task


End If

End Sub

 

[JHB]

Try the below instead:

  ..
  StrCriteria = "([DateCreated] >= #" & Me.txtDateFromPC & "#  And   [Datecreated] <= #" & Me.txtDateToPC & "#)"
  Me.Filter = StrCriteria
  Me.FilterOn = True
End If

 

[accadacca]

How good is that
spot on

Thankyou

 

[Galaxiom]

A Filter is not a full SQL query, just the Where clause without the word "Where".

Literal dates in SQL must be in the format #mm/dd/yyyy#
(Always beware US sourced code with dates)

BTW Instead of >= And <= use the Between operator:
[somefield] Between value1 And Value2

 

[Galaxiom]

Also note the declaration variables in VBA. Each variable must have it type specified separately.

In this code, strCriteria will be a Variant

Dim StrCriteria, task As String

 

[accadacca]

I updated the datatype to "Dim StrCriteria As Variant"
I do understand now as you explained dates in SQL must be formated right and will attempt to amend the code with between operators just to be sure im using the correct metthods in VBA for this particular operation.
But im still not sure what you meant in the first statment about the where clause.

None the less your solution has resolved the problem
Thankyou

 

[Galaxiom]

Variant is the default type when the variable type is undeclared. It was not declared in your code because the two variables do not share the type declaration as supported in some other programming languages.

To make them both strings they must be declared individually.

Dim StrCriteria As String, task As String

SQL requires dates in the US format with the month first (mm/dd/yyyy). In regions where such as Australia where we have dd/mm/yyyy dates, all date variables must be reformatted to mm/dd/yyyy when concatenated into SQL.

I use this expression to reformat all dates. It includes the delimiters in the output.

Format([datefield], "\#mm\/dd\/yyyy\#")

The backslash is the literal escape character which forces the next character to be included in the return value of the Format function, exactly as entered, regardless of any regional format settings.

JHB has shown another way to express a Filter. Either way the filter is only the Where condition, not the full SQL Select query you used in your original post.

 

[accadacca]

Seems to working except for one problem, in the search variables if a single day digit is used for between dates for eg 3/8/16 to 5/8/16 the search returnes nothing just an empty list but will work with double digit days for eg. 16/9/16 to 20/9/16. and will also work with date range for eg. 3/8/16 to 15/8/16

 

[accadacca]

([DateCreated] between #3/08/2016# And #4/07/2016#)

This also appears on the forms filter proberties after selecting the date range with single day digits
Does this have something to do with thr formating? if so im not sure where to implement this.

 

[Galaxiom]

It has everything to do with formatting. Windows will silently reinterpret dates that don't fit the expected context. So your July and August dates get moved to March and April.

The date format expected by SQL is mm/dd/yyyy.

BETWEEN Format([firstdate], "\#mm\/dd\/yyyy\#") AND Format([seconddate],"\#mm\/dd\/yyyy\#")

 

[Galaxiom]

([DateCreated] between #3/08/2016# And #4/07/2016#).

As well as mm/dd/yyyy, the earliest date must be first.

 

[accadacca]

The date Ranges i select has always been earliest date first. Please excuse my lack of VBA and SQL knowledge but i am still learning especialy when it comes to dates its a little confusing.
The code you have provided I dont know exactly where this is meant to be added.

Or for that matter where to include all the date formating expressions for this function. ive tried several ways but cannot get it to work.

 

[accadacca]

Ive got it, took me a bit with this

 StrCriteria =  "([DateCreated] between #" & Format(Me.txtDateFromPC,  "mm\/dd\/yyyy") & "# And  #" & Format(Me.txtDateToPC,  "mm\/dd\/yyyy") & "#)"
    Task = "Select * ProgressClaimList2Q  where (" & StrCriteria & ") order by #" &  Format(Me.DateCreated, "mm\/dd\/yyyy") & "#;"
    Me.Filter = StrCriteria
    Me.FilterOn = True

Thanks Galaxoim

 

[Galaxiom]

Glad you got is sorted.

You wouldn't need the line
Task = "Select ......

 

[EvyAnggraeni]

Dear Galaxiom,

Such an honour to read a tips from you to solve the problem with date range.
I am a newbie about access and actually I have the same problem with accadacca.

I was follow your guidance but when I try to used the “search” button in my form, there’s something error again about strcriteria.

Do you have an explanation what’s wrong with my VBA code?

Thank you