×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Invalid Date,Time Or Timestamp

Invalid Date,Time Or Timestamp

Invalid Date,Time Or Timestamp

(OP)
This is the error i get when i search a pervasive database between to dates need help

Invalid Date,Time or Timestamp

And here is the code i Used:

Private Sub cmdSearch_Click()
Dim rsTemp
Dim lc
Dim k
Dim sRecords

'Connect To Database
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "DSN=PASTEL"
mysql = "Select CustomerCode,DocumentNumber,InclusivePrice,Qty,Description from HistoryLines " 'Where Date Between '" + txtFrom.Text + "' and '" + txtTo.Text + "'"
Set rsTemp = objConn.Execute(mysql)


'Draw Excel WorkBook With Colums
Set Exlobj = CreateObject("excel.application")
Exlobj.Workbooks.Add
Screen.MousePointer = vbHourglass
With Exlobj.ActiveSheet
   ' Print the heading and columns
     .Cells(1, 3).Value = "Worcester Gas en Sport Maand Einde"
     .Cells(1, 3).Font.Name = "Verdana"
     .Cells(1, 3).Font.Bold = True:
     .Cells(1, 3).Font.Size = 14:
     .Cells(4, 1).Value = "Klient Nommer":                  .Cells(4, 2).Value = "Klient Naam"
     .Cells(4, 3).Value = "Aankope Per Produk":             .Cells(4, 4).Value = "Totale Aankope"
     .Cells(4, 5).Value = "Totale Aankope Sonder BTW":      .Cells(4, 6).Value = "Prys Per Kg"
     .Cells(4, 7).Value = "Totale Wins":                    .Cells(4, 8).Value = "Wins Per Kg"
     .Cells(4, 9).Value = "Wins Persentasie"
End With

'Import Data In Excel WorkBook
Exlobj.Visible = True
        NxtLine = 5
        Do Until rsTemp.EOF
            For lc = 0 To rsTemp.Fields.Count - 1
                ' Populate data into the sheet
                Exlobj.ActiveSheet.Cells(NxtLine, lc + 1).Value = rsTemp.Fields(lc)
                'If rsTemp.Fields.Item(lc).Name <> "DATE" Then
                '   Exlobj.ActiveSheet.Cells(NxtLine, lc + 1).Value = rsTemp.Fields(lc)
                'Else
                '   Exlobj.ActiveSheet.Cells(NxtLine, lc + 1).Value = Format(rsTemp.Fields(lc), "dd/mm/yy")
                'End If
                ' Autoformat the sheet
                Exlobj.ActiveCell.Worksheet.Cells(NxtLine, lc + 1).AutoFormat _
                       xlRangeAutoFormatList2, 0, regular, 0, 0, 0
            Next
            rsTemp.MoveNext
            NxtLine = NxtLine + 1
        Loop
Screen.MousePointer = vbDefault
End Sub

RE: Invalid Date,Time Or Timestamp

Sounds like you've got an "invalid date or time".  THis is a data issue (not a database issue).  Since you're using ADO and you are specifying a DSN, you're using ODBC.  The Microsoft ODBC Specification defines a valid date as being a month of 1 to 12, day of 1 to 31 (dependant on the month), and a year 0000 to 9999.  For example, 11/21/2005 is valid.  2/31/2005 is not.  00/00/0000 is not a valid date either.  
Usually when an app sees the "invalid date", it's either got a 00/00/0000 or 32/32/8224 in the data base.  
Fixing the data won't help until the way the data is entered  is fixed and the invalid dates can't be entered.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.
http://www.mirtheil.com

RE: Invalid Date,Time Or Timestamp

(OP)
Ok I've tried all the ways you can enter a date and it still tels me Invalid date,time or timestamp and I've tried them like this 01/12/2004 to 31/12/2004 and like 12/01/2004 to 12/31/2004 and like this 2004/12/01 to 2004/12/01 and the other way around.

RE: Invalid Date,Time Or Timestamp

Try '2004-12-1' for December 1, 2004.  
You will need the single quotes.  You'll also want to enclose the field name (Date) in double quotes.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.
http://www.mirtheil.com

RE: Invalid Date,Time Or Timestamp

(OP)
That date don't work and when i put the date in double quotes vb tells me expected end of statement.

RE: Invalid Date,Time Or Timestamp

Your statement needs to be something like:
mysql = "Select CustomerCode,DocumentNumber,InclusivePrice,Qty,Description from HistoryLines  'Where " & chr$(34) & "Date" & chr$(34) & " Between '" + txtFrom.Text + "' and '" + txtTo.Text + "'
Where txtFrom.Text (and txtTo.Text) should be something like: 2004-12-1

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.
http://www.mirtheil.com

RE: Invalid Date,Time Or Timestamp

(OP)
OK it seems to work but it gives me a syntex error that said something about between<<??>>2004-8-1.

RE: Invalid Date,Time Or Timestamp

(OP)
I think i fixed it can you just check this line because it works but it returns no data to excel:

mysql = "Select CustomerCode,DocumentNumber,InclusivePrice,Qty,Description from HistoryLines  Where '" & Chr$(34) & "[Date]" & Chr$(34) & "' Between '" + txtFrom.Text + "' And '" + txtTo.Text + "'"

RE: Invalid Date,Time Or Timestamp

Your code generates the following SQL statement(dates added for completeness):
Select CustomerCode,DocumentNumber,InclusivePrice,Qty,Description from HistoryLines  Where '"[Date]"' Between '2004-12-01' And '2004-12-31'
There is a problem.  It should look like:
Select CustomerCode,DocumentNumber,InclusivePrice,Qty,Description from HistoryLines  Where "Date" Between '2004-12-01' And '2004-12-31'
The above can be generated by the following:
mysql = "Select CustomerCode,DocumentNumber,InclusivePrice,Qty,Description from HistoryLines  Where " & Chr$(34) & "Date" & Chr$(34) & " Between '" + txtFrom.Text + "' And '" + txtTo.Text + "'"
Then, if you are still getting no records, try the exact query being passed (print out the query after the values are filled in) in the Pervasive COntrol Center.  If it returns records, there's something in your code that's causing the problem.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.
http://www.mirtheil.com

RE: Invalid Date,Time Or Timestamp

(OP)
Thanx mirtheil your the man and then just one other thing if i want to import two tables of data but both tables have a field with the same name how do i go to work then because the guy that i'm doing this program for just told me that he wants both tables on one excel sheet.

RE: Invalid Date,Time Or Timestamp

You'll need to alias the fields.  For example:
select table1.field1, table2.field1 from table1, table2
or
select t1.field1, t2.field1 from table1 t1, table2 t2

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.
http://www.mirtheil.com

RE: Invalid Date,Time Or Timestamp

(OP)
Ok I understand but what if you want to compare two of the fields and if the data is the same export that data to excel because both of my tables have a customernumber field and when i export the data it puts the customer name and product name out but the customer name have the same product name.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close