Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Query error code

Posted on 2013-11-15
18
Medium Priority
?
241 Views
Last Modified: 2014-01-03
I created a small DB to program, test and then import into an existing DB.  The below query works fine in a small DB, but gives an error when imported into existing DB. See attached

Here is the query syntax that works fine in small DB  
StatusNew: IIf([FACaseNo]+[TANFCaseNo]+[FosterChild]<>0,"F",IIf([NonNeedy] Is Not Null,"N",Nz(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & [IEChild]![HHInc ome] & ""),"N")))

Can you tell me why this is occurring?Error message when running queryI created a small DB to program, test and then import into an existing DB.  The below query works fine in a small DB, but gives an error when imported into existing DB. See attached

Here is the query syntax that works fine in small DB  
StatusNew: IIf([FACaseNo]+[TANFCaseNo]+[FosterChild]<>0,"F",IIf([NonNeedy] Is Not Null,"N",Nz(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & [IEChild]![HHInc ome] & ""),"N")))

Can you tell me why this is occurring?
0
Comment
Question by:softsupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
18 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39651852
perhaps a null value in field

[IEChild]![HHFrequency]
0
 

Author Comment

by:softsupport
ID: 39651982
Do you mean like this?  Nz([IEChild]![HHFrequency])
That did not work.  Did I do that correctly?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39652007
what is the field "[Frequency]" data type ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:softsupport
ID: 39652053
text
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39652226
Can you post a sample db? Sanitized records? No records if this is not possible.
0
 

Author Comment

by:softsupport
ID: 39652329
Do you mean post a sample of the smaller DB?  As stated, the smaller DB works fine, it is when imported into the existing larger DB, I receive errors.  Would this import cause an issue?  Larger DB is just that.... large and not able to post.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39652366
there could be a corrupted record in the big db that is causing the error.

can you post a sample db with the table "IEChild"
0
 

Author Comment

by:softsupport
ID: 39652494
I will post.  Should not be corrpt record as it is not connected to any tables and has no data.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39652675
From the error message you posted, it looks like you have nulls in BOTH HHFrequency and HHIncome.  You shouldn't need NZ around HHFrequency (the single quote text delimiters are automatically handling nulls), but HHIncome might be a problem.

Try this:

StatusNew: IIf([FACaseNo]+[TANFCaseNo]+[FosterChild]<>0,"F",IIf([NonNeedy] Is Not Null,"N",Nz(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & NZ([IEChild]![HHInc ome], 0),"N")))

Open in new window


Also, look at the field name [IEChild]![HHInc ome].  You have a space in Income that looks out of place.  Should the field name be [IEChild]![HHIncome] (without the space)?

StatusNew: IIf([FACaseNo]+[TANFCaseNo]+[FosterChild]<>0,"F",IIf([NonNeedy] Is Not Null,"N",Nz(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & NZ([IEChild]![HHIncome], 0),"N")))

Open in new window

0
 

Author Comment

by:softsupport
ID: 39660632
When I run the IEChild Form is where the error occurs in the existing database.  The IEstatus query is generating the error. Syntax error above occurs sporadically as stand alone DB, but occurs all the time when I import into an existing database.
App.accdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39661164
try to find the record that is raising the error, it could be a corrupted record
from any of the 4 tables in query IEStatus.

you can run this codes (place in a regular module) and probably catch the corrupted record..

change "NameOfTable" with the 4 tables in the query for each run.


sub checkRecords()
on error goto rs_error
dim rs as dao.recordset, j as long, k as integer
set rs=currentdb.openrecordset("NameOfTable")

if rs.eof then
   msgbox "No records"  
   exit sub
end if

rs.movefirst
j=1
do until rs.eof
     for k=0 to rs.fields.count-1
        debug.print j & " - " & rs(k).name  & " - " & rs(k)
     next
j=j+1
rs.movenext
loop

rs.close

 rs_error:
          msgbox err.number & vbcrlf & err.description & vbcrlf & "Record No :" & j

end sub
0
 

Author Comment

by:softsupport
ID: 39661364
Ran module above as instructed, in query where error occurs....  No errors or corrupt data found in any tables. UGH!  Was hoping this would find something....

In FrmIEChild, when I enter HHSize, HIncome, Frequency and click on button "Qualify",  I now receiving a compile error.  Attached...Compile error, and this message follows.Message after compile error
Can this be the culprit?

After I exit the error in VBA, and press button "Qualify" again, I receive the following message listed above.  Original Error
Would the first error trigger the last one?
0
 

Author Comment

by:softsupport
ID: 39671653
Any suggestions???!!!
0
 
LVL 58
ID: 39696933
Run a query against [IEChild]![HHFrequency] and see if it has any quotes or apostrophe's in it.

In a new query, define two columns:

Expr1: InStr([HHFrequency],"'")
Expr2: InStr([HHFrequency],"""")

 and check for a value of > 0

Jim.
0
 
LVL 85
ID: 39696987
I don't know that you'll ever troubleshoot something like this, with the queries in the state they are. You've got DLookups in aliased columns, and that's just a recipe for disaster. For example, in the IEStatus query you have this:

Status: Nz(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & [IEChild]![HHIncome] & ""),"N")

You really should add in the IEG table a second time and join it to IEChild table, or create a query that provides that data and add that query to the IEStatus query, something like:

SELECT IEChild.ChildIE, IEChild.HHIncome, IEG.IncomeType
FROM IEG INNER JOIN IEChild ON (IEG.Frequency = IEChild.HHFrequency) AND (IEG.HHSize = IEChild.HHSize)
WHERE (((IEChild.HHIncome)>[IEChild].[HHIncome]));

Save that query as "qIEChildIncomeType", and then add that query to the IEStatus query, and join it on CqIEChildIncomeType.ChildID=IEChild.ChildIE. This should give you the IncomeType for each Child in that initial query.

I'm not sure exactly what the database is used for, but if you could give us some insight into that we might be able to suggest a more efficient design. Anytime you see queries that must rely on IIFs, DLookups, DCounts, etc the underlying design is always suspect.

=======

Regarding your errors:

The "User Defined Type" error means either (a) you do not have a reference to the DAO library or (b) you DO have a reference, but there is trouble with the database. I downloaded the db you attached on 11-19, and the reference is okay in that one, and I don't get any compile errors. That doesn't mean your copy is okay on your machine, however, so first thing to do is perform maintenance:

First, make a backup.

Now do this:

1. Compact the database
2. Compile the database - from the VBA Editor click Debug - Compile. Fix any errors, and continue doing this until the Compile menuitem is disabled.
3. Compact again

Now Decompile it - Build a Shortcut with this as the Target:

"full path to msaccess.exe" "full path to your db" /decompile

Run this shortcut, then do the 3 steps above again.

Also, be sure that your machine is fully updated regarding Office and Windows.
0
 

Author Comment

by:softsupport
ID: 39701638
Thank you for your input and suggestions.  The DLookup were previous suggestions from other experts from EE.  The purpose of the DB is to enter a household size, income and frequency to establish where the family lies within the IEG table. The family may have multiple incomes and frequencies therefore the income is annualized for determination.  If they do not qualify within the table then they qualify as N.  The data is entered on the IEChild Table and determined by pressing Qualify.  Hope this gives a little more insight on purpose.

There is a reference to the DAO library in the HHI subform... I will perform maintenance you suggested.
0
 

Author Comment

by:softsupport
ID: 39701810
I have performed the maintenance as you suggested without any errors.  When I import all items into a larger database.... I get the DAO library error.  The imported DB was designed in 2007 while the larger database in 2003. Can that be the issue and if so how do I correct?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39701934
<The imported DB was designed in 2007 while the larger database in 2003.>

wished you mentioned this at your original post..

from the larger db
check your references
from VBA window

Tools > references
look for reference prefix with "MISSING:"

uncheck, then locate the available version of the reference

click OK

Do a DEBUG > Compile
make sure there are no more error raised
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question