Querying by vba

[ebasta]

Hi,

I have a query with no criteria, but only fields.
I would like to say to the query, using vba code, which are the criteria to use.

 

[maxmangion]

why don't you simply put your criteria directly in the query rather than using vba ?

 

[ebasta]

My form has 2 textControls for each field and a checkBox.
If the checkBox is checked the two textControls will be enabled.

In these you have to put you criteria (e.g. from 20 to 5000)

I tryied to put everything in a Query, but if I don't write any cirteria in some fields it doesn't work, or better it works, but in the list box (connected to the query to shoe results) there's no record!!!

So I was thinking about vba code!

 

[maxmangion]

My form has 2 textControls for each field and a checkBox

Do you mean that you have 2 bounded text boxes for a field in your table ?

Have you considered using unbound text boxes ?

 

[ebasta]

I haven't got a clue!

It's the first time i hear about unbound text boxes!

 

[maxmangion]

if the values you are putting in your textbox are used simply for passing criteria values, then you should use an unbound textbox. However, if you need to save your value in a table you need to use a bound textbox (i.e. bounded to a field in a table)

 

[ebasta]

aH, OK!

They're not bounded!
I'd like to use their values to set a sql statement or somenthing like
"between " & textBox1.value & "and " & textBox2.value

 

[maxmangion]

therefore in the criteria of your query why don't you refer to the textboxes directly by placing [Forms]![FormName]![TextboxName]

 

[ebasta]

i've aleady tried

When I put between textbox and textbox2 in the criteria of the query

if I don't fill something in the textboxes, in the listbox there won't be any records!

 

[maxmangion]

try using:

Not Is Null(textboxname)

 

[ebasta]

Do you mean I have to force users to put some criteria?
Because my goal it's different.

 

[ecniv]

The option I would choose...
- create the sql statement to return all the fields with no criteria
- change to Sql view and copy the text
- in vba, use a string set to the sql you just copied (take off the semicolon at the end)
- using vba to check if the textboxes have anything in and add the appropriate where clause (between or >= or <= etc) possibly to a second string variable if there are mulitple clause sections
- if the where clause is there add it to the sql string held
- set the list box rowsource to the completed sql string
- requery the list box

Text boxes are unbound, validation checks you'll have to do. The above can be put into a button to refresh the list box


Vince