HELP! Can't qualify data!!

[Guest]

I use 2 Access databases for reporting for my company. These databases
contain tables, queries, forms, and reports. I am randomly experiencing a
problem with each of the databses. Data which should be filtered out at the
query level is not being removed and is getting through to the report. For
example, I have a report that should only show data for account numbers
greater than 60000000, so in the query that is tied to the report, I have a
qualifier on account number: >60000000. Until yesterday, this qualifier had
worked perfectly. Today, there are account numbers less than 60000000
showing up on my report. I use the same qualifier to run reports for other
states and these are reporting without any errors. I can't identify what the
cause of the problem might be. This exact report ran correctly yesterday.
It is very unpredictable, but all of the errors seem to result from Access
not reading the qualifiers in my queries correctly. Any suggestions? Thanks!

 

[Lynn Trapp]

Sara, can you post the SQL for your query? Also, what datatype is your
[account number] field?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

How do I find and post the SQL? I'm unfamiliar with SQL.

My account number field is formatted as a number.

Sara, can you post the SQL for your query? Also, what datatype is your
[account number] field?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

I use 2 Access databases for reporting for my company. These databases
contain tables, queries, forms, and reports. I am randomly experiencing a
problem with each of the databses. Data which should be filtered out at
the
query level is not being removed and is getting through to the report.
For
example, I have a report that should only show data for account numbers
greater than 60000000, so in the query that is tied to the report, I have
a
qualifier on account number: >60000000. Until yesterday, this qualifier
had
worked perfectly. Today, there are account numbers less than 60000000
showing up on my report. I use the same qualifier to run reports for
other
states and these are reporting without any errors. I can't identify what
the
cause of the problem might be. This exact report ran correctly yesterday.
It is very unpredictable, but all of the errors seem to result from Access
not reading the qualifiers in my queries correctly. Any suggestions?
Thanks!

 

[Guest]

If your account field is a text field then 90000 is greater than 60000000.
It compares the left most characters.

 

[Guest]

Open the query in design view and click on menu VIEW - SQL View. Copy and
paste to the newsgroup.

How do I find and post the SQL? I'm unfamiliar with SQL.

My account number field is formatted as a number.

Sara, can you post the SQL for your query? Also, what datatype is your
[account number] field?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

I use 2 Access databases for reporting for my company. These databases
contain tables, queries, forms, and reports. I am randomly experiencing a
problem with each of the databses. Data which should be filtered out at
the
query level is not being removed and is getting through to the report.
For
example, I have a report that should only show data for account numbers
greater than 60000000, so in the query that is tied to the report, I have
a
qualifier on account number: >60000000. Until yesterday, this qualifier
had
worked perfectly. Today, there are account numbers less than 60000000
showing up on my report. I use the same qualifier to run reports for
other
states and these are reporting without any errors. I can't identify what
the
cause of the problem might be. This exact report ran correctly yesterday.
It is very unpredictable, but all of the errors seem to result from Access
not reading the qualifiers in my queries correctly. Any suggestions?
Thanks!

 

[Guest]

Thanks - The account numbers I need are actually any greater than 609999999.

SELECT [Q - Oregon for Form].ID, [Q - Oregon for Form].[Br Nbr], [Q - Oregon
for Form].[Acct Nm], [Q - Oregon for Form].[Acct Nbr], [Q - Oregon for
Form].[Orig Amt], [Q - Oregon for Form].[Curr Bal Amt], [Q - Oregon for
Form].[Create Dt], [Q - Oregon for Form].[Cred Life Ins Comp Nbr], [Q -
Oregon for Form].[*Cred Life Accr Amt], [Q - Oregon for Form].[Cred Life Accr
Amt], ([*Cred Life Accr Amt]*0.35) AS [Life Income], [Q - Oregon for
Form].[Accid Hlth Comp Cd], [Q - Oregon for Form].[*Accid Health Amt], [Q -
Oregon for Form].[Accid Health Amt], ([*Accid Health Amt]*0.35) AS [A&&H
Income], [Q - Oregon for Form].[Ofcr Cd], [Q - Oregon for Form].[Ins Disb
Cd], [Q - Oregon for Form].Comments, [Q - Oregon for Form].[Clear GL], [Q -
Oregon for Form].[Payoff Date], [Q - Oregon for Form].[Refund Process Date],
[Q - Oregon for Form].[Life Refund], [Q - Oregon for Form].[A&H Refund]
FROM [Q - Oregon for Form]
WHERE ((([Q - Oregon for Form].[Acct Nbr])>609999999) AND (([Q - Oregon for
Form].[Create Dt]) Between [Beginning Date] And [Ending Date]) AND (([Q -
Oregon for Form].[Clear GL])<>"-1")) OR ((([Q - Oregon for Form].[Clear GL])
Is Null));

Open the query in design view and click on menu VIEW - SQL View. Copy and
paste to the newsgroup.

How do I find and post the SQL? I'm unfamiliar with SQL.

My account number field is formatted as a number.

Sara, can you post the SQL for your query? Also, what datatype is your
[account number] field?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



I use 2 Access databases for reporting for my company. These databases
contain tables, queries, forms, and reports. I am randomly experiencing a
problem with each of the databses. Data which should be filtered out at
the
query level is not being removed and is getting through to the report.
For
example, I have a report that should only show data for account numbers
greater than 60000000, so in the query that is tied to the report, I have
a
qualifier on account number: >60000000. Until yesterday, this qualifier
had
worked perfectly. Today, there are account numbers less than 60000000
showing up on my report. I use the same qualifier to run reports for
other
states and these are reporting without any errors. I can't identify what
the
cause of the problem might be. This exact report ran correctly yesterday.
It is very unpredictable, but all of the errors seem to result from Access
not reading the qualifiers in my queries correctly. Any suggestions?
Thanks!

 

[Van T. Dinh]

If the Field [Clear GL] is Null, the Record will be selected regardless of
the account number according to the criteria you posted.

 

[Guest]

If I move the "Or Is Null" qualifier up to the criteria line of the "Clear
GL" field (<>"-1" Or Is Null) instead of having it in the Or line of that
field, should that solve my problem? It's important that I be able to pull
all records in which the value of the field is blank IF the account number
also fits my criteria.

If the Field [Clear GL] is Null, the Record will be selected regardless of
the account number according to the criteria you posted.

--
HTH
Van T. Dinh
MVP (Access)

Thanks - The account numbers I need are actually any greater than
609999999.

SELECT [Q - Oregon for Form].ID, [Q - Oregon for Form].[Br Nbr], [Q -
Oregon
for Form].[Acct Nm], [Q - Oregon for Form].[Acct Nbr], [Q - Oregon for
Form].[Orig Amt], [Q - Oregon for Form].[Curr Bal Amt], [Q - Oregon for
Form].[Create Dt], [Q - Oregon for Form].[Cred Life Ins Comp Nbr], [Q -
Oregon for Form].[*Cred Life Accr Amt], [Q - Oregon for Form].[Cred Life
Accr
Amt], ([*Cred Life Accr Amt]*0.35) AS [Life Income], [Q - Oregon for
Form].[Accid Hlth Comp Cd], [Q - Oregon for Form].[*Accid Health Amt],
[Q -
Oregon for Form].[Accid Health Amt], ([*Accid Health Amt]*0.35) AS [A&&H
Income], [Q - Oregon for Form].[Ofcr Cd], [Q - Oregon for Form].[Ins Disb
Cd], [Q - Oregon for Form].Comments, [Q - Oregon for Form].[Clear GL],
[Q -
Oregon for Form].[Payoff Date], [Q - Oregon for Form].[Refund Process
Date],
[Q - Oregon for Form].[Life Refund], [Q - Oregon for Form].[A&H Refund]
FROM [Q - Oregon for Form]
WHERE ((([Q - Oregon for Form].[Acct Nbr])>609999999) AND (([Q - Oregon
for
Form].[Create Dt]) Between [Beginning Date] And [Ending Date]) AND (([Q -
Oregon for Form].[Clear GL])<>"-1")) OR ((([Q - Oregon for Form].[Clear
GL])
Is Null));

 

[Van T. Dinh]

Yes, I think so.

I tend to work with SQL String a bit rather than the Query Grid. In SQL
View, it should be

....
WHERE ([Q - Oregon for Form].[Acct Nbr] > 609999999)
AND ([Q - Oregon for Form].[Create Dt]
Between [Beginning Date] And [Ending Date])
AND ( ([Q - Oregon for Form].[Clear GL] <> "-1")
OR ([Q - Oregon for Form].[Clear GL]) Is Null) );

I left the double-quotes around -1 but if it is a Boolean Field, I think you
should remove the double-quotes.