Help With ODBC

[m4dd0g]

Well I need help with some code what I need Is to search a table by date by using two textboxes one for the start date and one for the end date here is the code that I have use so far:

Private Sub cmdExcel_Click()
'Excell Object
Dim rsTemp
Dim lc
Dim k
Dim sRecords

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "DSN=PASTEL"
mySQL = "Select Number,Description,Balance08 from CustomerMaster where Category=" & Val(txtSch.Text)
Set rsTemp = objConn.Execute(mySQL)
If rsTemp.EOF Then
MsgBox "Geen data"
objConn.Close
Set objConn = Nothing
End If

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

'For k = 1 To rsTemp.Fields.Count
' ExlObj.ActiveSheet.Cells(4, k).Font.Bold = True
'Next
Exlobj.Visible = True
'Import Data In Excell
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, 3, 1, 1
Next
rsTemp.MoveNext
NxtLine = NxtLine + 1
Loop
Screen.MousePointer = vbDefault
End Sub

What this code do is it connects to pastel data with odbc and then export the tables above to excel but i need this code to export the fields specified by the date in the two textboxes.

 

[zemp]

Open the Pervasive control center. Make sure that you can see you DB (add if necessary). Select the db, right click and select 'Tasks - execute SQL query' or similar item.

Then copy the sql in your 'strSQL' variable and run the statememt. See what errors you get.

If Date is a keyword you may need to qualify it with single or double quotes (can't remember which, try double first), pervasive doesn't like square brackets ([]).

zemp

 

[zemp]

PCC should be installed with the pervasive DB engine. Look in start - pervasive...

zemp

 

[m4dd0g]

It gives me the same error but with this <<???>> before the date and i've tried the dubble quotes and single quotes but nothing not a date or export of data.

 

[jjames]

What does your SQL statement look like now?

mySQL = "Select Number,Description,Balance08 from CustomerMaster where Category = " & Val(txtSch.Text) AND ?????

 

[m4dd0g]

It lloks like this:
mysql = "Select DocumentNumber,Description,Date,Qty from HistoryLines Where Date Between '" + txtFrom.Text + "' and '" + txtTo.Text + "'"
and it gives me this error:
Invalid date,time or timestamp

 

[m4dd0g]

ODBC Error: SQLSTATE = 22008, Native error code = 0
Invalid date, time or timestamp value.
Error in predicate: Date >= '09/20/2004'

This is the error it gives me when i run my sql query in the PCC and it looks the same as the one vb gives me.

 

[johnwm]

You may need to ask in the Pervasive forum (forum318) to check the permissible date formats. Some databases only recognise yyyy-mm-dd formats

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[m4dd0g]

OK guys what i did now was I imported my data in excel now i wanted to filter my data with a macro and a form by using VBA code

 

[m4dd0g]

OK guys what i did now was I imported my data in excel now i wanted to filter my data with a macro and a form by using VBA code wat do i do and how do i do it.

 

[johnwm]

VBA = forum707

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[zemp]

< Error in predicate: Date >= '09/20/2004'

Might be that it is looking for a date as dd/mm/yyyy and it does not recognize 20 as a valid month. Try using '20/09/2004' and see what happens.


zemp

 

[m4dd0g]

Ok I think I fixed my problem but when I run my program and enter the two dates there is no data being imported to excel.
this is how my line of code looks now and sql execute this with no problem and in my PCC it also display no data

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