Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access 2007 SQL Data Mismatch (Integer)

Posted on 2013-01-10
11
361 Views
Last Modified: 2013-01-14
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
Comment
Question by:jbakestull
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38765617
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38765619
What are data types for [Test], [Entry Entry Date], and [Previous Entry Exit Exit Date] in our table?
0
 

Author Comment

by:jbakestull
ID: 38765626
test is number, [Entry Entry Date] and [Previous Entry Exit Exit Date] are dates
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 33

Expert Comment

by:Norie
ID: 38765634
sSQL should be declared as String, no Integer.
0
 

Author Comment

by:jbakestull
ID: 38765638
string returns an data mismatch type, not sure how to write the SQL as an integer.
0
 

Author Comment

by:jbakestull
ID: 38765640
sorry, data mistype is at

rs("Test") = DateDiff("d", [Entry Entry Date], [Previous Entry Exit Exit Date])
0
 
LVL 33

Expert Comment

by:Norie
ID: 38765644
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
 

Author Comment

by:jbakestull
ID: 38765654
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
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 38765660
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38765713
How are you dealing with null values in you (presumably) date fields?
Can you post a sample database, with at least table structures?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38766520
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question