Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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