[RESOLVED] DataEnvironment Filters

[I_Love_My_Vans]

I believe I need to use the .Filters property of a data environment command, I need to return all records between two dates, what syntax should I use?

[ne0_b0mb3r]

lolwut?

[I_Love_My_Vans]

Nice

Using the Filter attribute e.g.

DataEnvironment1.rsRecordset.Filter ="blah"

This will return the recordset with only they applicable records that pass the filter, I need to filter between two dates, what syntax should I use, is it the same as SQL, because I didn't have much luck with that.

[Hack]

I would highly recommend SQL for this.

What SQL code did you try with which you did not have much luck?

What kind of database are you using?

[I_Love_My_Vans]

Let me explain, I am using the Date Environment to interface with an Access database for a college project.

I do not want to use SQL in that concept because my friends have easily used a filter to sort an existing recordset.

All I need to know is what syntax to use, to make sure the [date] field is between tomorrows date, and the date a year and a day down the line.

[leinad31]

Let me explain, I am using the Date Environment to interface with an Access database for a college project.

I do not want to use SQL in that concept because my friends have easily used a filter to sort an existing recordset.

All I need to know is what syntax to use, to make sure the [date] field is between tomorrows date, and the date a year and a day down the line.

The filter is based on the WHERE clause concept of SQL. You'll have to learn it one way or another.

As to your use of Filter, it's not very efficient. You end up loading first most of the records (if not all) of the table into memory before trimming it down. Bad habit to acquire.

Lastly, the data environment is not scalable and easily becomes a white elephant in terms of updatability and maintenance.

[wes4dbt]

This is the syntax,

DataEnvironment1.rsCommand1.Filter = "startdate >=  #10/1/2007# and startdate <= #10/10/2007#"

if you are going to use variables then you have to format the string properly,

DataEnvironment1.rsCommand1.Filter = "startdate >=  #" & varStringdate1 & "# and startdate <= #" & varStringdate2 & "#"

[I_Love_My_Vans]

Hello! Update:

@Hack: I know SQL would be more effective etc, in this instance I did not have time to use SQL to the extent I wanted, thanks for your help regardless
@leinad31: Same again, the Data Environment is a load of crap, not only that its ability to create reports is less than a small child, thanks for your help
@wes4dbt: Thanks! That is the code I eventually managed to generate, thanks for your help

Resolved!

FYI: If you have come across this thread in a search, please use SQL, date environments suck.

[leinad31]

Use of data environment is usually tied to the assumption that it's needed in order to use the data report. This is not so, you can use data reports bypassing design time set-up once you've learned SQL http://www.vbforums.com/showthread.php?t=433765