Doug,
I tried both and both times the ProdProcedures field was not brought
down
with my data when I ran the query.
When I type in your statement in the field line and leave table field
blank
my SQL looks like this:
ELECT tblNewProducts.CatalogNum, tblNewProducts.DateEntered,
tblNewProducts.Desc, tblNewProducts.Host, tblNewProducts.Amount,
tblNewProducts.ProdSize, tblNewProducts.Buffer,
tblNewProducts.ShelfLife,
Replace([tblNewProducts].[ProdProcedures],Chr(13) & Chr(10),Chr(32)) AS
Expr1, tblNewProducts.SpeciesReactivity, tblNewProducts.WB,
tblNewProducts.IP, tblNewProducts.IHC, tblNewProducts.ICC,
tblNewProducts.IF,
tblNewProducts.ChIP, tblNewProducts.EMSA, tblNewProducts.Applications,
tblNewProducts.Storage, tblNewProducts.AntibodyType,
tblNewProducts.Format,
tblNewProducts.CurrentPrice, tblNewProducts.AltNames,
tblNewProducts.Discontinued
FROM tblNewProducts
WHERE (((tblNewProducts.Discontinued)=No));
When I type in the information as you gave me in SQL view same thing
happens
- my SQL now looks like:
SELECT tblNewProducts.CatalogNum, tblNewProducts.DateEntered,
tblNewProducts.Desc, tblNewProducts.Host, tblNewProducts.Amount,
tblNewProducts.ProdSize, tblNewProducts.Buffer,
tblNewProducts.ShelfLife,
Replace(tblNewProducts.ProdProcedures,Chr(13) & Chr(10),Chr(32)),
tblNewProducts.SpeciesReactivity, tblNewProducts.WB, tblNewProducts.IP,
tblNewProducts.IHC, tblNewProducts.ICC, tblNewProducts.IF,
tblNewProducts.ChIP, tblNewProducts.EMSA, tblNewProducts.Applications,
tblNewProducts.Storage, tblNewProducts.AntibodyType,
tblNewProducts.Format,
tblNewProducts.CurrentPrice, tblNewProducts.AltNames,
tblNewProducts.Discontinued
FROM tblNewProducts
WHERE (((tblNewProducts.Discontinued)=No));
and again ProProcedures field not pulled down in query.
If I select the table in the table line I get a "enter parameter value"
screen - I trield typing in ProdProcedures, NewProducts, * and don't
get
the
field.
thanks
Delma
:
Replace([tblNewProduct].[ProdProcedures],Chr(13) & Chr(10),Chr(32))
The Table line should be blank.
Failing that, when you're in the SQL, change it to
SELECT tblNewProducts.CatalogNum, tblNewProducts.DateEntered,
tblNewProducts.Desc, tblNewProducts.Host, tblNewProducts.Amount,
tblNewProducts.ProdSize, tblNewProducts.Buffer,
tblNewProducts.ShelfLife,
Replace(tblNewProducts.ProdProcedures, Chr(13) & Chr(10), Chr(32)),
tblNewProducts.SpeciesReactivity,
tblNewProducts.WB, tblNewProducts.IP, tblNewProducts.IHC,
tblNewProducts.ICC,
tblNewProducts.IF, tblNewProducts.ChIP, tblNewProducts.EMSA,
tblNewProducts.Applications, tblNewProducts.Storage,
tblNewProducts.AntibodyType, tblNewProducts.Format,
tblNewProducts.CurrentPrice, tblNewProducts.AltNames,
tblNewProducts.Discontinued
FROM tblNewProducts
WHERE (((tblNewProducts.Discontinued)=No));
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
When I type in the replace statement I can't get to the SQL in order
to
copy
and paste it here for you. I get the error "Syntax error (comma) in
query
expression 'tblNewProducts. [Replace([ProdProcedures],Chr(13) &
Chr(10),Chr(32)),Chr(13) & Chr(10),Chr(32))]
my field line reads:
Expr1: Replace([ProdProcedures],Chr(13) & Chr(10),Chr(32))
and the table line reads tblNewProducts
thanks
Delma
:
But I don't see the Replace statement anywhere in that SQL...
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Doug, I'm using in a query - here is the SQL info:
SELECT tblNewProducts.CatalogNum, tblNewProducts.DateEntered,
tblNewProducts.Desc, tblNewProducts.Host, tblNewProducts.Amount,
tblNewProducts.ProdSize, tblNewProducts.Buffer,
tblNewProducts.ShelfLife,
tblNewProducts.ProdProcedures, tblNewProducts.SpeciesReactivity,
tblNewProducts.WB, tblNewProducts.IP, tblNewProducts.IHC,
tblNewProducts.ICC,
tblNewProducts.IF, tblNewProducts.ChIP, tblNewProducts.EMSA,
tblNewProducts.Applications, tblNewProducts.Storage,
tblNewProducts.AntibodyType, tblNewProducts.Format,
tblNewProducts.CurrentPrice, tblNewProducts.AltNames,
tblNewProducts.Discontinued
FROM tblNewProducts
WHERE (((tblNewProducts.Discontinued)=No));
:
Are you using Replace in a query or in VBA code?
If you're using it in a query, open the query in Design view,
then
select
SQL View from the View menu. Copy what's shown there into your
reply.
If you change the vbCrLf to just vbLf in your code, it won't
read
properly
in Access: you'll get a small rectangle rather than a new line.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Doug I'm sorry I have no idea what the SQL is - tell me how to
find
out
and
I will. I read the help file on SQL types and still have no
idea.
I think I have come up with something that the boss can use
that
will
satisfy her - I have finally got the entire field exporting to
excel
which
does enough for her, but now my curiousity is taking over of
course.
Let me regress a bit - I have a field in a table that is
populated
by
other
fields as they are input on a form. The code behind this also
adds
blank
lines to make this field "look nice." This is a memo field
and
when
the
report and exported to excel to send to our distributors, the
data
is
cut
off
mid stream. This field on the report was set to "no" to
expansion.
I
changed the field to "yes" allow expansion and the full
information
now
is
on
the report in that field. But when I export that report to
excel
the
field
is cut off about mid way.
So my problem is I have a report in access that when exported
to
excel
this
field is cut off and not all information is sent to excel.
If I export from the query that this report is ran from I can
get
the
all
the data for that field exported, but the little square codes
that
are
at
the
end of each of the three statements that are put in this field
(what
I
think
are control returns) are sent with the data and the data is of
course
spread
out with this formatting.
The following is the code that I found in the table for that
field
that
is
getting populated - I guess maybe the problem is with the
vbCrLf
statements?
Is that what I need to remove at the query? - thanks
Left(Me.CatalogNum, 4)
If strCatNum = "A300" Or strCatNum = "A301" Or strCatNum
=
"A302"
Or
strCatNum = "A303" Or strCatNum = "A304" Or strCatNum = "A305"
Then
Me.ProdProcedures = "Antibody was affinity purified
using
an
epitope specific to " & [Target] & _
" immobilized on solid support." & vbCrLf & vbCrLf
&
"The
epitope recognized by " & [CatalogNum] & _
" maps to a region between residue ?? and ?? of ??
using
the
numbering given in entry ?? (GeneID ??)." & _
vbCrLf & vbCrLf & "Antibody concentration was
determined
by
extinction coefficient: " & _
"absorbance at 280 nm of 1.4 equals 1.0 mg of IgG."
Thanks so much
Delma
:
What's the SQL of your query?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
message
no that doesn't do it - I've also tried
replace([myfield], Chr(13) & Chr(10), Chr(32) - also used "
"
rather
than
chr(32)
replace([myfield], Chr(13) + Chr(10), Chr(32)
replace(([myfield], (Chr(13) & Chr(10)), (Chr(32)))
this is the only formula that I don't get a syntax formula
error
on
and
I
get a syntax comma error on it:
Expr1: Replace([ProdProcedures],Chr(13) & Chr(10)," ")
notice the spaces have been removed when I click off or
field
and
try
to
run
the query.
:
Is your computer set for a locale that uses ; (semi-colon)
as
the
list
separator rather than , (comma)?
Try:
Replace([MyField]; Chr(13) & Chr(10); " ")
