Jagata
asked on
Querying a Paradox Database with ADO
Hello,
I have connected sucessfully to a Paradox Database using both a System DSN and a Microsoft Access linked table setup.
I am trying to query table ACTIVITY, with a criteria for a specific date. The query is quite simple;
SELECT *
FROM ACTIVITY
WHERE ACTV_BEGIN_DATE = #27-Aug-2003#
The table has records dating from the 26/08/2003 and onwards. The query returns rows where ACTV_BEGIN_DATE is equal to the 26th or the 27th. If I then change the query to;
WHERE ACTV_BEGIN_DATE = #28-Aug-2003#
It then returns rows for the 26th, 27th, and 28th. This occurs regardless of the access method.
I have simplified the query slightly to try and illustrate my problem. The final query will be specific regarding selection of fields, and will use a similar WHERE clause to the one below;
WHERE ACTV_BEGIN_DATE BETWEEN #1-Aug-2003# AND #31-Aug-2003#
Thanks,
Jason.
I have connected sucessfully to a Paradox Database using both a System DSN and a Microsoft Access linked table setup.
I am trying to query table ACTIVITY, with a criteria for a specific date. The query is quite simple;
SELECT *
FROM ACTIVITY
WHERE ACTV_BEGIN_DATE = #27-Aug-2003#
The table has records dating from the 26/08/2003 and onwards. The query returns rows where ACTV_BEGIN_DATE is equal to the 26th or the 27th. If I then change the query to;
WHERE ACTV_BEGIN_DATE = #28-Aug-2003#
It then returns rows for the 26th, 27th, and 28th. This occurs regardless of the access method.
I have simplified the query slightly to try and illustrate my problem. The final query will be specific regarding selection of fields, and will use a similar WHERE clause to the one below;
WHERE ACTV_BEGIN_DATE BETWEEN #1-Aug-2003# AND #31-Aug-2003#
Thanks,
Jason.
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForParadox
make sure your connection string have no problem and
Paradox 7 doesn't include ODBC drivers, upgrade to version 8 or 9. Or by the ODBC driver from http://www.intersolv.com
after you have try above and still have problem,then pls try to format your date.Also make sure you understand your Regional setting format.
Paradox 7 doesn't include ODBC drivers, upgrade to version 8 or 9. Or by the ODBC driver from http://www.intersolv.com
after you have try above and still have problem,then pls try to format your date.Also make sure you understand your Regional setting format.
ASKER
MYLim,
Thankyou for your reply. However the problem has nothing to do with the connection to the Paradox database, I have quite successfully connected to the database using both a System DSN, and a Access Linked Table setup. I am querying all tables within the database with no issues.
I do not believe the date formating is incorrect, however to be sure I have also tried the following with the same results;
dd/mm/yy
dd/mm/yyyy
mm/dd/yy
mm/dd/yyyy
dd-mmm-yyyy
The last format should never pose any issues as the system cannot confuse the order of the day and month.
Jason.
Thankyou for your reply. However the problem has nothing to do with the connection to the Paradox database, I have quite successfully connected to the database using both a System DSN, and a Access Linked Table setup. I am querying all tables within the database with no issues.
I do not believe the date formating is incorrect, however to be sure I have also tried the following with the same results;
dd/mm/yy
dd/mm/yyyy
mm/dd/yy
mm/dd/yyyy
dd-mmm-yyyy
The last format should never pose any issues as the system cannot confuse the order of the day and month.
Jason.
Pls read this page:
http://www.elists.org/pipermail/delphi-db/2003-January/006570.html
Are you sure Paradox date quote by # ?
http://www.elists.org/pipermail/delphi-db/2003-January/006570.html
Are you sure Paradox date quote by # ?
ASKER
I have tried the following based on the Paradox date formating (error follows WHERE clause);
WHERE ACTV_BEGIN_DATE = "27.08.2003"
Coded as; "WHERE ACTV_BEGIN_DATE = " & Chr(34) & "27.08.2003" & Chr(34)
[Microsoft][ODBC Paradox Driver] " is not a valid name. Make sure it does not include invalid characters or punctuation and that it is
not too long.
WHERE ACTV_BEGIN_DATE = "8/27/2003"
Coded as; "WHERE ACTV_BEGIN_DATE = " & Chr(34) & "8/27/2003" & Chr(34)
[Microsoft][ODBC Paradox Driver] Too few parameters. Expected 1.
WHERE ACTV_BEGIN_DATE = #27.08.2003#
[Microsoft][ODBC Paradox Driver] Syntax error in date in query expression 'ACTV_BEGIN_DATE = #27.08.2003#'.
Other than the above I have tried all other formating.
I know it has to be some kind of issue between Paradox & ADO, but I am at a loss as to what else to try. I do appreciate your help MYLim. Thanks.
WHERE ACTV_BEGIN_DATE = "27.08.2003"
Coded as; "WHERE ACTV_BEGIN_DATE = " & Chr(34) & "27.08.2003" & Chr(34)
[Microsoft][ODBC Paradox Driver] " is not a valid name. Make sure it does not include invalid characters or punctuation and that it is
not too long.
WHERE ACTV_BEGIN_DATE = "8/27/2003"
Coded as; "WHERE ACTV_BEGIN_DATE = " & Chr(34) & "8/27/2003" & Chr(34)
[Microsoft][ODBC Paradox Driver] Too few parameters. Expected 1.
WHERE ACTV_BEGIN_DATE = #27.08.2003#
[Microsoft][ODBC Paradox Driver] Syntax error in date in query expression 'ACTV_BEGIN_DATE = #27.08.2003#'.
Other than the above I have tried all other formating.
I know it has to be some kind of issue between Paradox & ADO, but I am at a loss as to what else to try. I do appreciate your help MYLim. Thanks.
Pls try the combination below...
1.
WHERE ACTV_BEGIN_DATE >= #1-Aug-2003#
AND ACTV_BEGIN_DATE <= #31-Aug-2003#
2.
WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
AND ACTV_BEGIN_DATE <= '31-Aug-2003'
3.
WHERE ACTV_BEGIN_DATE BETWEEN '1-Aug-2003' AND '31-Aug-2003'
4.WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
AND ACTV_BEGIN_DATE <= '31-Aug-2003'
1.
WHERE ACTV_BEGIN_DATE >= #1-Aug-2003#
AND ACTV_BEGIN_DATE <= #31-Aug-2003#
2.
WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
AND ACTV_BEGIN_DATE <= '31-Aug-2003'
3.
WHERE ACTV_BEGIN_DATE BETWEEN '1-Aug-2003' AND '31-Aug-2003'
4.WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
AND ACTV_BEGIN_DATE <= '31-Aug-2003'
ASKER
1.
WHERE ACTV_BEGIN_DATE >= #1-Aug-2003#
AND ACTV_BEGIN_DATE <= #31-Aug-2003#
Query returned correct results with rows dating (ACTV_BEGIN_DATE) from 26/08/2003 to 31/08/2003. Please note; there are no
records before 26/08/2003.
WHERE ACTV_BEGIN_DATE >= #1-Sep-2003#
AND ACTV_BEGIN_DATE <= #30-Sep-2003#
Query returned incorrect results with rows dating from 26/08/2003 to 21/08/2003.
2.
WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
AND ACTV_BEGIN_DATE <= '31-Aug-2003'
Run-time error '-2147217913 (80040e07)':
[Microsoft][ODBC Paradox Driver] Data type mismatch in criteria expression.
3.
WHERE ACTV_BEGIN_DATE BETWEEN '1-Aug-2003' AND '31-Aug-2003'
Run-time error '-2147217913 (80040e07)':
[Microsoft][ODBC Paradox Driver] Data type mismatch in criteria expression.
4.
WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
AND ACTV_BEGIN_DATE <= '31-Aug-2003'
Run-time error '-2147217913 (80040e07)':
[Microsoft][ODBC Paradox Driver] Data type mismatch in criteria expression.
It basically seems to understand the upperbound of any criteria, i.e. = or < or BETWEEN, but any criteria if the syntax is correct returns rows below the lower bound.
WHERE ACTV_BEGIN_DATE >= #1-Aug-2003#
AND ACTV_BEGIN_DATE <= #31-Aug-2003#
Query returned correct results with rows dating (ACTV_BEGIN_DATE) from 26/08/2003 to 31/08/2003. Please note; there are no
records before 26/08/2003.
WHERE ACTV_BEGIN_DATE >= #1-Sep-2003#
AND ACTV_BEGIN_DATE <= #30-Sep-2003#
Query returned incorrect results with rows dating from 26/08/2003 to 21/08/2003.
2.
WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
AND ACTV_BEGIN_DATE <= '31-Aug-2003'
Run-time error '-2147217913 (80040e07)':
[Microsoft][ODBC Paradox Driver] Data type mismatch in criteria expression.
3.
WHERE ACTV_BEGIN_DATE BETWEEN '1-Aug-2003' AND '31-Aug-2003'
Run-time error '-2147217913 (80040e07)':
[Microsoft][ODBC Paradox Driver] Data type mismatch in criteria expression.
4.
WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
AND ACTV_BEGIN_DATE <= '31-Aug-2003'
Run-time error '-2147217913 (80040e07)':
[Microsoft][ODBC Paradox Driver] Data type mismatch in criteria expression.
It basically seems to understand the upperbound of any criteria, i.e. = or < or BETWEEN, but any criteria if the syntax is correct returns rows below the lower bound.
ASKER
In above message;
Query returned incorrect results with rows dating from 26/08/2003 to 21/08/2003
Should be;
Query returned incorrect results with rows dating from 26/08/2003 to 30/09/2003
Query returned incorrect results with rows dating from 26/08/2003 to 21/08/2003
Should be;
Query returned incorrect results with rows dating from 26/08/2003 to 30/09/2003
ASKER
I just tried;
WHERE (ACTV_BEGIN_DATE >= #1-Sep-2003#
AND ACTV_BEGIN_DATE <= #10-Sep-2003#)
The query returned records before 1/09/2003.
WHERE (ACTV_BEGIN_DATE >= #1-Sep-2003#
AND ACTV_BEGIN_DATE <= #10-Sep-2003#)
The query returned records before 1/09/2003.
Pls post your Date setting in Regional Setting.
ASKER
Short date sample: 4/11/2003
Short date format: d/MM/yyyy
Short date format: d/MM/yyyy
Hi ! try the code below :
Dim MySDate,MyEDate as date
Dim SDate,EDate as string
SDate='1-Sep-2003'
EDate='10-Sep-2003'
if isdate(sdate)=true then
mysdate=format(sdate,"mm/d
else
msgbox "Invalid Start date"
end if
if isdate(edate)=true then
myedate=format(edate,"mm/d
else
msgbox "Invalid Ending date"
end if
"select * from activity WHERE (ACTV_BEGIN_DATE >= #" & mysdate & "# " _
& AND ACTV_BEGIN_DATE <= #" & myedate & "#) "
Dim MySDate,MyEDate as date
Dim SDate,EDate as string
SDate='1-Sep-2003'
EDate='10-Sep-2003'
if isdate(sdate)=true then
mysdate=format(sdate,"mm/d
else
msgbox "Invalid Start date"
end if
if isdate(edate)=true then
myedate=format(edate,"mm/d
else
msgbox "Invalid Ending date"
end if
"select * from activity WHERE (ACTV_BEGIN_DATE >= #" & mysdate & "# " _
& AND ACTV_BEGIN_DATE <= #" & myedate & "#) "
Still Cannot ? Pls try the following:
change your regional setting to mm/dd/yyyy
and try again...
before you change regional setting,Pls read follow:
http://www.4guysfromrolla.com/webtech/041001-1.2.shtml
Go for Launch :
q(^ _ ^)p
change your regional setting to mm/dd/yyyy
and try again...
before you change regional setting,Pls read follow:
http://www.4guysfromrolla.com/webtech/041001-1.2.shtml
Go for Launch :
q(^ _ ^)p
ASKER
Sorry was in a meeting. This is the code I used, system still returned 26-Aug to 10-Sep.
Dim MySDate, MyEDate As Date
Dim SDate, EDate As String
SDate = "1-Sep-2003"
EDate = "10-Sep-2003"
If IsDate(SDate) = True Then
MySDate = Format(SDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
If IsDate(EDate) = True Then
MyEDate = Format(EDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
Call ConnectActivity("SELECT * " & _
"FROM ACTIVITY " & _
"WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
"AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
MsgBox rstActivity.RecordCount
Set dgActivity.DataSource = rstActivity
Dim MySDate, MyEDate As Date
Dim SDate, EDate As String
SDate = "1-Sep-2003"
EDate = "10-Sep-2003"
If IsDate(SDate) = True Then
MySDate = Format(SDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
If IsDate(EDate) = True Then
MyEDate = Format(EDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
Call ConnectActivity("SELECT * " & _
"FROM ACTIVITY " & _
"WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
"AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
MsgBox rstActivity.RecordCount
Set dgActivity.DataSource = rstActivity
ASKER
I changed the date format in regional settings to MM/dd/yyyy and ran the above code with the same result (returned 26-Aug to 10-Sep). I do not think that the regional settings will effect the query criteria as we are passing the date format in dd-MMM-yyyy which cannot be confused by the query engine.
wat is this "Call ConnectActivity" ?
a function ?
a function ?
ASKER
Yes, they call some procedures in my module modDatabase. These are the two that are applicable;
Public Sub ConnectDatabase()
Set adoConnection = New ADODB.Connection
With adoConnection
.ConnectionString = "Provider=MSDASQL.1;Persis
.Open
End With
End Sub
Public Sub ConnectActivity(ByVal pstrQuery As String)
Set rstActivity = New ADODB.Recordset
With rstActivity
.ActiveConnection = adoConnection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = pstrQuery
.Open
End With
End Sub
Public Sub ConnectDatabase()
Set adoConnection = New ADODB.Connection
With adoConnection
.ConnectionString = "Provider=MSDASQL.1;Persis
.Open
End With
End Sub
Public Sub ConnectActivity(ByVal pstrQuery As String)
Set rstActivity = New ADODB.Recordset
With rstActivity
.ActiveConnection = adoConnection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = pstrQuery
.Open
End With
End Sub
ASKER
In the ConnectDatabase() Procedure; the following ConnectionString also works;
"Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Parado
"Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Parado
how about your field type ? Date or string ?
ASKER
It is a Date field type.
Change below connection:
Public Sub ConnectActivity(ByVal pstrQuery As String)
Set rstActivity = New ADODB.Recordset
With rstActivity
.ActiveConnection = adoConnection
.CursorLocation = adUseClient
.CursorType = adOpenStatic 'change
.LockType = adLockReadOnly 'change
.Source = pstrQuery
.Open
End With
End Sub
And execute this command:
Set rstActivity=adoConnection.
"FROM ACTIVITY " & _
"WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
"AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) order by actv_begin_date")
Set dgActivity.DataSource = rstActivity
Public Sub ConnectActivity(ByVal pstrQuery As String)
Set rstActivity = New ADODB.Recordset
With rstActivity
.ActiveConnection = adoConnection
.CursorLocation = adUseClient
.CursorType = adOpenStatic 'change
.LockType = adLockReadOnly 'change
.Source = pstrQuery
.Open
End With
End Sub
And execute this command:
Set rstActivity=adoConnection.
"FROM ACTIVITY " & _
"WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
"AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) order by actv_begin_date")
Set dgActivity.DataSource = rstActivity
ASKER
I have executed this (returned no records);
Dim prstActivity As New ADODB.Recordset
Dim MySDate, MyEDate As Date
Dim SDate, EDate As String
SDate = "1-Sep-2003"
EDate = "10-Sep-2003"
If IsDate(SDate) = True Then
MySDate = Format(SDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
If IsDate(EDate) = True Then
MyEDate = Format(EDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
Set prstActivity = adoConnection.Execute("SEL
"FROM ACTIVITY " & _
"WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
"AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
Set dgActivity.DataSource = prstActivity
Dim prstActivity As New ADODB.Recordset
Dim MySDate, MyEDate As Date
Dim SDate, EDate As String
SDate = "1-Sep-2003"
EDate = "10-Sep-2003"
If IsDate(SDate) = True Then
MySDate = Format(SDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
If IsDate(EDate) = True Then
MyEDate = Format(EDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
Set prstActivity = adoConnection.Execute("SEL
"FROM ACTIVITY " & _
"WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
"AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
Set dgActivity.DataSource = prstActivity
ASKER
I have also executed this, with requested changes to ConnectActivity() (which return records from 26-Aug to 10-Sep);
Dim MySDate, MyEDate As Date
Dim SDate, EDate As String
SDate = "1-Sep-2003"
EDate = "10-Sep-2003"
If IsDate(SDate) = True Then
MySDate = Format(SDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
If IsDate(EDate) = True Then
MyEDate = Format(EDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
Call ConnectActivity("SELECT * " & _
"FROM ACTIVITY " & _
"WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
"AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
MsgBox rstActivity.RecordCount
Set dgActivity.DataSource = rstActivity
Dim MySDate, MyEDate As Date
Dim SDate, EDate As String
SDate = "1-Sep-2003"
EDate = "10-Sep-2003"
If IsDate(SDate) = True Then
MySDate = Format(SDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
If IsDate(EDate) = True Then
MyEDate = Format(EDate, "mm/dd,yyyy")
Else
MsgBox "Invalid Start date"
End If
Call ConnectActivity("SELECT * " & _
"FROM ACTIVITY " & _
"WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
"AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
MsgBox rstActivity.RecordCount
Set dgActivity.DataSource = rstActivity
Did you try change this :
SDate = "1-9-2003"
EDate = "10-9-2003"
SDate = "1-9-2003"
EDate = "10-9-2003"
ASKER
With the above changes, the query returns 26-Aug to Sep-10.
Getting Give Up :)
ASKER
Yeah I know, this is such a crazy problem. Thanks for your help MYLim. :)
1.Reserved work
("SELECT * " & _
"FROM [ACTIVITY] " & _
"WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
"AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
2.y2k
https://www.experts-exchange.com/questions/20308204/Retro-Y2K-failure.html?query=paradox+date+problem&searchType=all
3.reporting tools
Try to Use reporting tool such as Crystal report to select your data.Is output correct ?
("SELECT * " & _
"FROM [ACTIVITY] " & _
"WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
"AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
2.y2k
https://www.experts-exchange.com/questions/20308204/Retro-Y2K-failure.html?query=paradox+date+problem&searchType=all
3.reporting tools
Try to Use reporting tool such as Crystal report to select your data.Is output correct ?
Accepted Answer from supunr
Date: 06/29/2003 07:47PM PDT Accepted Answer
Private Sub cmdSearch_Click()
If rs.State = adStateOpen Then rs.Close
If From.Value = "" Or dtto.Value = "" Then
MsgBox "Please Enter valid dates ", vbCritical, "mbc"
Exit Sub
Else
If Not IsDate(From.Value) Then
MsgBox "Please enter a valid date in it", vbCritical, "mbc"
Exit Sub
ElseIf Not IsDate(dtto.Value) Then
MsgBox "Please enter a valid date in it", vbCritical, "mbc"
Exit Sub
End If
rs.Open "select *, (Format([date], ""dd/mmm/yyyy"")) as FormattedDate from movies where Val(Format([date],""YYYYMM
'rs.Open "select *, (Format([date], ""dd/mmm/yyyy"")) as FormattedDate from movies"
' if first does not work try commenting it out and try the second
If rs.RecordCount > 0 Then rs.MoveFirst
' press Ctrl+G before you run the code (or goto View->Immediate window) and this is the
' debug window. when you type debug.print, it prints to this immediate window
' similar to print, but print will print to the form itself.
Do While Not rs.EOF
Debug.Print rs.Fields("FormattedDate")
rs.MoveNext
Loop
If rs.RecordCount > 0 Then
rs.MoveFirst
Set DataGrid1.DataSource = rs
Else
MsgBox "Could not found Attendance between" & " " & From.Value & " and " & dtto.Value, vbCritical, "mbc"
End If
End Sub
Date: 06/29/2003 07:47PM PDT Accepted Answer
Private Sub cmdSearch_Click()
If rs.State = adStateOpen Then rs.Close
If From.Value = "" Or dtto.Value = "" Then
MsgBox "Please Enter valid dates ", vbCritical, "mbc"
Exit Sub
Else
If Not IsDate(From.Value) Then
MsgBox "Please enter a valid date in it", vbCritical, "mbc"
Exit Sub
ElseIf Not IsDate(dtto.Value) Then
MsgBox "Please enter a valid date in it", vbCritical, "mbc"
Exit Sub
End If
rs.Open "select *, (Format([date], ""dd/mmm/yyyy"")) as FormattedDate from movies where Val(Format([date],""YYYYMM
'rs.Open "select *, (Format([date], ""dd/mmm/yyyy"")) as FormattedDate from movies"
' if first does not work try commenting it out and try the second
If rs.RecordCount > 0 Then rs.MoveFirst
' press Ctrl+G before you run the code (or goto View->Immediate window) and this is the
' debug window. when you type debug.print, it prints to this immediate window
' similar to print, but print will print to the form itself.
Do While Not rs.EOF
Debug.Print rs.Fields("FormattedDate")
rs.MoveNext
Loop
If rs.RecordCount > 0 Then
rs.MoveFirst
Set DataGrid1.DataSource = rs
Else
MsgBox "Could not found Attendance between" & " " & From.Value & " and " & dtto.Value, vbCritical, "mbc"
End If
End Sub
do you have the latest MDAC install in your computer (ADO 2.7+) aslo make sure you have the latest jet servicepack installed as well. (Jet SP5)
ASKER
Hey MYLim, thanks for not giving up. I will check over all your comments when I get to work in the morning. :)
Take a look :)
http://oldlook.experts-exchange.com/questions/20413021/Date-Problem-into-database.html?query="date+problem"&searchType=all
https://www.experts-exchange.com/questions/20686807/Linkining-Paradox-Tables-in-Access-2000.html
https://www.experts-exchange.com/questions/10191629/query-date-problem.html?query="date+problem"&searchType=all
https://www.experts-exchange.com/questions/20100058/Date-problem.html?query="date+problem"&searchType=all
After that Pls check this, and configure it all as the format you want, be sure the first two of them to be the same:
1. Configure Date format in Access
2. Configure Date format on Control Panel, (the icon: PLANET EARTH, in Spanish Configuracion Regional) to be the same as you want
3. In your SQL query when you work with dates put this function DATE VALUE( date field) (remember it is part of the SQL query, not of VB code).
How about the look of date field inside your database.Pls post the format...
http://oldlook.experts-exchange.com/questions/20413021/Date-Problem-into-database.html?query="date+problem"&searchType=all
https://www.experts-exchange.com/questions/20686807/Linkining-Paradox-Tables-in-Access-2000.html
https://www.experts-exchange.com/questions/10191629/query-date-problem.html?query="date+problem"&searchType=all
https://www.experts-exchange.com/questions/20100058/Date-problem.html?query="date+problem"&searchType=all
After that Pls check this, and configure it all as the format you want, be sure the first two of them to be the same:
1. Configure Date format in Access
2. Configure Date format on Control Panel, (the icon: PLANET EARTH, in Spanish Configuracion Regional) to be the same as you want
3. In your SQL query when you work with dates put this function DATE VALUE( date field) (remember it is part of the SQL query, not of VB code).
How about the look of date field inside your database.Pls post the format...
ASKER
MyLim, thanks again for your help. However, the problem I am facing here has nothing to do with the date format. I am using the format dd-MMM-yyyy, cannot be misinterpreted by SQL. The regional settings will not affect it.
Jason.
Jason.
totally give up :)
ASKER
I am thinking I will need to use DTS in SQL Server to import the data and then work with it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.