Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

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(sSQL)
 
 
  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
0
jbakestull
Asked:
jbakestull
  • 4
  • 3
  • 2
  • +2
1 Solution
 
Dale FyeCommented:
First guess is that your [test] field is a string, not a number, DateDiff will return a number, so [test] must be numeric.

BTW, those are some really strange looking field names!
0
 
IrogSintaCommented:
What are data types for [Test], [Entry Entry Date], and [Previous Entry Exit Exit Date] in our table?
0
 
jbakestullAuthor Commented:
test is number, [Entry Entry Date] and [Previous Entry Exit Exit Date] are dates
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
NorieCommented:
sSQL should be declared as String, no Integer.
0
 
jbakestullAuthor Commented:
string returns an data mismatch type, not sure how to write the SQL as an integer.
0
 
jbakestullAuthor Commented:
sorry, data mistype is at

rs("Test") = DateDiff("d", [Entry Entry Date], [Previous Entry Exit Exit Date])
0
 
NorieCommented:
This returns a type mismatch?
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]"

Open in new window


Or do you mean the error moves further down the code?
0
 
jbakestullAuthor Commented:
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])
0
 
NorieCommented:
Where are [Entry Entry Date] and [Previous Entry Exit Exit Date] coming from?

Are they supposed to be from the recordset you've opened?
rs("Test") = DateDiff("d", rs("Entry Entry Date"), rs("Previous Entry Exit Exit Date")) 

Open in new window

0
 
jerryb30Commented:
How are you dealing with null values in you (presumably) date fields?
Can you post a sample database, with at least table structures?
0
 
Dale FyeCommented:
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()))
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now