Criteria using textbox

[JPR]

Hello.

My table (table1) has a field (RTN) which refers to bank codes made of 9 digits.

The first two digits indicate the country in which clients have their account (Country Code). which can be in a Country different from where they live.

Example: RTN 001111111 - RTN: 112222222 - RTN: 223333333

00 is the country code for US
11 is the country code for Germany
22 is the country code for Spain

Since US is the country of residence of all clients, I have placed a textbox (txt1) on a form (form1) with the digits 00.

My query should search all records that in the first two digits of the field RTN, are not 00, basically are not those in txt1.

Appreciate any help with this query criteria. Thank you

 

[Gasman]

WHERE Left(RTN,2) <> "00" ?

or

WHERE Left(RTN,2) <> Forms!FormName!txt1

where FormName is the name of your form.

HTH

 

[JPR]

Thank you. Could you please where I should put the code below in my query? Is it in the Field or criteria line?

 

[jdraw]

Criteria line but you should test it yourself.
If you are using Gasman's sample code with the form control, then that form must be open when you run the query.

 

[arnelgp]

place it here

 

[Pat Hartman]

Before you go much further, I would suggest separating country code from the bank number. It will eliminate the need to use functions in the query to parse out the country code because that will prevent Access from optimizing the query. Forcing Access to do a full table scan to find records won't be time consuming until you get more than a few thousand records but using best practices is always advised because then you don't have to worry if your app gets too successful for itself.

 

[JPR]

Done. Thank you very much for your help.

 

[Pat Hartman]

You're welcome.