jbakestull
asked on
Access 2007 SQL Data Mismatch (Integer)
When I try to run code below, I receive a data mismatch at:
sSQL = "SELECT * FROM tblNewlyHomeless ORDER BY [Client Uid], [Test], [Entry Entry Date], [Previous Entry Exit Exit Date]"
Public Function Test()
Dim sSQL As Integer
DAO.DBEngine.SetOption dbMaxLocksPerFile, 1500000
sSQL = "SELECT * FROM tblNewlyHomeless ORDER BY [Client Uid], [Test], [Entry Entry Date], [Previous Entry Exit Exit Date]"
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(sS
Do Until rs.EOF
rs.Edit
rs("Test") = DateDiff("d", [Entry Entry Date], [Previous Entry Exit Exit Date])
rs.Update
rs.MoveNext
Loop
End Function
Not sure why, need some assistance
sSQL = "SELECT * FROM tblNewlyHomeless ORDER BY [Client Uid], [Test], [Entry Entry Date], [Previous Entry Exit Exit Date]"
Public Function Test()
Dim sSQL As Integer
DAO.DBEngine.SetOption dbMaxLocksPerFile, 1500000
sSQL = "SELECT * FROM tblNewlyHomeless ORDER BY [Client Uid], [Test], [Entry Entry Date], [Previous Entry Exit Exit Date]"
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(sS
Do Until rs.EOF
rs.Edit
rs("Test") = DateDiff("d", [Entry Entry Date], [Previous Entry Exit Exit Date])
rs.Update
rs.MoveNext
Loop
End Function
Not sure why, need some assistance
What are data types for [Test], [Entry Entry Date], and [Previous Entry Exit Exit Date] in our table?
ASKER
test is number, [Entry Entry Date] and [Previous Entry Exit Exit Date] are dates
sSQL should be declared as String, no Integer.
ASKER
string returns an data mismatch type, not sure how to write the SQL as an integer.
ASKER
sorry, data mistype is at
rs("Test") = DateDiff("d", [Entry Entry Date], [Previous Entry Exit Exit Date])
rs("Test") = DateDiff("d", [Entry Entry Date], [Previous Entry Exit Exit Date])
This returns a type mismatch?
Or do you mean the error moves further down the code?
Dim sSQL As String
DAO.DBEngine.SetOption dbMaxLocksPerFile, 1500000
sSQL = "SELECT * FROM tblNewlyHomeless ORDER BY [Client Uid], [Test], [Entry Entry Date], [Previous Entry Exit Exit Date]"Or do you mean the error moves further down the code?
ASKER
When sSQL is a integer the data mismatch occurs at line:
sSQL = "SELECT * FROM tblNewlyHomeless ORDER BY [Client Uid], [Test], [Entry Entry Date], [Previous Entry Exit Exit Date]"
When sSQL is a string the data mismatch occurs at line:
rs("Test") = DateDiff("d", [Entry Entry Date], [Previous Entry Exit Exit Date])
sSQL = "SELECT * FROM tblNewlyHomeless ORDER BY [Client Uid], [Test], [Entry Entry Date], [Previous Entry Exit Exit Date]"
When sSQL is a string the data mismatch occurs at line:
rs("Test") = DateDiff("d", [Entry Entry Date], [Previous Entry Exit Exit Date])
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How are you dealing with null values in you (presumably) date fields?
Can you post a sample database, with at least table structures?
Can you post a sample database, with at least table structures?
I think imnorie got it right, you cannot just refer to the field name, you must use the recordset object and the field name.
The only other thing I can think of is that there are records where either [Entry Entry Date] or [Previous Entry Exit Exit Date] is a null value. If that is possible, then consider something like:
rs("Test") = DateDiff("d", NZ(rs("Entry Entry Date"), #1/1/2000#), NZ(rs("Previous Entry Exit Exit Date"), Date()))
The only other thing I can think of is that there are records where either [Entry Entry Date] or [Previous Entry Exit Exit Date] is a null value. If that is possible, then consider something like:
rs("Test") = DateDiff("d", NZ(rs("Entry Entry Date"), #1/1/2000#), NZ(rs("Previous Entry Exit Exit Date"), Date()))
BTW, those are some really strange looking field names!