Need to TransferText "LIKE" ...

  • Thread starter Thread starter Bonnie A
  • Start date Start date
B

Bonnie A

Hi everyone! Using A02 on XP.

I have a macro that imports a comma delimited file with a TransferText line.
The data for the line is:

Transfer Type - Import Delimited
Specification Name - CensusImportSpec1
Table Name - Census1
File Name: ="S:\RPS\PTS\CensusConversion\" &
[Forms]![fCensus1Conversion]![PolNum] & ".csv"
Has Field Names - No

The file name has been the policy number dot csv. This year the download
will change the the file names will be the policynumber followed by 8-10
digits and characters that I do not care about. The file extension will
still be dot csv. How can I change the

="S:\RPS\PTS\CensusConversion\" & [Forms]![fCensus1Conversion]![PolNum] &
".csv"

to find the file that is

LIKE "S:\RPS\PTS\CensusConversion\" & [Forms]![fCensus1Conversion]![PolNum]
&"*"& ".csv"

The file name I am testing with is: 0002_120080110_161113.csv Only the
first 4 digits are the policy number.

I tried the "LIKE" above but the table is empty. I know I'm missing
something easy. It's probably the quotes, the bane of my access understanding.

Thanks in advance for any help or advice! Luv u guys!!!
 
Bonnie,

I am not in a position to test it at the moment, but I would be pretty
sure that you can't use the Like operator in this context. So, I don't
think you are missing something easy, I think you are trying to do the
impossible! :-)

I think you will need to rename the file, or make a copy of the file to
a name that you have exact control over the name. You can do that
manually, or you can do it using VBA code, but not in a macro.
 
Hey Steve,

Thanks for the reply. Oh well. It was a nice idea. We'll try to change
the output name from the vendor software.

I appreciate the time you take to help out, it allows me to do so much more
than I could on my own.
--
Bonnie W. Anderson
Cincinnati, OH


Steve Schapel said:
Bonnie,

I am not in a position to test it at the moment, but I would be pretty
sure that you can't use the Like operator in this context. So, I don't
think you are missing something easy, I think you are trying to do the
impossible! :-)

I think you will need to rename the file, or make a copy of the file to
a name that you have exact control over the name. You can do that
manually, or you can do it using VBA code, but not in a macro.

--
Steve Schapel, Microsoft Access MVP

Bonnie said:
Hi everyone! Using A02 on XP.

I have a macro that imports a comma delimited file with a TransferText line.
The data for the line is:

Transfer Type - Import Delimited
Specification Name - CensusImportSpec1
Table Name - Census1
File Name: ="S:\RPS\PTS\CensusConversion\" &
[Forms]![fCensus1Conversion]![PolNum] & ".csv"
Has Field Names - No

The file name has been the policy number dot csv. This year the download
will change the the file names will be the policynumber followed by 8-10
digits and characters that I do not care about. The file extension will
still be dot csv. How can I change the

="S:\RPS\PTS\CensusConversion\" & [Forms]![fCensus1Conversion]![PolNum] &
".csv"

to find the file that is

LIKE "S:\RPS\PTS\CensusConversion\" & [Forms]![fCensus1Conversion]![PolNum]
&"*"& ".csv"

The file name I am testing with is: 0002_120080110_161113.csv Only the
first 4 digits are the policy number.

I tried the "LIKE" above but the table is empty. I know I'm missing
something easy. It's probably the quotes, the bane of my access understanding.

Thanks in advance for any help or advice! Luv u guys!!!
Click to expand...
Click to expand...
 
How about this expression for the filename argument:

="S:\RPS\PTS\CensusConversion\" & Dir("S:\RPS\PTS\CensusConversion\" &
[Forms]![fCensus1Conversion]![PolNum] &"*"& ".csv")

--

Ken Snell
<MS ACCESS MVP>




Steve Schapel said:
Bonnie,

I am not in a position to test it at the moment, but I would be pretty
sure that you can't use the Like operator in this context. So, I don't
think you are missing something easy, I think you are trying to do the
impossible! :-)

I think you will need to rename the file, or make a copy of the file to a
name that you have exact control over the name. You can do that manually,
or you can do it using VBA code, but not in a macro.

--
Steve Schapel, Microsoft Access MVP

Bonnie said:
Hi everyone! Using A02 on XP.

I have a macro that imports a comma delimited file with a TransferText
line. The data for the line is:

Transfer Type - Import Delimited
Specification Name - CensusImportSpec1
Table Name - Census1
File Name: ="S:\RPS\PTS\CensusConversion\" &
[Forms]![fCensus1Conversion]![PolNum] & ".csv"
Has Field Names - No

The file name has been the policy number dot csv. This year the download
will change the the file names will be the policynumber followed by 8-10
digits and characters that I do not care about. The file extension will
still be dot csv. How can I change the ="S:\RPS\PTS\CensusConversion\" &
[Forms]![fCensus1Conversion]![PolNum] & ".csv"

to find the file that is LIKE "S:\RPS\PTS\CensusConversion\" &
[Forms]![fCensus1Conversion]![PolNum] &"*"& ".csv"

The file name I am testing with is: 0002_120080110_161113.csv Only the
first 4 digits are the policy number.

I tried the "LIKE" above but the table is empty. I know I'm missing
something easy. It's probably the quotes, the bane of my access
understanding.

Thanks in advance for any help or advice! Luv u guys!!!
Click to expand...
Click to expand...
 
Hi Ken,

Not just a pretty face, are you? :-) That's a great idea. I didn't
test, but looks like it would work, reliable as long as there is only
one matching file.
 
Back
Top