UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Write > 255 Characters Via VBA To Long Text Field?, Access 2016    
 
   
jsurpless
post Dec 11 2017, 09:38 AM
Post#1



Posts: 459
Joined: 21-December 03



Hi everyone

I am trying to figure out how to have my VBA function return a string that is longer than 255 characters.

I am aware of the 'Long Text' data type and have confirmed that it can hold the string that my function would produce.

Here is my function

CODE
Public Function Determine_CustomerLoopTypical(strLoopName As String)

'*************************************************************
'****       Determine_CustomerLoopTypical
'*************************************************************
'!  Purpose:        Determines Customer Loop Typical for the specified LoopName
'!  Caller:         None
'!  Input :         LoopName
'!  Output:         None
'!
'!************************************************************
'!  REVISION HISTORY :
'!************************************************************
'!  REV   NAME           DATE        DESCRIPTION
'!======  ========       =======     =====================
'! 2.0    J.Surpless     MM/DD/YY    Initial Version
'*************************************************************

On Error GoTo Err_Determine_CustomerLoopTypical

'-----

Dim rstIODatabase_Function As DAO.Recordset

Set rstIODatabase_Function = CurrentDb.OpenRecordset("SELECT [REFNO], [TagName]" _
                                                        & " FROM [IO Database]" _
                                                        & " WHERE [LoopName] LIKE '" & strLoopName & "' AND (Nz([IO Database].[Deleted?]) <> ""Yes"")" _
                                                        & " ORDER BY [Function], [Suffix]", dbOpenSnapshot)
                                                                                                            
Do Until rstIODatabase_Function.EOF

    Determine_CustomerLoopTypical = Determine_CustomerLoopTypical & Determine_TagName_Type(rstIODatabase_Function.Fields("REFNO")) & ","
    
'    Debug.Print Determine_CustomerLoopTypical

    rstIODatabase_Function.MoveNext
    
Loop

'-----

If (Determine_CustomerLoopTypical <> "") Then

    'Trim trailing ',' resulting from LAST TagName_Type added ABOVE
    Determine_CustomerLoopTypical = Left(Determine_CustomerLoopTypical, Len(Determine_CustomerLoopTypical) - 1)

End If


My function seems to work properly as the 'Determine_CustomerLoopTypical' does print a string that is longer than 255 characters; however, my query that calls it somehow truncates it to 255 characters

CODE
INSERT INTO [Customer Loops] ( LoopName, LoopTypical )
SELECT DISTINCT LoopNames.LoopName, Determine_CustomerLoopTypical([LoopNames].[LoopName]) AS LoopTypical
FROM (SELECT DISTINCT [IO Database].[LoopName] FROM [IO Database] WHERE (Nz([IO Database].[LoopName]) <> "") AND (Nz([IO Database].[Deleted?]) <> "Yes"))  AS LoopNames
WHERE (((LoopNames.LoopName)="001-AB-5300"))
ORDER BY LoopNames.LoopName;


Any thoughts?

Thanks!
Go to the top of the page
 
RuralGuy
post Dec 11 2017, 10:02 AM
Post#2


UtterAccess VIP
Posts: 2,801
Joined: 25-June 05
From: @ 8300' in the Colorado Rocky Mountains


See if there is anything in this link that might apply: http://allenbrowne.com/ser-63.html

--------------------
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please reply to the forum so all may benefit.
Go to the top of the page
 
doctor9
post Dec 11 2017, 10:06 AM
Post#3


UtterAccess Editor
Posts: 18,033
Joined: 29-March 05
From: Wisconsin


jsurpless,

RuralGuy beat me to it. I've seen the link to this article in many past discussions along the lines of "why is my query truncating my memo field". Looks like the SELECT DISTINCT is the culprit here.

By the way, your function header doesn't define the function's datatype. You might want to change it to this:

CODE
Public Function Determine_CustomerLoopTypical(strLoopName As String) as String


Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
jsurpless
post Dec 11 2017, 04:26 PM
Post#4



Posts: 459
Joined: 21-December 03



Thanks for the information - it was the SELECT DISTINCT on the main query that was doing it...

Not even sure why I had it there because the subquery's DISTINCT would produce unique listing already

In any case, I got the query to return the full length but am now having a problem with the last query in my app

CODE
SELECT DISTINCT Loops.LoopTypical, Loops.NumberOfLoops
FROM [Customer Loops] INNER JOIN (SELECT [LoopTypical], Count([LoopName]) AS NumberOfLoops FROM [Customer Loops] GROUP BY [LoopTypical])  AS Loops ON [Customer Loops].[LoopTypical] = Loops.[LoopTypical]
WHERE ((([Customer Loops].[LoopName]) In ('001-AB-5300')))
ORDER BY Loops.LoopTypical;


Its goal is to list the NumberOfLoops for each unique LoopTypical; unfortunately, Access won't let me join on the LoopTypical field because it's a Long Text field...

Any thoughts on how I can get around that?
Go to the top of the page
 
doctor9
post Dec 12 2017, 09:18 AM
Post#5


UtterAccess Editor
Posts: 18,033
Joined: 29-March 05
From: Wisconsin


jsurpless,

> goal is to list the NumberOfLoops for each unique LoopTypical

I'm not familiar with your table structure, and I don't recognize these terms, but here's the basic concept...

Let's say you have two tables: the Bills and the Bill Line Items. The Bills table contains the date the bill was sent, the customer ID, the purchase order number, etc. The primary key field is "BillID". The Bill Line Items table contains the individual items that appear on the bill. The foreign key field that links to BillID is "lngBillID". To get a count of the Line Items for each unique Bill, you'd use a query along one of these lines:

SELECT *, DCount("*","tblBillLineItems","lngBillID=" & [BillID]) AS CountOfLineItems FROM tblBills;

SELECT tblBills.BillID, Count(tblBillDetails.BillDetailID) AS CountOfBillDetailID
FROM tblBills INNER JOIN tblBillDetails ON tblBills.BillID = tblBillDetails.lngBillID
GROUP BY tblBills.BillID;

I'm assuming your LoopTypical table has a primary key field and your Loops table has a foreign key field that links to it. Use the same strategy there.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
jsurpless
post Dec 22 2017, 03:45 PM
Post#6



Posts: 459
Joined: 21-December 03



Hi

Still struggling with this last part...

I am trying to calculate this 'LoopTypical' field, add it to a 'Loop Typicals' table and use VBA/SQL to assign its PrimaryKey to each 'LoopName' in the 'Customer Loops' table

I know that this is probably pretty odd but it's part of my requirement.

I am able to add NEW 'LoopTypical' values to my 'Loop Typicals' table but I am struggling to assign the PK value to Customer Loops

I can't use SQL to join on LoopTypical in each table via an UPDATE query because I can't join on LongText fields

So then I figured, well, I'll just use DAO Recordsets... unfortunately, the fields in DAO seem to be limited to 255 characters so that's out too...

Here's my code which works fine for LoopTypical values < 255 characters

CODE
'APPEND Customer Loops & Typicals To Customer Loops Temporary Table - Excluding NON-SPECIFIED LoopNames And DELETED Records
strSQL = "INSERT INTO [" & strCustomerLoops_TemporaryTable & "] ( LoopName, CustomerLoopTypical )" _
            & " SELECT [LoopNames].[LoopName], Determine_CustomerLoopTypical([LoopNames].[LoopName]) AS LoopTypical" _
                & " FROM (SELECT DISTINCT [IO Database].[LoopName] FROM [IO Database]" _
            & " WHERE (Nz([IO Database].[LoopName]) <> " & strEmptyStringInQuotes & ") AND (Nz([IO Database].[Deleted?]) <> ""Yes"")) AS LoopNames" _
            & " ORDER BY [LoopNames].[LoopName];"

CurrentDb.Execute strSQL

'APPEND Customer Loops from Customer Loops TEMPORARY Table to ACTUAL Customer Loops Table
strSQL = "INSERT INTO [Customer Loops] ( LoopName )" _
            & " SELECT [LoopName]" _
                & " FROM [" & strCustomerLoops_TemporaryTable & "]" _
            & " ORDER BY [LoopName];"

CurrentDb.Execute strSQL

'-----

'Process Customer Loop Typicals
Set rstCustomerLoops_CustomerLoopTypicals = CurrentDb.OpenRecordset("SELECT [CustomerLoopTypical] FROM [" & strCustomerLoops_TemporaryTable & "]" _
                                                    & " GROUP BY [CustomerLoopTypical]", dbOpenSnapshot)
                                
Do Until rstCustomerLoops_CustomerLoopTypicals.EOF

    Debug.Print rstCustomerLoops_CustomerLoopTypicals.Fields("CustomerLoopTypical")

'Check if CustomerLoopTypical is DEFINED already
    Set rstCustomerLoopTypicals = CurrentDb.OpenRecordset("SELECT [CustomerLoopTypicalID], [CustomerLoopTypical] FROM [Customer Loop Typicals]" _
                                                    & " WHERE [CustomerLoopTypical] IN ('" & rstCustomerLoops_CustomerLoopTypicals.Fields("CustomerLoopTypical") & "')", dbOpenDynaset)

'If not, ADD IT
    If (rstCustomerLoopTypicals.EOF) Then

        rstCustomerLoopTypicals.AddNew
        rstCustomerLoopTypicals.Fields("CustomerLoopTypical") = rstCustomerLoops_CustomerLoopTypicals.Fields("CustomerLoopTypical")
        rstCustomerLoopTypicals.Update

'Requery to get NEWLY ADDED PK value
        rstCustomerLoopTypicals.Requery

    End If

    '-----

    strSQL = "UPDATE [Customer Loops]" _
                & " INNER JOIN [" & strCustomerLoops_TemporaryTable & "] ON [Customer Loops].[LoopName] = [" & strCustomerLoops_TemporaryTable & "].[LoopName]" _
                & " SET [Customer Loops].[CustomerLoopTypicalID] = " & rstCustomerLoopTypicals.Fields("CustomerLoopTypicalID") _
                & " WHERE ((([" & strCustomerLoops_TemporaryTable & "].[CustomerLoopTypical]) IN ('" & rstCustomerLoops_CustomerLoopTypicals.Fields("CustomerLoopTypical") & "')));"

    CurrentDb.Execute strSQL
    
    '-----
    
    rstCustomerLoops_CustomerLoopTypicals.MoveNext
    
Loop
Go to the top of the page
 
jsurpless
post Jan 16 2018, 01:47 PM
Post#7



Posts: 459
Joined: 21-December 03



Hi

I've revised my table structure so that it's similar to what you described

Mostly got things to work but still seem to having one issue

My primary table has records grouped into a 'CustomerLoop' and have an individual unique tagname

A 'CustomerLoopTypical' is a recurring group of records that compose multiple 'CustomerLoops'

For instance,

001FIT1000A
001FV1000

001FIT1001A
001FV1001

These 'CustomerLoops' have a 'CustomerLoopTypical' of '*FIT*A,*FV*'

Some 'CustomerLoopTypicals' are VERY LONG, hence the need for the LongText field, as they have MANY records in them

Here is one such example

*II*AA,*II*AB,*II*BB,*MO*AAAL,*MO*AARK,*MO*AARN,*MO*AAT,*MO*AATA,*MO*AATB,*MO*AA
TC,*MO*AATD,*MO*ABAL,*MO*ABRK,*MO*ABRN,*MO*ABT,*MO*ABTA,*MO*ABTB,*MO*ABTC,*MO*ABT
D,*MO*BAAL,*MO*BARK,*MO*BARN,*MO*BAT,*MO*BATA,*MO*BATB,*MO*BATC,*MO*BATD,*MO*BBAL
,*MO*BBRK,*MO*BBRN,*MO*BBT,*MO*BBTA,*MO*BBTB,*MO*BBTC,*MO*BBTD,*XA*AA

I have figured out how to properly add this to the 'CustomerLoopTypicals' table but for some reason, this SQL doesn't return it

CODE
SELECT [CustomerLoopTypicalID], [CustomerLoopTypical] FROM [Customer Loop Typicals] WHERE [CustomerLoopTypical] = '*II*AA,*II*AB,*II*BB,*MO*AAAL,*MO*AARK,*MO*AARN,*MO*AAT,*MO*AATA,*MO*AATB,*M
O*AATC,*MO*AATD,*MO*ABAL,*MO*ABRK,*MO*ABRN,*MO*ABT,*MO*ABTA,*MO*ABTB,*MO*ABTC,*MO
*ABTD,*MO*BAAL,*MO*BARK,*MO*BARN,*MO*BAT,*MO*BATA,*MO*BATB,*MO*BATC,*MO*BATD,*MO*
BBAL,*MO*BBRK,*MO*BBRN,*MO*BBT,*MO*BBTA,*MO*BBTB,*MO*BBTC,*MO*BBTD,*XA*AA'


I am doing this in order to identify 'CustomerLoopTypicalID' so I can assign it to the 'CustomerLoop'

Any thoughts?

Thanks again!
Go to the top of the page
 
jsurpless
post Jan 17 2018, 07:26 AM
Post#8



Posts: 459
Joined: 21-December 03



It looks like my problem was that the Long Text field had been set to 'Indexed (No Duplicates)'

When i took that off, everything seems to work better

Thanks!
Go to the top of the page
 
RuralGuy
post Jan 17 2018, 09:42 AM
Post#9


UtterAccess VIP
Posts: 2,801
Joined: 25-June 05
From: @ 8300' in the Colorado Rocky Mountains


Thanks for the update and your solution.

--------------------
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please reply to the forum so all may benefit.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd January 2018 - 07:06 PM