Excel VBA - SQL to .dbf date problem - GETTING DESPERATE

E

Erasmus

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.
 
R

RayMills

Erasmus said:
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
Click to expand...
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...
 
A

Anders Altberg

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
 
C

Cindy Winegarden

Erasmus said:
And the next field along is time so I'm sure I'll struggle again.
Click to expand...

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
(e-mail address removed)

VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx
 
S

swdev2

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]
 
P

Paul Pedersen

I have run into trouble with that approach, finding that in some cases at
least, Excel datetimes are stored with fractional seconds.
 
O

Olaf Doschke

is DStamp always equal to today?

sql_string = "UPDATE prjbat SET dstamp = Date()" & _
" WHERE (batchno = " & batchno & " AND jobno = " & _
Jobno & ")"

Bye, Olaf.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Assigning print date / batch number to reports & invoices 6
VBA - exporting serial letters to single documents 0
Export to Excel in different tabs 0
Assign batch # to rcds so report can be recreated. 4
Incorrect date value when open DBF file in Excel 0
Publisher is filling blank lines with data 2
counting records by month and year / date format issues 2
Date Format 10

Top