removing ""s from fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a .txt file that is imported into a DB. There are 100+ fields in
the DB and that data types are text, date, memo, currency, number, pretty
much everything.

My problem is that some of the fields, not all, are in ""s.

When the data is imported I can not set the data type as the data should be
because the fields will be blank for all fields with the values in ""s if
they are not formatted as text fields.

How would I run a query to select only the data between the ""s. I can't
use Mid() because the characters between the ""s is different for each record.

Thanks in advance,

-Matt
 
Matt said:
I've got a .txt file that is imported into a DB. There are 100+ fields in
the DB and that data types are text, date, memo, currency, number, pretty
much everything.

My problem is that some of the fields, not all, are in ""s.

When the data is imported I can not set the data type as the data should be
because the fields will be blank for all fields with the values in ""s if
they are not formatted as text fields.

How would I run a query to select only the data between the ""s. I can't
use Mid() because the characters between the ""s is different for each record.
Click to expand...


Sure you can use Mid. Along with the Len function, you have
all the information you need:

Mid(field, 2, Len(field) - 2)

If you are not concerned with quotes in the middle of the
field, you could also just delete all the quotes in the
field:
Replace(field, """", "")
 
I don't know why, but:

When I run the Replace([Adopt Date],"""","") I get this error:
"Undefined function 'Replace' in expression".

And when I run Mid([Adopt Date],2,Len([Adopt Date]-2)) I get
#Error in a select query if its an update query the field is null.
 
The Replace is was fixed years ago in an A2K Service Pack.
If you're using A2K without all the updates, apply them.
They're pretty important for a long list of issues. If you
are not using an early version of A2K, then I don't know why
you're getting that error.

The Mid function does not like it when you feed it Null
values ;-) If the field really has Null values and not
zero length strings, you can use something more like:

IIf(Len(Nz(field,""))<2, "", Mid(field,2,Len(field)-2))

This also raises the question of what happens if the field
does not have quotes??
--
Marsh
MVP [MS Access]

I don't know why, but:

When I run the Replace([Adopt Date],"""","") I get this error:
"Undefined function 'Replace' in expression".

And when I run Mid([Adopt Date],2,Len([Adopt Date]-2)) I get
#Error in a select query if its an update query the field is null.


Marshall Barton said:
Sure you can use Mid. Along with the Len function, you have
all the information you need:

Mid(field, 2, Len(field) - 2)

If you are not concerned with quotes in the middle of the
field, you could also just delete all the quotes in the
field:
Replace(field, """", "")
Click to expand...
Click to expand...
 
I've got SP3, but the Nz() worked great.

Thanks

Marshall Barton said:
The Replace is was fixed years ago in an A2K Service Pack.
If you're using A2K without all the updates, apply them.
They're pretty important for a long list of issues. If you
are not using an early version of A2K, then I don't know why
you're getting that error.

The Mid function does not like it when you feed it Null
values ;-) If the field really has Null values and not
zero length strings, you can use something more like:

IIf(Len(Nz(field,""))<2, "", Mid(field,2,Len(field)-2))

This also raises the question of what happens if the field
does not have quotes??
--
Marsh
MVP [MS Access]

I don't know why, but:

When I run the Replace([Adopt Date],"""","") I get this error:
"Undefined function 'Replace' in expression".

And when I run Mid([Adopt Date],2,Len([Adopt Date]-2)) I get
#Error in a select query if its an update query the field is null.

Matt wrote:
I've got a .txt file that is imported into a DB. There are 100+ fields in
the DB and that data types are text, date, memo, currency, number, pretty
much everything.

My problem is that some of the fields, not all, are in ""s.

When the data is imported I can not set the data type as the data should be
because the fields will be blank for all fields with the values in ""s if
they are not formatted as text fields.

How would I run a query to select only the data between the ""s. I can't
use Mid() because the characters between the ""s is different for each record.
Click to expand...

Marshall Barton said:
Sure you can use Mid. Along with the Len function, you have
all the information you need:

Mid(field, 2, Len(field) - 2)

If you are not concerned with quotes in the middle of the
field, you could also just delete all the quotes in the
field:
Replace(field, """", "")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top