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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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
the
query
rather
than the table:

Replace([MyField], Chr(13) & Chr(10), Chr(10))

If that doesn't work, try replacing the Carriage Return/Line
Feed
with
a
space:

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:000344F6-F0BA-41F0-9AB7-25F49D0B35D4@xxxxxxxxxxxxxxxx
I am trying to export data from access that has control
returns
in
the
basic
format of the field. When data is exported to excel (or
imported
by
query
in
excel) these codes cause the data to be spread out and
sometimes
cut
off.
How can I run a query in access and export that data
completely
ingnoring
the
control returns. Or as an alternative, how can I copy
fields
from
access
and
paste them into excell and have the control characters
ingnored.
Or
run a
query in excel that imports data ingnoring the codes?


















.

Relevant Pages

  • Re: Excel Calcs in Access
    ... show below looks to me like an Excel sheet with one column of ... "John Nurick" wrote: ... below is the result of the same query but 1 line above. ... John Nurick [Microsoft Access MVP] ...
    (microsoft.public.access.modulesdaovba)
  • Re: ignoring format/control codes exporting data from access to ex
    ... Doug Steele, Microsoft Access MVP ... If you're using it in a query, open the query in Design view, then ... satisfy her - I have finally got the entire field exporting to excel ...
    (microsoft.public.access.externaldata)
  • Re: Will not open Dialog box
    ... This DB is for finding duplicates in various Excel Reports. ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.forms)
  • Re: Excel Calcs in Access
    ... Import it from an excel spreadsheet into a table. ... "John Nurick" wrote: ... below is the result of the same query but 1 line above. ... John Nurick [Microsoft Access MVP] ...
    (microsoft.public.access.modulesdaovba)
  • Re: Excel Calcs in Access
    ... pefrom this in access since i have about 30000 rows in excel to do this to, ... "John Nurick" wrote: ... below is the result of the same query but 1 line above. ... John Nurick [Microsoft Access MVP] ...
    (microsoft.public.access.modulesdaovba)