Between Function In VBA

[Lex]

Is it possible to use a Between function in vba as you can in a query? Also how can i reference a field in another table other than the one I am currently in?

 

[Jack Cowley]

Your question is very general so I will say 'Yes' to the first question and 'Code' to the second. You did not mention a form, but I assume you are in a form and not a table in your second question.

Jack

 

[Pat Hartman]

The other way to reference fields from multiple tables in a form is to use a query that joins the tables as the recordsource for the form. So to populate an order form, join customer and order tables. That way when the user selects a customer from a combo, the customer identification fields will populate and you can copy the customer's address as the default ship-to address.

 

[Lex]

Thanks on the query info, I just didnt think about that. On the between part how would you write the following for a number field on a form

"OrderAmount Between 50 and 75"

I have tried the <> but because it is a number field(as double) it shows me all of the records not just the one between 50 and 75.
thanks again later

 

[Jack Cowley]

You can use criteria in the query based on two unbound text boxes on the form. If the two unbound text boxes are called Control1 and Control two then you can enter one number in one and the other number in the other and then requery the form. In the query use code like this in the field you want to filter on:

Between [Forms]![NameOfYourForm]![Control1] And [Forms]![NameOfYourForm]![Control2]

hth,
Jack