Filter and Sort a form based on TextBox Values.

[Ramun_Flame]

I have a few text boxes that have values assigned to them. I want to be able to filter and sort based on those values in the them, but I can't seem to figure out a way.

Let's say I have a Textbox called EndTime. This is not a field in the table, it is a text box taking the value of StartTime, a field in the table, plus an hour. Don't tell me to just filter based on the start time, since this is just an example, and I want to know how to do it with the textbox.

So this is what I would use to filter:

DoCmd.ApplyFilter , "EndTime < Now()"

This would prompt me for the value of EndTime, since it doesn't realize it is a textbox. It must be looking for a table field called EndTime, but there isn't one.

This is what I would use to sort it ascending:



DoCmd.SetOrderBy "EndTime ASC"

This would do nothing.

I've been looking all over for a way to reference a textbox in these commands, but I can't seem to find anything. Is it only possible to reference table fields here? If so, please let me know, so I can rethink how I am doing this.

[June7]

Don't put variable within quote marks. Reference to control on form is a variable. Concatenate variables. Either of these should work:

DoCmd.ApplyFilter , Me.EndTime & "<Now()"
DoCmd.ApplyFilter , Me.EndTime & "<" & Now()

Then
DoCmd.SetOrderBy Me.EndTime & " ASC"

[Ramun_Flame]

StartT is a field in the table, EndT is a textbox on the form with the control source of =StartT + 1/24(adds an hour)

Okay, I tried a few things.

DoCmd.ApplyFilter , Me.StartT & "= #11:00AM#" This does nothing.

DoCmd.ApplyFilter , Me.EndT & "= #11:00AM#" This does nothing.

DoCmd.ApplyFilter , "StartT = #11:00AM#" This works.

DoCmd.ApplyFilter , "Me.StartT = #11:00AM#" This asks me for what is Me.StartT.

DoCmd.ApplyFilter , "EndT = #11:00AM#" This asks me for what is EndT.

DoCmd.ApplyFilter , "Me.EndT = #11:00AM#" This asks me for what is Me.EndT

Not sure what to try now.

[June7]

I knew that expression looked odd to me, should have thought about it some more. It doesn't make sense to set the value in textbox = to another value as filter criteria.

I've never used the ApplyFilter or SetOrderBy methods so this is a learning experience for me.

This expression must be like the WHERE clause of an sql statement. That means the criteria must include a field that is in the form's RecordSource, just as you asked earlier.

The textbox can provide the parameter for the filter expression, as in:

DoCmd.ApplyFilter , "StartT = #" & Me.StartT & "#"

[Ramun_Flame]

So then, is there another way to filter or sort based on a textbox then? Since the expression I was trying only takes tables fields.

[June7]

That IS filtering/sorting based on textbox. Any filter/sort process must identify the field that the parameter is applied to. Textbox provides the parameter for the expression.

[Ramun_Flame]

Yeah, but I don't want to check if a field is equal to a text box, I want to check if a text box is equal to a value.

Edit:

I don't need to do this anymore. Instead of using Dlookup to show information, I just made a query with a one to many relationship between the tables. This means I can update the table, and have all the fields, while keeping them separate for organizations sake. Thanks for the help anyway,

[Rainlover]

If you designed this in the Query Grid it would be so much easier.

One you got it working you could copy the SQL and use that in Code as your recordsource.

<< Don't tell me to just filter based on the start time, since this is just an example >>

I don't understand why not. Placing a Formula on the start time gives the result you want.

I must be missing something.