How to use multiple (Form) text box fields in a query

[kevgoodwin]

Hi,

I would like to run a Query using the following data from Text boxes in a Form:

Query Criteria: [Forms]![SalesLettersQry]![Type], [Forms]!SaleslettersQr y]![Date]

Query Field: [Booking Date]

The [Type] text box has a list of < > Between Date() etc that the yser can select.
The [Date] text box is to enter a date.

I was hoping the result would work somthing like e.g Criteria: </1/1/08 if < and the date was inputted into the text fields.

Can this be done in a Query?

Hope you can help
Kevin

[jimatqsi]

Sorry, but I can't make much sense out of your question. What is in the Type box? And how does it relate to the query you want to run?

You have given a Query field [Booking Date], which I presume to mean you want to test the content of column [Booking Date] against the value in [Forms]!SaleslettersQr y]![Date]; that part seems clear. But what column would this Type value be tested against?

Jim

[kevgoodwin]

Hi Jim,
Thanks for your reply.

To answer your question, the Type box contains a list of... <, >, =, etc. I was hopeing to use it like a Query Criteria. For example the user would select "<" from the Type Box then a date from the Date box. I hoped this would result in all dates less than the date given.

As an alternitive, can a single Form Text Box contain; '<=04/01/2011' to run a query?
Kevin

[munkee]

type box is a drop down list / combo box you mean with the values of >,=,< etc in. So you can basically say you want to look at everything before a date, at a date or after a certain date?

[kevgoodwin]

Yes, this is correct. It is a combo box with a list of the values.
Before a date, at a date and after a date is correct.

Also, if there is a way of between dates, that would be even better. I assume i would need a 2nd Date text box to do this?

[jimatqsi]

Yes, no problem to use between. For dates, the criteria would be "where fieldname between #x/xx/xx# and #x/xx/xx#"

Or >,<,=,>=,<= ... all use similar construction
"where fieldname >#x/xx/xx#"

Jim

[munkee]

I use two textboxes with a calendar control beside them to enter dates in to them.

The first text box is named "start date" and the second is name "end date".

If a value is only placed in "end date" then all records are returned up until this date.


If a value is only placed in the "start date" all records after this point are returned.

If values are placed in both text boxes then all records between the two dates are returned.

All of the >,<,between etc is handled in code in the background. Just a thought that might make life easier for you.

I think Allen Browne uses this style as an example in his form filter script which you might be able to take the coding idea from.

http://allenbrowne.com/ser-62.html

[kevgoodwin]

Hi Jim,

Thanks for this... but where do i put "where fieldname >#x/xx/xx#"
Is it in the query criteria? If so, does fieldname represent the textbox name?
Kevin

[kevgoodwin]

Hi munkee,
i have tried somthing like this and have had problems with the between criteria. I will look at the Allen Browne code.
Thanks