?
Solved

Issue with Access Data in SQL Statement

Posted on 2011-03-11
12
Medium Priority
?
318 Views
Last Modified: 2012-05-11
Hi.  I have issue with SQL Statement

DoCmd.RunSQL "SELECT [Combined-Tax-Data].LoanID, Sumtax([LoanID]) AS TaxTotal, ConcatVar([LoanID]) AS Explanation INTO temp_deliquency FROM [Combined-Tax-Data] GROUP BY [Combined-Tax-Data].LoanID, Sumtax([LoanID]), ConcatVar([LoanID]);"

Data is truncated: If you see Attached Excel File I'm trying to combine all tax explanation.  Using above SQL statement Data is truncated: you may from the top to "$94.80, HEALTH/EXTR".  Data for $90.87, HEALTH/EXTR
$90.87, HEALTH/EXTR
$505.44, EM REPAIR/NEW
Not included into Combined value.

Purpose was to arrange everything from top as array:

Example:

Explanation
$739.74, RENT STAB;$411.50, RENT STAB;$514.37, RENT STAB;$328.81, RENT STAB;$411.01, RENT STAB;$431.56, RENT STAB;$464.80, EM REPAIR/NEW;$122.52, EM REPAIR/NEW;$141.20, INSP;$129.70, INSP;$94.80, HEALTH/EXTR;$94.80, HEALTH/EXTR;$94.80, HEALTH/EXTR;$94.80,"

Question: How can I change VBA Code or SQL Statement to get full data without truncation.

Current data type is text 255.  I tried to change data type to memo and append data to exising text field with data type "memo".  Not working

Please see file and code data.
Function ConcatVar(sVar As Variant) As String
Dim rs As DAO.Recordset, s As String

Set rs = CurrentDb.OpenRecordset("select * From [Combined-Tax-Data] Where [LoanID]='" & sVar & "'")

    rs.MoveFirst
 
    Do Until rs.EOF
      s = s & rs("[Tax_Explanation]") & ";"
      rs.MoveNext
    Loop

    ConcatVar = Left(s, Len(s) - 1)
End Function

Open in new window

Today-work.xls
0
Comment
Question by:maximyshka
  • 8
  • 4
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35111401
your information is getting truncated due to the Group By clause in your query.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35111430
use VBA codes and recordset to achieve this.
0
 

Author Comment

by:maximyshka
ID: 35111583
Thanks Capricorn.

However, how can I achieve it it using vba code:

Suggestion:

Set rs = CurrentDb.OpenRecordset("SELECT [Combined-Tax-Data].LoanID, Sumtax([LoanID]) AS TaxTotal, ConcatVar([LoanID]) AS Explanation INTO temp_deliquency
FROM [Combined-Tax-Data]
Where [LoanID]='" & sVar & "'"
GROUP BY [Combined-Tax-Data].LoanID, Sumtax([LoanID]), ConcatVar([LoanID]), [Combined-Tax-Data].LoanID)

I changed little query but it is not working.  Main purpose it to put data into array
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:maximyshka
ID: 35113501
Would you please help me to change SQL statement.  Part of vba code i put above with my changes which are not working
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35113712
upload a copy of the db..
0
 

Author Comment

by:maximyshka
ID: 35133988
Attached, you can find testdb.

See field Combined_Tax_Data.  Would you please combined all explanations for all fiedls Loan # 8009280299-200703.  All fields listed in combined explanation should equal sum listed in the function Sumtax([LoanID]).

Please let me know if you have any questions

TestDB.mdb
0
 

Author Comment

by:maximyshka
ID: 35134023
Additional information: please see frm_import.  Button: Update Input File: OnClick Event.  Code is listed there
0
 

Author Comment

by:maximyshka
ID: 35137857
Capricorn, did you see test database which I uploaded for you?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35138362
test this


TestDB.mdb
0
 

Author Comment

by:maximyshka
ID: 35139121
Would you please tell me how it is working

Thanks, instead of make table query:

DoCmd.RunSQL "SELECT [Combined-Tax-Data].LoanID, Sumtax([LoanID]) AS TaxTotal, ConcatVar([LoanID]) AS Explanation INTO temp_deliquency FROM [Combined-Tax-Data] GROUP BY [Combined-Tax-Data].LoanID, Sumtax([LoanID]), ConcatVar([LoanID]

You open recordset group by loanid
and then use following code:

 With rsTemp
        .AddNew
        !LoanID = rsLoanID!LoanID
        !TaxTotal = SumTax(rsLoanID!LoanID)
        !Explanation = ConcatVar(rsLoanID!LoanID)
        .Update
Would you please tell me how this is working?

You used 2 recordsets and looks like one inside another.  I'm little lost

Dim rsTemp As DAO.Recordset, rsLoanID As DAO.Recordset
Set rsTemp = CurrentDb.OpenRecordset("temp_delinquency")

Set rsLoanID = CurrentDb.OpenRecordset("select LoanId from [Combined-Tax-Data] group by LoanID") -- as I understand you open one recordset and put there unique loan id.
How ccde below is working?
With rsTemp
        .AddNew
        !LoanID = rsLoanID!LoanID
        !TaxTotal = SumTax(rsLoanID!LoanID)
        !Explanation = ConcatVar(rsLoanID!LoanID)
        .Update
0
 

Author Comment

by:maximyshka
ID: 35139291
Based on the debugging window: looks like you add new record, run functions which contantenate records and sum of total value.  All data from function you put into recordset !TaxTotal and !Explanation and With Update you update table.
Then you close both recordsets

2 questions: why it did not work with previous statement?
2nd If I need to group everything also by due date will this work with statement

CurrentDb.OpenRecordset("select LoanId from [Combined-Tax-Data] group by LoanID", DueDate?
0
 

Author Closing Comment

by:maximyshka
ID: 35141740
Capricorn, please answer last my questions. Understanding is important
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

568 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