Text box and query !

[achalrikhi]

Hello,

I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the
command :

DoCmd.TransferText acImportDelim, "fo Import Specification", "fo", FileName:="C:\Users\welcome\Desktop\fo.csv", HasFieldNames:=True

Earlier I asked the question in other forum but by mistake I provided the wrong information so did not apply the solution.

Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month’s date. And every date has got several thousand records.

now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.

The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.

I use this code for export :

DoCmd.TransferText acExportDelim, "NewFnoSpec", "fnoquery", "C:\Users\welcome\Desktop\FO Output.txt", True

this code is working fine but when the month will change, the code won't work.

Can anyone throw some light on this ?

Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.

I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.

Regards

Achal

 

[vbaInet]

Welcome to the forum Achal!

So you're only using Access to filter the data? Have you considered just doing it in Excel instead?

 

[achalrikhi]

Hello vbaInet,

First of all thank you for replying. And yes, I have tried to do it in excel but the I thought of Access as the CSV file I download, the numbers of records it contain are ever changing. More over, I have 5 / 7 dates including 3 / 4 current months date and some next and next to next month's dates. Now I have to choose the records with specific "SERIES" with specific "DATE". I do it manually everyday. I sort, I select the rest and then delete the unnecessary rows and columns and then change the headers as per my requirement and then save it.

Now I guess these kind of things will be much easier with access so I made a database, a table. Imported the CSV file. Made a query with the columns I require with a custome header and exported it. Everything is working fine except this DATE part. As the month changes, the code won't work.

Now if you have a solution in excel, I will be more than happy to have it.

Regards
Achal

 

[vbaInet]

If all you're doing is filtering the data then it's counter intuitive to use Access for that. Excel has numerous functions that can perform the same task, and you can even write your VBA in Excel too.

My advice is Excel is a better option for you, you won't have to deal with changing column headers and everything is contained in the same type of application. So if you can this question in an appropriate Excel forum I'm sure that you'll get a solution.

 

[achalrikhi]

vbaInet,

Thank you for your valuable suggestions. I have tried a lot but it was not working so I started making a database in access. But I will surely try to find the answer in excel too.

Regards

Achal

 

[vbaInet]

Upload a sample excel file so I can see what you're dealing with. Non confidential data of course.

 

[achalrikhi]

Dear vbaInet,

The file I download for edit is of .CSV format and I tried a lot but the attachment window says every time : Invalid file & does not get attached.

I tried to cut down the file size by deleting rows and from 2.05 mb to made it 100 kb but still it did not help. Pasted all the content in excel file and made an excel file, still did not help.

Regards
Achal

 

[namliam]

have you tried creating it as a ZIP file? New users I believe are limited to that.

 

[achalrikhi]

Upload a sample excel file so I can see what you're dealing with. Non confidential data of course.

Yes Sure vbaInet,

Attached is the file I download every day.

1. Sort by first column "INSTRUMENT" then sort by "EXPIRY_DT", Then delete other rows, then delete the columns "A", "D", "E","J","L" and "N", then change the date format to "mm/dd/yyyy" and then cut the column DATE and pase it on the column "EXPIRY_DT", then change the headers like :

ticker, date, open, high, low, close, volume, openint, name

So these are the basic things I do everyday.

Regards

Achal

 

[achalrikhi]

have you tried creating it as a ZIP file? New users I believe are limited to that.

Thank you namliam for your guidence... !

Regards
Achal

 

[namliam]

Looks like it should be easy to do in Access or Excel, though I think I would too use Access....

To me, this sounds very unclear

Sort by first column "INSTRUMENT" then sort by "EXPIRY_DT", Then delete other rows

What other rows?

Unless I made a mistake, your timestamp column is renamed to "name" ??

 

[achalrikhi]

I am extremely sorry about creating the confusion. Let me explain you.

I download the csv file.

Sort by INSTRUMEN & EXPIRY_DT. I require the rows with the “INSTRUMENT” = FUTIDX & FUTSTK and “EXPIRY_DT” = ( The date of last Thursday of month, in this case, current month’s date is 25-06-2015 ) So now after sorting this, I have the rows with the INSTRUMENT =FUTIDX & FUTSTK and EXPIRY_DT = 25/06/2015. So I guess over all will be not more than 165 rows. Rest of the rows I delete.

Now after all the sorting, I delete the Columns “INSTRUMENT”, “STRIKE_PR”, “OPTION_TYP”, “SETTLE_PR”, “VAL_INLAKH”, “CHG_IN_OI”. That is Columns “A”. “D”, “E”, “J”, “L”, “N”.
Then I change the format of column “TIMESTAMP” as “mm/dd/yyyy” and cut the whole column from there and paste it to the column “EXPIRY_DT” and copy the SYMBOL column and paste it in the last alongside column “OPEN_INT”, and then change the headers.
ticker, date, open, high, low, close, volume, openint and name.
So the answer of your question is, SYMBOL will become ticker, TIMESTAMP = date, OPEN = open, HIGH = high, LOW = low, CLOSE = close, CONTRACT = volume, OPEN_INT = openint and SYMBOL = name

I guess, I have cleared the confusion. Please forgive me to create this confusion

Regards
Achal

 

[vbaInet]

This looks like an FX Derivatives data, I would stick to Excel. The time taken to fetch the data into Access, look for any kinks, reformat if necessary and then publish might just not be worth it. Plus you will lose any formatting/formulas that exist on your sheet. Yes you can link to the file and save a bit of time but it's still counter intuitive doing it in Access.

* Get the used range and put it in array
* Work from the array instead of using the Cells() object
* Start from the first column and look for the last occurrence of "FUTSTK"
* Move to the second column and do the same looking for the last occurrence of your date criteria
* Delete everything below that in the sheet

Basically your array will be your reference point and the sheet will be the output.

 

[namliam]

I would do it in access, sounds like one simple query to be done on the file



Some clever use of Date and date functions can calculate the Thursday, the query takes care of the formatting etc....
date()-day(date()) will return the last day of the previous month though doing it inline is possible, doing it in a function would be easier/best IMHO

Function LastThur() As Date
    Dim StartDate As Date
    StartDate = Date - Day(Date)
    Do While Weekday(StartDate, vbMonday) <> 4
        StartDate = StartDate - 1
    Loop
    LastThur = StartDate
End Function

should calculate the last Thursday for the previous month (this case May)
Can (offcourse) easily be altered to calculate June

 

[achalrikhi]

Dear friends,

Thank you all for your kind efforts to answer my question.


Your answers were quite helpful. I have tried a little easier way and it worked for me. I put a text box named txtexpdate, on the form and in the query ( design mode ), in criteria I have put this :

Like "*" & [Forms]![Futures]![txtexpdate] & "*"

This is working fine at this juncture. And thank you once again for your efforts to answer my question. Hope this also will help others as an option to this problem.

Regards

Achal

 

[namliam]

are you doing a like on a date field? That is a seriously bad idea

 

[achalrikhi]

Do we have any easier way ? Please suggest

Regards

Achal

 

[namliam]

Think i already did? If this is actualy a date field you dont want to use like