Invalid Date,Time Or Timestamp

[m4dd0g]

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

 

[mirtheil]

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

 

[m4dd0g]

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.

 

[mirtheil]

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

 

[m4dd0g]

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

 

[mirtheil]

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

 

[m4dd0g]

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

 

[m4dd0g]

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 + "'

 

[mirtheil]

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

 

[m4dd0g]

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.

 

[mirtheil]

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

 

[m4dd0g]

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.