Need ADO Filter for One Big record using Instr? Like?
Upstanding Guru's,
I have a Flat File text file with thousands of records.
Each record contains a single field "F1" containing a long string of characters.
I know that I would like to filter for all records with the value "20061225" in columns 21 though 29.
VB6 sputters error messages if I use the following code:
mADORecordSet.Open "Select * From " & sResult, moADOConnection
mADORecordSet.Filter = "(MID$(F1,21,8)='" & Cur_TranDate & "')"
The Error message I'm getting is:
"Arguments are of the wrong type, are out of acceptable range,
or are in conflict with one another."
Many Thanks
Ted
I have a Flat File text file with thousands of records.
Each record contains a single field "F1" containing a long string of characters.
I know that I would like to filter for all records with the value "20061225" in columns 21 though 29.
VB6 sputters error messages if I use the following code:
mADORecordSet.Open "Select * From " & sResult, moADOConnection
mADORecordSet.Filter = "(MID$(F1,21,8)='" & Cur_TranDate & "')"
The Error message I'm getting is:
"Arguments are of the wrong type, are out of acceptable range,
or are in conflict with one another."
Many Thanks
Ted
try mid instead of mid$
Hi Ted,
How about something like this....
mADORecordSet.Filter = "F1 LIKE '*" & Cur_TranDate & "*')"
Regards,
Wayne
How about something like this....
mADORecordSet.Filter = "F1 LIKE '*" & Cur_TranDate & "*')"
Regards,
Wayne
Hi tcalbaz,
> Arguments are of the wrong type
I think this is the clue to what is wrong.
You have not metnioned how you declared Cur_TranDate.
I would assume that if you declared it as a Date type, that you would get an error like that.
Try converting Cur_TranDate to string first , something like
Dim sCur_TranDate as String
sCur_TranDate = Format(Cur_TranDate,"yyyyM
Dabas
> Arguments are of the wrong type
I think this is the clue to what is wrong.
You have not metnioned how you declared Cur_TranDate.
I would assume that if you declared it as a Date type, that you would get an error like that.
Try converting Cur_TranDate to string first , something like
Dim sCur_TranDate as String
sCur_TranDate = Format(Cur_TranDate,"yyyyM
Dabas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Everybody, Thank you for your responses.
HTH-Jon
Your presentation of the ADO and Schema.ini file is looking very promising. All of the variables being used are of the string type. Right now I am using a DSN ODBC Text Link. Since I receiive an identically formated new file each day to process how and where would I go about using and specifying the schema specs? It would be difficult to automate a new file name every time in the DSN. I'll be giving it a try later this morning.
Thank you
Ted
HTH-Jon
Your presentation of the ADO and Schema.ini file is looking very promising. All of the variables being used are of the string type. Right now I am using a DSN ODBC Text Link. Since I receiive an identically formated new file each day to process how and where would I go about using and specifying the schema specs? It would be difficult to automate a new file name every time in the DSN. I'll be giving it a try later this morning.
Thank you
Ted
Schema.ini
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
>Since I receiive an identically formated new file each day to process how and where would I go about using and specifying the schema specs?
In your code copy the days file to a 'processing' directory and rename it to a specific name. This way you schema and your connection string will not need to change from day to day.
Leon
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
>Since I receiive an identically formated new file each day to process how and where would I go about using and specifying the schema specs?
In your code copy the days file to a 'processing' directory and rename it to a specific name. This way you schema and your connection string will not need to change from day to day.
Leon
ASKER
Leon,
Good Suggestion. and good information on Schema.Ini Files.
Case Closed.
Thank you
Ted
Good Suggestion. and good information on Schema.Ini Files.
Case Closed.
Thank you
Ted