Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba,microsoft.public.excel.misc,microsoft.public.excel,microsoft.public.fox.helpwanted
|
|||
|
|||
Excel VBA - SQL to .dbf date problem - GETTING DESPERATE
Hi,
I've been trying to do some SQL work in VBA to allow people to change database fields more easily. I managed to: extract the data from the file, change it in excel, add a new record where the user adds a new row, start populating this field (with default values as only certain values are changed in the view the user sees), crash when trying to populate a date field. At some point I'm going to try deleting fields (all efforts so far have failed) but for now, I'm trying to get this date field updated so that the remaining fields will populate themselves. Using the UPDATE statement I tried each of the following: sql_string = "UPDATE prjbat SET dstamp = #" & DStamp & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = #" & Format(DStamp, "dd-mmm- yyyy") & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = '" & DStamp & _ "' WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = '" & Format(DStamp, "dd-mmm- yyyy") & _ "' WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = " & DStamp & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = " & Format(DStamp, "dd-mmm- yyyy") & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = #" & Format(DStamp, "mm/dd/ yy") & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = """ & Format(DStamp, "mm/dd/ yy") & """" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = LUPDATE(prjbat)* & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = sysdate" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = {ts " & DString & "}" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" The format of the dstamp field in the table is mm/dd/yy. I also tried reading dstamp in in both date and string format, including a date format which removes the time element ( DATE(year,month,day) ). I just want a date stamp showing the latest date so am happy for a SQL function that sticks in the current date. I'm going insane. And the next field along is time so I'm sure I'll struggle again. |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.office.developer.vba,microsoft.public.fox.helpwanted
|
|||
|
|||
Excel VBA - SQL to .dbf date problem - GETTING DESPERATE
"Erasmus" wrote:
Hi, I've been trying to do some SQL work in VBA to allow people to change database fields more easily. I managed to: extract the data from the file, change it in excel, add a new record where the user adds a new row, start populating this field (with default values as only certain values are changed in the view the user sees), crash when trying to populate a date field. At some point I'm going to try deleting fields (all efforts so far have failed) but for now, I'm trying to get this date field updated so that the remaining fields will populate themselves. Using the UPDATE statement I tried each of the following: sql_string = "UPDATE prjbat SET dstamp = #" & DStamp & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = #" & Format(DStamp, "dd-mmm- yyyy") & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = '" & DStamp & _ "' WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = '" & Format(DStamp, "dd-mmm- yyyy") & _ "' WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = " & DStamp & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = " & Format(DStamp, "dd-mmm- yyyy") & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = #" & Format(DStamp, "mm/dd/ yy") & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = """ & Format(DStamp, "mm/dd/ yy") & """" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = LUPDATE(prjbat)* & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = sysdate" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = {ts " & DString & "}" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" The format of the dstamp field in the table is mm/dd/yy. I also tried reading dstamp in in both date and string format, including a date format which removes the time element ( DATE(year,month,day) ). I just want a date stamp showing the latest date so am happy for a SQL function that sticks in the current date. I'm going insane. And the next field along is time so I'm sure I'll struggle again. I too have struggled with date fields in sql ...without studying your code try this create an adp in access linked to your database and create a simple search query in the designer on a specific date..then view sql...i suspect you'll see you need a convert statement... simple yes but i think it may put u in the right direction... |
#3
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba,microsoft.public.excel.misc,microsoft.public.excel,microsoft.public.fox.helpwanted
|
|||
|
|||
Excel VBA - SQL to .dbf date problem - GETTING DESPERATE
There are two ways to specify dates unambiguously in VFP (and VFPOLEDB)
code. Either DATE(2007,6,29) or {^2007/06/29} The VB # date indentifier means nothing to VFP. Or rather, in VFP # equals <, Not Equal To If your using parameters, try preceding the parameter name with ?, or build a parameter object. -Anders "Erasmus" wrote in message oups.com... Hi, I've been trying to do some SQL work in VBA to allow people to change database fields more easily. I managed to: extract the data from the file, change it in excel, add a new record where the user adds a new row, start populating this field (with default values as only certain values are changed in the view the user sees), crash when trying to populate a date field. At some point I'm going to try deleting fields (all efforts so far have failed) but for now, I'm trying to get this date field updated so that the remaining fields will populate themselves. Using the UPDATE statement I tried each of the following: sql_string = "UPDATE prjbat SET dstamp = #" & DStamp & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = #" & Format(DStamp, "dd-mmm- yyyy") & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = '" & DStamp & _ "' WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = '" & Format(DStamp, "dd-mmm- yyyy") & _ "' WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = " & DStamp & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = " & Format(DStamp, "dd-mmm- yyyy") & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = #" & Format(DStamp, "mm/dd/ yy") & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = """ & Format(DStamp, "mm/dd/ yy") & """" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = LUPDATE(prjbat)* & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = sysdate" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = {ts " & DString & "}" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" The format of the dstamp field in the table is mm/dd/yy. I also tried reading dstamp in in both date and string format, including a date format which removes the time element ( DATE(year,month,day) ). I just want a date stamp showing the latest date so am happy for a SQL function that sticks in the current date. I'm going insane. And the next field along is time so I'm sure I'll struggle again. |
#4
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba,microsoft.public.excel.misc,microsoft.public.excel,microsoft.public.fox.helpwanted
|
|||
|
|||
Excel VBA - SQL to .dbf date problem - GETTING DESPERATE
"Erasmus" wrote in message
oups.com... And the next field along is time so I'm sure I'll struggle again. Hi Erasmus, To go further with what Anders said, VFP datetime values can be written like "... Where DateTime = {^2007/06/29 10:30:59}" or "...Where DateTime = Datetime(2007, 6, 29, 10, 30, 59)" or again as a parameter. The parameter is perhaps the simplest because you just let the OLE DB data provider translate between VB and FoxPro. -- Cindy Winegarden VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx |
#5
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba,microsoft.public.excel.misc,microsoft.public.excel,microsoft.public.fox.helpwanted
|
|||
|
|||
Excel VBA - SQL to .dbf date problem - GETTING DESPERATE
I would suggest to you that you are going through too many steps.
If all you want to do for your users is to add records and change values in existing records, in a dbf file, then most of your 'machinations' are really 'extra' work. You have some other choices. 1. open the file natively as a dbf file IN EXCEL. This presupposes the file format for the dbf is in fox2x file format. Make your changes there, save it in native dbf file format within Excel. No ODBC required. 2. Use VFP9 IDE, make a wizard to build an app for you. 3. Use another program - I'd suggest DBF Viewer 2000 - see http://www.dbf2002.com/ for more info. Give it and the dbf table to your users. It handles ALL dbf types, and I do mean ALL. 4. your dstamp string, as others mentioned, is not properly formed for VFP. Sure - you have mm/dd/yy listed, but there are variations of this. Prior to STRICTDATE implementations in VFP, you could have made your dstamp variable look like this {mm/dd/yy}- surround by curly braces, and if you used SQL STATEMENTS into an odbc connection, it would work. Now since STRICTDATE has been implemented, you have to reform your dstamp variable to look more like {^YYYY/MM/DD} - again with the curly braces. 5. There is no native TIME format in VFP - so you can either make a column with TYPE DATETIME, or use a character column that holds the TIME value that you want. 6. If you are an experienced VB 6 developer, you might want to consider changing over to VFP 9 and using it as a development tool. Mondo Regards [Bill] -- =================== William Sanders / EFG VFP / mySql / MS-SQL www.efgroup.net/vfpwebhosting www.terrafox.net www.viasqlserver.net "Erasmus" wrote in message oups.com... Hi, I've been trying to do some SQL work in VBA to allow people to change database fields more easily. I managed to: extract the data from the file, change it in excel, add a new record where the user adds a new row, start populating this field (with default values as only certain values are changed in the view the user sees), crash when trying to populate a date field. At some point I'm going to try deleting fields (all efforts so far have failed) but for now, I'm trying to get this date field updated so that the remaining fields will populate themselves. Using the UPDATE statement I tried each of the following: sql_string = "UPDATE prjbat SET dstamp = #" & DStamp & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = #" & Format(DStamp, "dd-mmm- yyyy") & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = '" & DStamp & _ "' WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = '" & Format(DStamp, "dd-mmm- yyyy") & _ "' WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = " & DStamp & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = " & Format(DStamp, "dd-mmm- yyyy") & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = #" & Format(DStamp, "mm/dd/ yy") & _ "# WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = """ & Format(DStamp, "mm/dd/ yy") & """" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = LUPDATE(prjbat)* & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = sysdate" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" sql_string = "UPDATE prjbat SET dstamp = {ts " & DString & "}" & _ " WHERE (batchno = " & batchno & " AND jobno = " & Jobno & ")" The format of the dstamp field in the table is mm/dd/yy. I also tried reading dstamp in in both date and string format, including a date format which removes the time element ( DATE(year,month,day) ). I just want a date stamp showing the latest date so am happy for a SQL function that sticks in the current date. I'm going insane. And the next field along is time so I'm sure I'll struggle again. |
#6
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba,microsoft.public.excel.misc,microsoft.public.excel,microsoft.public.fox.helpwanted
|
|||
|
|||
Excel VBA - SQL to .dbf date problem - GETTING DESPERATE
I have run into trouble with that approach, finding that in some cases at least, Excel datetimes are stored with fractional seconds. "Cindy Winegarden" wrote in message ... "Erasmus" wrote in message oups.com... And the next field along is time so I'm sure I'll struggle again. Hi Erasmus, To go further with what Anders said, VFP datetime values can be written like "... Where DateTime = {^2007/06/29 10:30:59}" or "...Where DateTime = Datetime(2007, 6, 29, 10, 30, 59)" or again as a parameter. The parameter is perhaps the simplest because you just let the OLE DB data provider translate between VB and FoxPro. -- Cindy Winegarden VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx |
#7
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba,microsoft.public.excel.misc,microsoft.public.excel,microsoft.public.fox.helpwanted
|
|||
|
|||
Excel VBA - SQL to .dbf date problem - GETTING DESPERATE
is DStamp always equal to today?
sql_string = "UPDATE prjbat SET dstamp = Date()" & _ " WHERE (batchno = " & batchno & " AND jobno = " & _ Jobno & ")" Bye, Olaf. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Desperate | Excel Discussion (Misc queries) | |||
Desperate for help in travelling salesman problem! | Excel Discussion (Misc queries) | |||
DESPERATE FOR HELP ON THIS PROBLEM...please | Excel Discussion (Misc queries) | |||
Desperate...please help! | Excel Worksheet Functions | |||
Desperate NEED!!!! | Excel Worksheet Functions |