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.

 

[GhostWolf]

Modify your Where clause to include:

and [Date] between #" + txtStartDate + "# and #" + txtEndDate + "#"

If you're querying SQL Server, you'll want to use ' instead of # as the date value delimiters.

 

[m4dd0g]

Ok I did that but vb tells me this when i search:

Run-time error'-2147217900(80040e14)
[Pervasive][ODBC Client Interface][LNA]
[Pervasive][ODBC Engine Interface]Syntex Error:
Select Number,Desciption,Balance08,LastCrDate from
CustomerMaster Where LastCrDate = <<???>> [Date] between
'11/18/2004' and '9/30/2005'

and then when i debug it takes me to this code

Set rsTemp = objConn.Execute(mySQL)

 

[GhostWolf]

Change:

Where LastCrDate = <<???>> [Date] between
'11/18/2004' and '9/30/2005'

to:

Where LastCrDate between '" + txtStartDate + "' and '" + txtEndDate + "'"

Debug took you to that line because it was trying to execute an invalid SQL statement.

Good luck.

 

[zemp]

What is your new dynamic SQL statement? You can also do a debug.print to see your actual SQL statement. Post both if you can.


zemp

 

[m4dd0g]

It seems To me that the code you just gave me is correct but now vb is complaining about an invalid date,time or timestamp what does that meen

 

[zemp]

Pervasive can be fussy with syntax and formats. Post your SQL and the contents of strSQL and we can have a look.

zemp

 

[m4dd0g]

Well the code at the top of this thread is all the code i have there is no more code there is no module or class sorry guys but i'm new to this and i really don't have a clue what to do because i still need this done by tommorow or i'm fired from work all it needs to do is to export data to an excel sheet between the dates specified in the two text boxes if i leave out the textboxes it exports fine but it the exports all the data in that table.

 

[GhostWolf]

At this point, Zemp's got the best idea: copy/paste the mySQL contents when it's trying to select the records. In order to do that:

- open the source code, position to the line that says

Set rsTemp = objConn.Execute(mySQL)

and press <F9>, (this will highlight the line; this is now a debug break-point);

- run the application, the program will stop when it hits the line you put the debug break on;

- click View, then click Immediate - another window will open at the bottom of your display;

- type ?mySQL and hit <ENTER>

- copy/paste the response you get in your next message and we'll see if we can figure out what's goin' on.

 

[m4dd0g]

Well Guys It Tells me this

Select * from HistoryLines Where Date between '11/18/2004' and '9/8/2005'

 

[petermeachem]

Date formats again? Change to

between '" + format(txtStartDate,"dd mmm yyy") + "' etc

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[m4dd0g]

Thanx For the help But i get the same error from vb but when i export the data to excel the date in excel look like this "38310" but in the pervasive database manager it look like this "11/19/2004" what happens to the date then because when i change my search criteria to quantity it works but not with the dates.

 

[GhostWolf]

If the date selection in the query isn't working, and you're getting a "date error" message, I'd suggest using # instead of ' around the dates in your query string. It's worth a try, anyway.

The "38310" value in the spreadsheet is because the cell's defined as a number. If you change the cell's format to "Date", you should see a real date value in it.

 

[m4dd0g]

Well I did Change the ' with # then it tells me the same as the message above and kills the program

 

[zemp]

Your field name 'Date' may be a keyword. If so that could be causing your problems. You may need to qualify it with square brackets or other characters. Try the SQL in the PCC and see what errors it gives.

zemp

 

[m4dd0g]

What do You meen a keyword and I was thinking last night is it possible to import my data in an access database with a module and then work from there because they also need me to compare the data of two tables while im busy designing the program and if you can import it to access you then can minipulate the date.

 

[GhostWolf]

Some words are "reserved", and Date is usually one of 'em.

If "Date" is one, you'd need to use [Date], instead of just Date, in your query statement - so that your statement would look like "where [Date] between...".

To use Access, just use the ODBC DSN and link the tables.
.

 

[m4dd0g]

Well I did change the date thing then it tells me this

Run-time error'-2147217900(80040e14)
[Pervasive][ODBC Client Interface][LNA]
[Pervasive][ODBC Engine Interface]Syntex Error:
Select Number,Desciption,Balance08,LastCrDate from
CustomerMaster Where <<???>> [Date] between
'11/18/2004' and '9/30/2005'

so that don't work and i'm getting frustrated now because i've been working on this for the last week and i can't get it right what am i doing wrong.

 

[zemp]

Did you run the copied SQL in the PCC (pervasive control center)? What errors did it give you?

zemp

 

[m4dd0g]

Nope don't have a clue what your talking about zemp i've you can be more specific it would help because I'm only starting with database programming and it is not fun anymore because the stuff that I do don't work and that is bull@$&$.