filter a query

[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

 

[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

 

[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

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.

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

 

[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

 

[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.)

 

[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

 

[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.