Date Range Syntax

[livvie]

I am trying to open up a form using the following linkcriteria
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmNewDrawings"
stLinkCriteria = "[datefiled] " & "Between " & Me.txtstart & " And " & Me.txtend

DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
but I cannot get it to return records. I presume it is something to do with the date format. I have tried just copying the syntax from a query but that doesn't work either. Any help anyone?
I am using Access 2003 .adp

I have also tried
stLinkCriteria = "[datefiled] " & "Between #" & Me.txtstart & "# And #" & Me.txtend & "#"

also
DoCmd.OpenForm "frmNewDrawings", _
WhereCondition:= BuildCriteria( "datDateField" _
, dbDate _
, "Between " & txtStart & " And " & txtEnd )
also


Dim strCriteria as String
strCriteria = ">= '" & Forms!<FormName>.txtStart & "' AND <= '" & Forms!<FormName>.txtEnd & "'"
Me.ServerFilter = strCriteria

This opens the form but the records aren't filtered this is the value in strCriteria when debugged
"[datefiled] >= '22/09/2004' AND <= '24/11/2004'"
and [datefiled] = 30/11/2004

also strCriteria = "[datefiled] >= '" & Format(Forms<FormName>.txtStart,'Short Date') & "' AND [datefiled] <= '" & Format(Forms!<FormName>.txtEnd,'Short Date) & "'"
strSQL = Select <FieldList> from <Table> Where " & strCriteria
Me.RecordSource = strSQL

This one says format is not a recognised function name.

 

[reclusivemonkey]

You could try creating a query for the form, and using that. The correct format to put in the date field in the query is:-

Between [Forms]![frmName]![StartDate] And [Forms]![frmName]![EndDate]

And then you could use;

DoCmd.OpenForm (frmFormname, acNormal , qryFilterName)

To open the form. If this doesn't work let me know I'll get back to you with a more detailed answer.

 

[livvie]

start date and end date aren't fields they are text boxes that the user puts a date into the date I am checking is datefiled?

 

[WayneRyan]

livvie,

strCriteria = "Between #" & Forms!<FormName>.txtStart & "# AND #" & Forms!<FormName>.txtEnd & "#"

Wayne

 

[livvie]

Wayne
With this piece of code what field am I checking against. I need to check that the datefiled date is between the start and end dates entered.
When I justuse this line without my datefiled field I get an error near Between. If I inlcude [datefiled] then i get the sql error.

 

[WayneRyan]

Livvie,

I need a sample here. If you enclose the date parameters in "#", then
Access will understand them.

Need more info.
Wayne

 

[livvie]

Wayne
In the form load of the form I have the code from post 1:
What I am trying to do is as follows
I have an input form where the users enters a start date (txtstart) and and end date (txtend) and the new form is loaded with the records whose [datefiled] falls between these dates.

This is the code I am using:
Private Sub Form_Load()

Dim strCriteria As String
Dim strSQL As String

strCriteria = "[datefiled] >= '" & Forms!frmDateRange.txtstart & "' AND [datefiled] <= '" & Forms!frmDateRange.txtend & "'"
strSQL = "Select * from SQLACCESS.tbldrawings Where " & strCriteria
Me.RecordSource = strSQL

End Sub

but I have also tried the other variations posted in post 1.

 

[Mile-O]

"[datefiled] Between #" & Forms!frmDateRange.txtstart & "# AND #" & Forms!frmDateRange.txtend & "#"

Dates require the # delimiter.

 

[Mile-O]

Why are you making this query in VBA anyway? It's unnecessary.

Just create a normal query with the query grid and put:


Between [Forms]![frmDateRange].[txtstart] And [Forms]![frmDateRange].[txtend]

as the criteria.

And then just set the form's RecordSource as the query by default.

 

[livvie]

I am using Access .adp and you cannot reference a form from the query grid in .adp (or so I have been told and I can't get it to work)

 

[reclusivemonkey]

Sorry livvie, no experience in .adp, but I had some similar trouble with this kind of thing before. I've made some progress. I made a quick table with dates for each day in december. Using the following as code to open the form works;

Sub OpenFilteredForm2()

Dim myStartDate As Date
Dim myEndDate As Date

myStartDate = InputBox("Please enter a Start Date", "Start Date")
myEndDate = InputBox("Please enter a End Date", "End Date")

strWhere = "(((Table1.DateFiled) Between #" & myStartDate & "# And #" & myEndDate & "#))"
strFormName = "FormDate"
DoCmd.OpenForm strFormName, acNormal, , strWhere

End Sub

One of the problems is that the SQL seems to want an american date format. If you enter the dates in American format (mm/dd/yy) it works. Hopefully this is enough to get you to where you want. I am sure you can add something to convert the date format from english to american after its been entered. If not I am sure someone here will!