Query SQL, the first line "Phytos.Adddeclar" is the one I am trying to Pad,
the rest are working fine.
SELECT Phytos.Adddeclar,
IIf([Treatment Date] Is Null,"* * * * * * * * * * * * * * *
* * * * * * * * * *",[Treatment Date]) AS TreatmentDate2,
IIf([Chemical] Is Null,"* * * * * * * * * * * * * * * * * *
* * * * * * *",[Chemical]) AS Chemical2, IIf([Concentration] Is Null,"*
* * * * * * * * * * * * * * * * * * * * * * *
*",[Concentration]) AS Concentration2, IIf([Treatment] Is Null,"* * * * *
* * * * * * * * * * * * * * * * * * * *",[Treatment]) AS
Treatment2, IIf([Duration & Temp] Is Null,"* * * * * * * * * * * *
* * * * * * * * * * * * *",[Duration & Temp]) AS [Duration &
Temp2], IIf([Add# Info#] Is Null,"* * * * * * * * * * * * * * *
* * * * * * * * * *",[Add# Info#]) AS [Add# Info#2], IIf([Produce
2] Is Null,"* * * * * * * * * * * * * * * * * * *
*",[Produce 2]) AS ProduceTwo, IIf([Produce 3] Is Null,"* * * * * * *
* * * * * * * * * * * * *",[Produce 3]) AS ProduceThree,
IIf([Produce 4] Is Null,"* * * * * * * * * * * * * * * * * *
* *",[Produce 4]) AS ProduceFour, IIf([Produce 5] Is Null,"* * * * * *
* * * * * * * * * * * * * *",[Produce 5]) AS ProduceFive,
IIf([Produce 6] Is Null,"* * * * * * * * * * * * * * * * * *
* *",[Produce 6]) AS ProduceSix, IIf([Unit 2] Is Null,"* * * * * * *
* * * *",[Unit 2]) AS UnitTwo, IIf([Unit 3] Is Null,"* * * * * * *
* * * *",[Unit 3]) AS UnitThree, IIf([Unit 4] Is Null,"* * * * * * *
* * * *",[Unit 4]) AS UnitFour, IIf([Unit 5] Is Null,"* * * * * * *
* * * *",[Unit 5]) AS UnitFive, IIf([Unit 6] Is Null,"* * * * * * *
* * * *",[Unit 6]) AS UnitSix, Phytos.PhytoNumber,
Phytos.[State/Federal], Phytos.Date, Phytos.[Inspection Date],
Phytos.[Destination country/state], Phytos.Company, Phytos.Address,
Phytos.City, Phytos.St, Phytos.ZIP, [City] & ", " & [St] & " " & [ZIP] AS
[City/State/Zip], Phytos.Consignee, Phytos.Addconsignee, Phytos.Moreaddress,
Phytos.Constatecountry, Phytos.[Produce 1], Phytos.Botname1, Phytos.[Quant
1], Phytos.Botname2, Phytos.[Quant 2], Phytos.Botname3, Phytos.[Quant 3],
Phytos.Botname4, Phytos.[Quant 4], Phytos.Botname5, Phytos.[Quant 5],
Phytos.Botname6, Phytos.[Quant 6], Phytos.Despack, Phytos.Dismarks,
Phytos.CoOrigin, Phytos.StOrigin1, Phytos.Conveyance, Phytos.[Port of entry],
Phytos.[Permit No], Phytos.[AD#], Phytos.Officer, Phytos.[Unit 1],
Phytos.Printed
FROM Phytos
WHERE (((Phytos.[State/Federal])="Federal Phyto") AND ((Phytos.Printed)=No))
ORDER BY Phytos.PhytoNumber;
Ken Sheridan said:
Post back here with the SQL of the query in which the function is called.
Ken Sheridan
Stafford, England
:
Tried it in *standard* module as PadMemo and PadText with the query changed
as needed througout. I still get an error on the query not recognixing
PadMemo or PadText.
The query was very close to what I needed. The module sounds good but, I
have not seen the result yet and to tell the truth I'm lost as to where in
the code it even looks for the Additional2 field. I can tell it does do it
though as the old query language does not work when that module is in place.
:
Make sure the function is in a *standard* module not a class module. A
standard module is the normal type you see listed in the modules page of the
database window, a class module is a module of a form, report or a class
you've created yourself (the last also show in the database window, but with
a different icon). Make sure the module has a different name to the
function; I usually prefix module names with 'bas' so you could call it
basTextStuff for instance and use it for any text manipulation functions you
create.
If it still doesn't work try renaming the function by opening the module and
doing a Replace from the Edit menu on the VBA menu bar, renaming it PadText
say (which is actually a better name as it can be used for any text, not just
memos). You'll need to change the name in the query too of course.
Ken Sheridan
Stafford, England
:
PadMemo Not recognized in query:
Additional2: PadMemo([Adddeclar],"*",900)
:
It would be simplest to do that with a function. Paste the following
function into a standard module:
Public Function PadMemo(varText As Variant, strPadChr As String, intPadTo As
Integer) As String
Dim strPad As String
Dim n As Integer
Dim intTextLength As Integer
intTextLength = Len(Nz(varText, ""))
For n = 1 To intPadTo \ 2
strPad = strPad & strPadChr & " "
Next n
If intTextLength <= intPadTo Then
If intTextLength > 0 Then varText = varText & vbNewLine
PadMemo = varText & Right$(strPad, intPadTo - intTextLength)
Else
PadMemo = varText
End If
End Function
And call it in the query like so:
Additional2: PadMemo([Adddeclar],"*",900)
If you wish you can change the length the text is padded to and the
character used by passing different values into the function as the second
and third arguments.
Ken Sheridan
Stafford, England
:
Thanks Ken, that's just about perfect.
Additional2: IIf(Len([Adddeclar] & "")>=900,[Adddeclar],[Adddeclar] &
String(900-Len([Adddeclar] & ""),"*"))
Does fill in the blank space in the memo with *.
I am getting the text broke up a little bit though. Example (not sure if
this will show properly):
"This is the test
example***********************************************...
To be perfect, the text would be on one line followed by the *(maybe with a
space between each *) The *'s could actually even just start on the following
line.
Thanks again for your time and knowledge.
:
Your Treatment Date expression could be simplified to:
TreatmentDate2: Nz([Treatment Date], [Treatment Date],"* * * * * * *
* * * * * * * * * * * * * * * * * *")
For the memo field you could pad it out with asterisks(or any character of
your choice) to 900 characters with an expression such as the following as
the ControlSource of the text box on the form:
=IIf(Len([YourMemoField] & "")>=900,[YourMemoField],[YourMemoField] &
String(900-Len([YourMemoField] & ""),"*"))
Ken Sheridan
Stafford, England
:
I have some official reports, when blank, they need some type of fill in them.
In order to NOT have redundant data put nto the table, I created the fills
in queries that the reports run off from. Example:
TreatmentDate2: IIf([Treatment Date] Is Null,"* * * * * * * * * *
* * * * * * * * * * * * * * *",[Treatment Date])
' PUTS * * IN NULL FIELDS
This is working great but, my problem is;
I do not know how to fill the blank space in a memo field on this report.
The fiield may be blank or have 900+ charactors. I am trying to have the memo
field filled in on exisisting blank space that may be there. This fill can be
lines or charactors but must occur after any text that may be in the memo
field.
