SQL Command 1

[Rock4J]

Hi, I have code to access Foxpro DBF table as written below:

Dim VerifyConnection As Boolean
Dim sql_dbfcon As String
Set dbfcon = New ADODB.Connection
sql_dbfcon = "DSN=Visual FoxPro Tables;UID=;SourceDB=" & dbfpath & ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;NUll=Yes;Deleted=Yes;Extended Properties=DBase IV;" 'DRIVER={MySQL ODBC 3.51 Driver};OPTION=16387;SERVER=\\NW410;"
With dbfcon
    .Provider = "MSDASQL.1;"
    .ConnectionString = sql_dbfcon
    .Open
End With
If dbfcon.State = 1 Then
    VerifyConnection = True
Else
    VerifyConnection = False
End If
If VerifyConnection = False Then
    MsgBox "Failed to Connect to DBF file!", vbInformation + vbOKOnly, "Connect2DBF"
    Exit Sub
End If
Set dbfrs = New ADODB.Recordset
dbfrs.Open mysql & ";", dbfcon, adOpenStatic, adLockOptimistic

I want to execute SQL querry that can get record between 2 dates by user input, i've tried the following SQL querry but it was fail:

SELECT * FROM Log WHERE Sdate>={" & sDate1 & "} AND Sdate<={" & sDate2 & "}"

my system date is using dd/mm/yyyy format, but the foxpro date format is different.

I hope there is someone who can help here, there might someone here who have tried using FoxPro table in VB6.

Thank you.

Regards,
Rocky

 

[Andrzejek]

What would be the Select SQL if you would write it in foxpro?

If you would hard-code the dates, how would it look like?

Something like this? (my guess here, correct me, please):
[tt]
SELECT * FROM Log WHERE Sdate >= {12/12/2012} AND Sdate <= {5/6/2022}[/tt]


Have fun.

---- Andy

 

[Bluejay07]

For more code efficiency, I would recommend changing:

If dbfcon.State = 1 Then
    VerifyConnection = True
Else
    VerifyConnection = False
End If
If VerifyConnection = False Then
    MsgBox "Failed to Connect to DBF file!", vbInformation + vbOKOnly, "Connect2DBF"
    Exit Sub
End If

to

If dbfcon.State <> 1 Then
    MsgBox "Failed to Connect to DBF file!", vbInformation + vbOKOnly, "Connect2DBF"
    Exit Sub
End If

Although in my experience, if the .open fails, an error message is usually returned and the sub exits anyway unless it is handled in an error handler.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Rock4J]

What I mean is, sDate1 & sDate2 is a value from user input, such as textbox or datepicker.

I already known this code:-

SELECT * FROM Log WHERE Sdate >= {12/12/2012} AND Sdate <= {5/6/2022}

that's why i want to use it, but by getting the date value from user input.

By the way, Thanks for the great info Bluejay07..

and thanks anyway Andrzejek.. I appreciates that you are trying to help.. Thanks..

Regards,
Rocky

 

[Andrzejek]

Dim strSQL As String

strSQL = "SELECT * FROM Log WHERE Sdate >= {" & Format(sDate1, "MM/DD/YYYY") & "} AND Sdate <= {" & Format(sDate2, "MM/DD/YYYY") & "}"

or...

strSQL = "SELECT * FROM Log WHERE Sdate BETWEEN {" & Format(sDate1, "MM/DD/YYYY") & "} AND {" & Format(sDate2, "MM/DD/YYYY") & "}"

Have fun.

---- Andy

 

[Rock4J]

Thanks Andrzejek, I already tested that way earlier too and still I could not get what I want. I think the problem is because the table is a Foxpro Table (*.DBF) and it's native DATE format is not the same with what is displayed when we browse the table.

I have tested this way, (refer to the earliest code in this thread)

dbfrs.Fields("Sdate").value => Date displayed is in MM/DD/YYYY format

But actually in foxpro native format in table it has other value such as this " 12:00:00".. The structure is different.. Sdate field is not in a STRING or CHAR format.. and it is in FoxPro format..

I could not change the format to STRING, because the table is active and it is used in another FOXPRO program. I am just trying to create a VB program that could get specific information from the Table which have the DATE criteria.

No offense okay. I'm just explaining..

I understand that there is a lot of differences between VB and Foxpro..

Regards,
Rocky