Re: ignoring format/control codes exporting data from access to ex

Exactly how is the parameter labelled? Right about the input box, there
should be a name. Odds are you mistyped the field name, so Access doesn't
know what it's supposed to retrieve.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Horsecat" <Horsecat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2F24D519-E66D-4DD7-9D81-8A022B4A8378@xxxxxxxxxxxxxxxx
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

"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)


"Horsecat" <Horsecat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7271816D-20A6-485E-B4EF-82548D7519C8@xxxxxxxxxxxxxxxx
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

"Douglas J. Steele" wrote:

But I don't see the Replace statement anywhere in that SQL...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Horsecat" <Horsecat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:992337E3-188B-468F-B9FB-C69987A627A2@xxxxxxxxxxxxxxxx
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));


"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)


"Horsecat" <Horsecat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3DDE5796-F621-406F-880C-157F3EC1C8E0@xxxxxxxxxxxxxxxx

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




"Douglas J. Steele" wrote:

What's the SQL of your query?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Horsecat" <Horsecat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C51CDE97-F943-48AA-889B-0AB2904A3589@xxxxxxxxxxxxxxxx
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.


"Douglas J. Steele" wrote:

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); " ")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Horsecat" <Horsecat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:85759336-55B2-45A8-A622-E647BCF1973C@xxxxxxxxxxxxxxxx
Douglas,

I typed this in just as it reads and I'm getting a syntax
error
reading
"syntax error (comma) in query expression....." I have
tried
both
of
your
suggested lines putting them in the field row of my query
and
get
this
same
thing both times. Any suggestions?
thanks
Delma

"Douglas J. Steele" wrote:

Yes, Chr(13) is the Carriage Return, Chr(10) is the Line
Feed.
(If
you
were
doing it in VBA, as opposed to in a query, you could use
the
intrinisic
constant vbCrLf)

For a field named ProductionProcedure, you'd put
Replace([ProductionProcedure], Chr(13) & Chr(10), " ")
instead
of
just
ProductionProcedure in the Field row of the query grid.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Horsecat" <Horsecat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:DC9916AD-7184-480D-81E6-D397B85C96B6@xxxxxxxxxxxxxxxx
Do I put this code in the query criteria for that field?
If I
want
to
remove
the returns all together what would my line look like?
I
am
not
familiar
enough with this to know what to put in the variables -
so
my
field
is
ProductionProcedure - would my line be
replace[ProductionProcedure],
Chr(13)
& Chr(10), " ") - are the Chr(13) & Chr(10) the codes
for
the
carriage
return/line feed codes?

thanks so much for your help
Delma

"Douglas J. Steele" wrote:

Create a query that replaces the Carriage Return/Line
Feed
Access
uses
with
just a Line Feed (which is all Excel requires), and
export


.

Relevant Pages
Loading