MS Access Between Query Statement Question

[twwayne]

I have two tables that I want to use to use a between query statement for.

The first table and example data:

Sequence =1
beginaccount = 100001
endaccount = 110160

The second table and sample data:

Account = 100001, 100002

I want to write a statement that says if the account is between the beginaccount and endaccount to return the applicable sequence number.

My query statement is:

ReportSequence: IIf([account] Between [beginaccount] And [endaccount],[sequence])

When I write this statement it only returns account 100001, but I want it to return accounts 100001 and 100002.

Any help would be appreciated. Thanks!

 

[HandsOnAccess]

Wonder how the above statement can return Account 100001 When it is intended to return Sequence!

First you say you want to write a statement that returns the applicable sequence number.

Next line you say you want the query statement to return accounts 100001 and 100002.

Am I reading you wrongly?

 

[twwayne]

Sorry, I meant that I want it to return accounts 100001 and 100002 as part of the recordset, with the new ReportSequence field being 1.