I'm trying to use a data table object to display a saved daily spreadsheet. Could use some help with the SQL setup.

[dsg1]

I am using a spreadsheet object to create a dailylog.csv  file.

I save that to C:\program files\national instruments\lookout 5.1\ (the four digit year)\(first 3 letters of the current month)\(two digit day)

I would like to use a data table object to display the values of that day.

I've looked around at some of the help files and am not sure if they are getting me closer or farther away from where i need to be.

The code from a previous staff member worked at one time, but now that it isn't working, i'm not sure if its in that code, or if  i'm missing a driver or something.

The connecting string and SQL command that I had working in version 5.0 was

"DSN=Text Files; DBQ=C:\Program Files\National Instruments\Lookout 5.1\;"

"SELECT * FROM "&text(ReportViewingYear,"0000")&"\"&tchoose(nif(RTS_ReportMonthly2.1,1,0)+nif(RTS_ReportMonthly2.2,2,0)+nif(RTS_ReportMonthly2.3,3,0)+nif(RTS_ReportMonthly2.4,4,0)+nif(RTS_ReportMonthly2.5,5,0)+nif(RTS_ReportMonthly2.6,6,0)+nif(RTS_ReportMonthly2.7,7,0)+nif(RTS_ReportMonthly2.8,8,0)+nif(RTS_ReportMonthly2.9,9,0)+nif(RTS_ReportMonthly2.10,10,0)+nif(RTS_ReportMonthly2.11,11,0)+nif(RTS_ReportMonthly2.12,12,0),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"\"&text(RTS_ReportViewingday,"00")&"\dailylog.csv"

I'm pretty sure that there is a better way than the nif statements but would appreciate a little guidance.

[Mike@DTSI]

That is a radio select correct?

SELECT * FROM "&text(ReportViewingYear,"0000")&"\"&tchoose(RTS_ReportMontly2.value,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"\"&text(RTS_ReportViewingday,"00")&"\dailylog.csv"

As far as using the text driver, havent used it.  Good luck
Mike

[dsg1]

ReportViewingYear is a potentiometer.

RTS_reportMonthly2 is a radio.

RTS_ReportViewingday is a potentiometer.

[dsg1]

For anyone that does get to reading this.... I am able to hardcode a date in the connect string, and just put the name of the file in the SQL command.

Neither the statements for the choosing the year, month, or date work when not hardcoded in the connect string.

If someone knows how to select those different values with SQL statements that would be great, I'm pretty sure its my statement that is wrong as it works hardcoded.

I know that it is possible to put up a text entry box and then have a user enter in the 2007/Feb/13  if that would be easier than working with the pots and radio button, but again, not sure how to do the SQL command for that.

[Mike@DTSI]

Did my revised SQL statement work for you?

[dsg1]

Unfortunately it did not.  ....I was only able to get it to work if I used...

connect string of

"DSN=Text Files; DBQ=C:\Program Files\National Instruments\Lookout 5.1\2007\Feb\12;"

and SQL command of

SELECT * FROM "&"\dailylog.csv"

I've tried to insert the code you gave me for selecting the month, and hardcoded the 2007 in the connect string and the 12 in the SQL command, but that did not work either.  The code for selecting either the year or the day also was a no go. 

[Mike@DTSI]

Try this code.   Works on mine just fine.

[dsg1]

Thanks Mike... I appreciate the help.  I renamed my variables to make it simple and matched yours and then did the same command code you did in the connect string.

My problem was that I was trying to do the variable in the SQL statement, not in the connect string.  The old code had applied it in the SQL statement, so that was what I was trying to do as well.  Again, thanks for the help.  It works great now.

[maxofmillions]

i use a couple of comboboxes for the month and day ( add the year if you like) with a go pushbutton...this is the run expression that makes it work..

"C:\Program Files\Microsoft Office\Office\Excel.Exe "&"""C:\Program Files\National Instruments\Lookout 6.0\2007\"&left(Side2MonthCombobox.properties.selectedItem,3)&"\"&Side2DayCombobox.properties.selectedItem&"\Side 2 Material Log.Csv"""

[maxofmillions]

did you get this working like you wanted....randy