Field Size Dilemma

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

I have a code below that distributes Invoices to there rightful owners BUT
if I have more than 80 Characters in tblOwnerInfo.OwnerAddress which is set
to Memo field , The error I get is " The field is to small to accept the
amount of data you attempt to add,try inserting and pasting less"

then on my debug I am getting the yellow line on this line below , shown by
the xxxxxxxxxxxxxxx Error On Line Below xxxxxxxxxxxxxxxx

Trouble is that it crashes my batch distribute and causes a major problem
just because one owner address field has more than 8o Characters

Private Sub subSetInvoiceValues()
Dim recTmpOwner As New ADODB.Recordset, strTmp As String
recTmpOwner.Open "SELECT CompanyID FROM tblCompanyInfo WHERE CompanyName
LIKE '" _
& tbCompanyName.value & "'", cnnStableAccount, adOpenDynamic,
adLockOptimistic

lngInvoiceID = NextInvoiceID

With recInvoice
Dim recHorseOwners As New ADODB.Recordset, dblOwnerPercentAmount As
Double
Dim dblTotal As Double, dblGSTContentsValue As Double

recHorseOwners.Open "SELECT OwnerID,OwnerPercent FROM
tblHorseDetails" _
& " WHERE HorseID=" _
& val(tbHorseID.value) & " AND OwnerID > 0 AND Invoicing = False
ORDER BY OwnerID ", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

If recHorseOwners.EOF = True And recHorseOwners.BOF = True Then
recHorseOwners.Close
Set recHorseOwners = Nothing
MsgBox "This Horse Has No Owner At ALL.", vbApplicationModal +
vbOKOnly + vbInformation
.Fields("CompanyID") = Nz(recTmpOwner.Fields("CompanyID"), 0)
.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = val(tbHorseID.value)
.Fields("HorseName") = tbHorseName1.value
.Fields("FatherName") = tbFatherName.value
.Fields("MotherName") = tbMotherName.value

If tbDOB.value = "" Or IsNull(tbDOB.value) Then
Else
.Fields("DateOfBirth") = Format(CDate(tbDOB.value),
"mm/dd/yyyy")

.Fields("HorseDetailInfo") = tbFatherName.value & "--" &
tbMotherName.value & "--" & funCalcAge(Format(tbDOB.value, "dd-mmm-yyyy"),
Format("01-Aug-" & Year(Now()), "dd-mmm-yyyy"), 1) & "-" & tbSex.value
End If

.Fields("Sex") = tbSex.value
.Fields("GSTOptionsText") = tbGSTOptions.value
.Fields("GSTOptionsValue") = tbGSTOptionsValue.value
.Fields("SubTotal") = tbSubTotal.value
.Fields("TotalAmount") = tbTotalAmount.value

If tbInvoiceDate.value = "" Or IsNull(tbInvoiceDate.value) Then
Else
.Fields("InvoiceDate") = Format(tbInvoiceDate.value,
"dd/mm/yyyy")
End If

Exit Sub
End If
recHorseOwners.MoveFirst
Dim nloop As Long
Do Until recHorseOwners.EOF = True


Dim recOwnersInfo As New ADODB.Recordset



recOwnersInfo.Open "SELECT
OwnerID,IIf(isnull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & '
') & " _
&
"IIf(isnull(tblOwnerInfo.OwnerLastName),'',trim(Left(tblOwnerInfo.OwnerLastName,21))
& ', ') & " _
&
"IIf(isnull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName) AS
Name, " _
& "OwnerAddress FROM tblOwnerInfo WHERE OwnerID=" &
val(recHorseOwners.Fields("OwnerID")), _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic


If recOwnersInfo.EOF = True And recOwnersInfo.BOF = True
Then
recOwnersInfo.Close
Set recOwnersInfo = Nothing
Else

dblTotal = IIf(tbTotalAmount.value = "" Or
IsNull(tbTotalAmount.value), 0, val(tbTotalAmount.value))
dblOwnerPercentAmount =
IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0, dblTotal *
recHorseOwners.Fields("OwnerPercent"))

.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")

.Fields("OwnerName") = Nz(recOwnersInfo.Fields("Name"),
"")
'xxxxxxxxxxxxxxxxxxxxxxxxxxxx ERROR ON LINE BELOW
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
.Fields("OwnerAddress") =
recOwnersInfo.Fields("OwnerAddress")
.Fields("OwnerPercent") =
IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0,
recHorseOwners.Fields("OwnerPercent"))
.Fields("OwnerPercentAmount") = dblOwnerPercentAmount


dblGSTContentsValue = (dblOwnerPercentAmount / 9)
.Fields("GSTContentsValue") = dblGSTContentsValue

If dblGSTContentsValue > 0 Then
.Fields("GSTContentsText") = "Tax Contents"
ElseIf dblGSTContentsValue < 0 Then
.Fields("GSTContentsText") = "Credit"
Else
.Fields("GSTContentsText") = ""
End If
End If
recOwnersInfo.Close
Set recOwnersInfo = Nothing

If chkByCheque.value = True Then
recInvoice.Fields("InvoiceNo") = NextInvoiceNo
Else
recInvoice.Fields("InvoiceNo") = 0
End If


.Fields("CompanyID") = Nz(recTmpOwner.Fields("CompanyID"),
0)
.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = val(tbHorseID.value)
.Fields("HorseName") = tbHorseName1.value
.Fields("FatherName") = tbFatherName.value
.Fields("MotherName") = tbMotherName.value

If tbDOB.value = "" Or IsNull(tbDOB.value) Then
Else
.Fields("DateOfBirth") = Format(CDate(tbDOB.value),
"mm/dd/yyyy")

.Fields("HorseDetailInfo") = tbFatherName.value & " -- "
& tbMotherName.value & " -- " & funCalcAge(Format(tbDOB.value,
"dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()), "dd-mmm-yyyy"), 1) & " -- "
& tbSex.value
End If

.Fields("Sex") = tbSex.value
.Fields("GSTOptionsText") = tbGSTOptions.value
.Fields("GSTOptionsValue") = tbGSTOptionsValue.value
.Fields("SubTotal") = tbSubTotal.value
.Fields("TotalAmount") = tbTotalAmount.value

If tbInvoiceDate.value = "" Or IsNull(tbInvoiceDate.value)
Then
Else
.Fields("InvoiceDate") = Format(tbInvoiceDate.value,
"dd/mm/yyyy")
End If

strTmp = " tblInvoice.InvoiceID=" & lngInvoiceID

subSetInvoiceDetailsValues

recHorseOwners.MoveNext
If recHorseOwners.EOF = False Then
.AddNew
lngInvoiceID = NextInvoiceID
strExpressionOrgument = strExpressionOrgument & strTmp &
" OR "
Else
strExpressionOrgument = strExpressionOrgument & strTmp
End If

Loop
End With
recHorseOwners.Close
Set recHorseOwners = Nothing
Set recTmpOwner = Nothing
End Sub
 
The implication is that the OwnerAddress field in whatever table provides
that field to recInvoice can handle a maximum of 80 characters. If you don't
want to change that field, trying changing the offending line of code to:

.Fields("OwnerAddress") = Left(recOwnersInfo.Fields("OwnerAddress"), 80)
 
Brilliant Douglas it worked with the 80, But what does the the 80 mean, how
many characters can I have?
Thanks Bob
 
I put 80 there because you said you ran into the problem when you had more
than 80 characters. As a result, I'm trimming the address so that only the
first 80 characters are used if there are more. If your field can hold more
than 80 characters, change the number.
 
Thanks Douglas, I will test to see the max amount of data I can have in my
Address Box, BTW gaps are not counted are they?
Regards Bob
 
Thanks Douglas, I will test to see the max amount of data I can have in my
Address Box, BTW gaps are not counted are they?
Click to expand...

The limiting factor is not the textbox on the form; it's the field definition
in the table. Open the table in design view; select this address field; and
look at its size in the field properties on the lower left of the screen. A
Text datatype field will default to 50 bytes, has a maximum of 255, and can be
set anywhere from 1 to 255. I'm guessing that when your table was created it
was set to 80 - hence the error message at 80 characters.
 
Thanks John, the field is set to Memo
I tested my Address field , filled it up to about 300 characters then used
this formula
.Fields("OwnerAddress") = Left(recOwnersInfo.Fields("OwnerAddress"), 100)
and the 100 was the highest it would let me set it befoe erroring
Regadrs Bob
 
Thanks John, the field is set to Memo
Click to expand...

Why would you EVER need more than 255 characters for a mailing address? A Memo
gives you up to two BILLION characters: that's not an address, it's not even a
novel, it's a freaking roomfull of encyclopedias!
I tested my Address field , filled it up to about 300 characters then used
this formula
.Fields("OwnerAddress") = Left(recOwnersInfo.Fields("OwnerAddress"), 100)
and the 100 was the highest it would let me set it befoe erroring
Click to expand...

So you're copying the leftmost 100 (or 300 or however many you think you need)
characters of OwnerAddress into... OwnerAddress? Are these two different
tables? Is the field Memo type in both tables? Why not just copy the whole
field?

It sounds like you're doing something which should be pretty simple in the
most complex way imaginable...
 
Hehe :) your dead right John the table that it was going into was a text
field, so was going from a Memo to a text field, but the text field was set
at 255, tested Memo field to Memo field alls good, using a memo field dosent
use any more space???
Thanks Bob
 
Hehe :) your dead right John the table that it was going into was a text
field, so was going from a Memo to a text field
Click to expand...

That would, of course, cause an error if the text being copied exceeded the
size of the text field.
but the text field was set
at 255,
Click to expand...

And you got an error at 100? That's odd.
tested Memo field to Memo field alls good, using a memo field dosent
use any more space???
Click to expand...

A little more. Memo fields aren't stored in the table record; the record in
the table contains an 18-byte "chunk" containing a pointer to the (separately
stored) memo text, its size, and maybe other stuff (Microsoft isn't talking).
A Text field stores only the text you enter (trailing blanks are removed),
plus 2 bytes overhead.

Memo fields have disadvantages, though. They cannot be indexed; they can be
searched but the search will be slower than a text field, and MUCH slower than
an indexed text field. Sorting on a memo field will sort only on the first 255
bytes and will always be significantly slower than sorting a text field. In my
experience they are also more vulnerable to corruption. I wouldn't use a memo
field unless I had a real, valid business need to store more than 255
characters in one field; this is not unusual (e.g. in an animal shelter
database I use a Memo field for the veterinarian's comments about the animal;
it's USUALLY not needed but it is needed often enough!)
 
In addition to what John said... if I have to use a Memo field it is in its
own table so if corruption does appear, and in my experience it does, I only
have to worry about one table with some memo fields and not critical data.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Bob Vance said:
Hehe :) your dead right John the table that it was going
into was a text field, so was going from a Memo to a
text field, but the text field was set at 255, tested Memo
field to Memo field alls good, using a memo field dosent
use any more space???
Click to expand...

I got here a little late, so don't know specifically what you are doing --
but Fields are components of either Tables or Queries, and transferring data
from one Field to another tends to raise a red flag that you may have
defined your tables in a non-normalized manner and have redundant data (the
way things are often done in a spreadsheet). It is not necessarily an error,
but does catch our attention.

Many people, however, refer to the Controls in a Form or Report as "fields"
or to VBA variables as "fields".

In any case, Access with Jet or ACE physically stores both Text and Memo
fields as variable-length; thus, a Memo field with length of 255 characters
or less will, in fact, take exactly the same physical space as a Text field
of the same length. A length of less than 255 in the definition of either a
Text or Memo represents the maximum length allowed.

Larry Linson
Microsoft Office Access MVP
 
Back
Top