filter a query

A

Amateur

I have a query where all data from ALL account numbers are shown.
my Acountnumbers have 13 digits (like 43001000-8000). The first 8 digits can
be changed but that is irrelevant for my question (I think).
I would like that I only see record groups filtered on the last 4 digits.
For example I would like to see all records for the account group 8000 that
means 8000,8001,8002 etc. and as well for the account group 3000.
How can I set my criteria to see only the group 8### and 3### in the same
query?
Thanks
Klaus
 
A

Allen Browne

When you open your table in design view, is AccountNumber a Text field or a
Number field?

If text, enter this in the Field row:
Right([AccountNumber],4)
and group on that.

If Number, try:
[AccountNumber] Mod 10000
 
A

Amateur

Hi Allen
In the table the account number is a "text" field"
But I do not understand what you ment. I wanted to filter a query so that
the AccountNumber field is only showing ########-8### AND ########-3###
accountnumber groups (all the rest I dont want to see).
Where do I have to enter your: Right([AccountNumber],4) - do I have to write
this in the Criteria?
And how can I ask than only to show 3### and 8### numbers?
Thanks for your help.
Klaus

Allen Browne said:
When you open your table in design view, is AccountNumber a Text field or a
Number field?

If text, enter this in the Field row:
Right([AccountNumber],4)
and group on that.

If Number, try:
[AccountNumber] Mod 10000

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Amateur said:
I have a query where all data from ALL account numbers are shown.
my Acountnumbers have 13 digits (like 43001000-8000). The first 8 digits
can
be changed but that is irrelevant for my question (I think).
I would like that I only see record groups filtered on the last 4 digits.
For example I would like to see all records for the account group 8000
that
means 8000,8001,8002 etc. and as well for the account group 3000.
How can I set my criteria to see only the group 8### and 3### in the same
query?
Thanks
Klaus
 
C

CyberDwarf

Klaus

try adding this to the 'WHERE' clause:-

CAST(Right(AcctNummer, 4) AS Int) BETWEEN 8000 AND 8999 OR
CAST(Right(AcctNummer, 4) AS Int) BETWEEN 3000 AND 3999

This sort of query can be easily constructed dynamically in code, using the
DoCmd.RunSQL command in VBA, as follows:-

Dim InSql As String
InSql = "CAST(Right(AcctNummer, 4) AS Int) BETWEEN 8000 AND 8999 OR
CAST(Right(AcctNummer, 4) AS Int) BETWEEN 3000 AND 3999"
DoCmd.RunSQL OriginalQuery & " " & InSql

If you are using your query as the data source for a form or report, you can
set the data source of your form/report to the 'OriginalQuery & " " & InSql'
variable and do Me.ReQuery.

HTH

Steve
 
A

Allen Browne

Okay: you just want to filter to see the 8000s or 3000s.

Try this in the Criteria row under your field:
Like "*8###" Or Like "*3###"

(Don't change the # characters.)
 
A

Amateur

Hi Allen

You wrote: "Okay: you just want to filter to see the 8000s or 3000s." - not
8000s or 3000s - 8000s AND 3000s

Should your criteria works as well for AND - because I only see either/or?
Thanks
Klaus
 
A

Allen Browne

AND is irrelevant.

If a record has an 8 in the 4th place from the right, then it does not have
a 3 in that position. There is no record that has both an 8 in that position
and also a 3 in that position at the same time. Therefore if you use AND in
the Critiera row, you will get no records returned.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top