Searching multiple fields that aren't a given for input

[Dal Jeanis]

I agree with June about "why bother". The standard way is easy and achieves the need.

[WithoutPause]

It just seems like an odd way to do it. There's too many fields on some of the forms to simply have unbound and their respective bound fields on the same form. I think I'll just pass the results to a new form and have both forms mimic each other in a way so they look the same with some minor differences. Since I wasn't getting my results back 100% correctly, I'll likely be back later today to get further help in that regard. It is mostly in regards to non-text fields.

[Dal Jeanis]

Normally, the number of filter fields is vastly smaller than the number of fields to be displayed for update. Also, the filter fields are generally NOT fields that are going to be updated - for example, the client company ID, the type of invoice, the date range. If every record to be displayed all have the same value for those fields, and if the fields will not be updated, then there is no reason to display them on the subform/detail form. The classic example would be a form that allows you to look at all the invoices for a particular client. The top of the form allows you to pick the client, and the bottom displays the invoices.

Your idea of passing forward the results is just fine. You'll probably want a command button to cause the form to open, and it's your call whether to try to pass the user back to the filter form afterwards.

[WithoutPause]

I got the search working but when I click on a button to go to the next record it seems to add in a blank record at the end of the results. Once I click on go to the next record again, it says it can't since there are no more. In other words, if I do a search and should get one record back, it returns two with one of them being blank.

Edit: Fixed.

[WithoutPause]

How do I search for the time?

[June7]

Do you want to search for a particular hour of any and all dates or just the hour on a specific date?

Do you understand that date/time value in a Date/Time type field is actually stored as a number?

Are you using the Allen Browne code method?

[WithoutPause]

The dates are a separate field. I'm looking for any time within the short time format. I am using the Allen Browne method, which I did have to tweak since the check box code wasn't working for what I wanted.

[June7]

The date and time parts are in separate fields? The fields are Date/Time type?

User inputs something like 15:23 into unbound search textbox?

If a Date/Time type field contains only time part, Access will assume a date of 1/1/1899.

So the string construction will be like:

"Format([fieldname], 'Short Time')='" & Me.textbox & "'"

Or create a field in query that is the RecordSource for form or report that calculates the short time and apply the filter criteria to that constructed field:

"[fieldname]='" & Me.textBox & "'"

Use the apostrophe delimiters because the values are text, not date/time.

[WithoutPause]

How do I add in the "AND" at the end? This gives me an error.

strWhere = strWhere & "([C05] >= " & Format([Me.C05], 'Short Time')='" & Me.C05 & AND "'"

[June7]

strWhere = strWhere & "([C05] >= " & Format([Me.C05], 'Short Time')='" & Me.C05 & "' AND "

[WithoutPause]

The code text still goes red after I go to another line.

[June7]

Sorry, I didn't look at the rest of the expression. Why is [C05] field referenced twice (that was not in my suggestion)? The expression is nonsensical.

strWhere = strWhere & " Format([Me.C05], 'Short Time')>='" & Me.C05 & "' AND "

[Dal Jeanis]

1) As standard practice, when building a complex condition, I would include the "AND" at the beginning of each clause after the first, rather than at the end of the previous clause. For the first clause, I would begin with either "WHERE ", or nothing, depending on whether the SQL was being used as a filter or an entire query.

2) Also as standard practice, I would make sure that the controls on the form do NOT have the same name as the fields on the database. I know that's the default for MS, but making them different clarifies what item is being referred to in any given line of code.

3)
If your database is storing exact times, and you want to search on time, then you may need a more complex condition.
Searching for times can be pretty squirrely. If those are exact times in your database, then you probably want to generate a test like "[Mytimefield] between X and Y", where X and Y are mathematically generated from the time field on the form. You can use the Hour function to make this easy.

" (Hour([C05]) BETWEEN " & (Hour(Me.C05) - 1) & " AND " & (Hour(Me.C05) + 1) & ") AND "

[WithoutPause]

When I run the application as is and do a search, the resulting form only shows the form header info and the Detail part is left blank. If I use a break point to see what is going on, the search runs fine and everything shows up correctly. If I click on the Toggle Filter button, the form shows up with unfiltered data. If I click it again, the form shows up with the correct data for my search.

There seems to be a similar issue when I am passing data from one form to another without doing a search. The first time the new form opens, the data isn't showing. If I go back to the previous screen and retry, the data shows up. Again, if I do it the first time and click on the Toggle Filter button twice, my data shows up.

Also, if there is no search criteria, how can I have it return all records?

[June7]

Options:

1. parameterized query using LIKE and wildcard

2. If there is no criteria input then don't set Filter property, and set the FilterOn property to False