SQL Date 2

[MattSTech]

This one gives me the error "data mismatch in criteria expression"

strSQL = "SELECT * FROM tblSched WHERE (APPROVED=0) AND (sDate BETWEEN '" & dteDate & "' AND '" & dteDate2 & "')"

This one does not

strSQL = "SELECT * FROM tblSched WHERE (sdate BETWEEN '" & strTemp & "' AND '" & DateAdd("d", -1, DateAdd("m", 1, strTemp)) & "')"

This leads me to believe that the dates need to be strings. However, when I do this, (or remove the single quotes) the statement returns no records.
What is wrong with the first statement that I am not seeing?

 

[Bluejay07]

What is the data type of sDate in SQL?

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

Also, what is the value of strTemp?
If you have a date separator, such as '/' then the single quote is required.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Andrzejek]

It would help if you would do either in Immediate window:

? strSQL

and hit Enter, copy and paste what your strSQL looks like, or do

Debug.Print strSQL

Have fun.

---- Andy

 

[Andrzejek]

... forgot about...

Is it Access, SQL Server, Oracle, other DB?

Have fun.

---- Andy

 

[MattSTech]

I changed the data type to a string to test just a bit ago. It now works with the single quotes, but it still returns no records.

From the debug window

SELECT * FROM tblSched WHERE sDate BETWEEN '03/01/2011 12:00:00 AM' AND '05/30/2011 11:59:00 PM'

The SQL statement I am having problems with is in Access. The one that works as posted in the OP is in SQL.

Thanks for the assist.

 

[PWise]

if this is a linked table in access use # as date delimiters

SELECT * FROM tblSched WHERE sDate BETWEEN #03/01/2011 12:00:00 AM# AND #05/30/2011 11:59:00 PM#

 

[gmmastros]

IF you are running the query against an Access database, then you should use the # symbol for your date delimiters. With Microsoft SQL Server, you want to use single-quotes '.

Personally, I cringe any time I see someone use the between operator with dates. I used to do this a lot until I had a problem and learned a better way to handle things.

You see... in your query (with the between operator), you could potentially be missing rows from the table where the sdate happens to be within the last minute of the day.

It's better to use 2 conditions with a less than, like this:

SELECT * 
FROM   tblSched 
WHERE  sDate >= '03/01/2011 12:00:00 AM' 
       AND sDate < '06/01/2011 12:00:00 AM'

Note that I changed the Between operator to be 2 conditions and I changed the 2nd condition to < (12am of the next day).

The performance of the 2 queries will be the same, but the query I recommend will always work and return the data you want.

This problem is actually much worse in SQL Server because it depends on the data type. I've seen people use 11:59:59 PM which actually rounds to the next day in sql server. I've seen people use 11:59:57 PM which actually rounds to the next day if you are using the SmallDateTime data type. If you are using DateTime2, which offers more precision, then there is another time you "could" use. However, if you re-work the query the way I suggest, it works for all conditions, doesn't depend on the data types, and executes in the same amount of time as the between operator.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MattSTech]

Will the # work in SQL as well? I am trying to write the code for when I upsize the database to SQL in the future.

I will avoid the betweens! thanks a ton for digging in on this one.

 

[PWise]

Sorry but # does not work in SQL Server

 

[TLowder]

I am trying to write the code for when I upsize the database to SQL (Server) in the future."
I've found cross database sql statements are not so easy. The right focus from the begining does make it easier. But from Access to anything may not be as "easy". You'll want to look into sql standards. For instance I went from Access(for a split second till I saw many problems) to MySQL to PostgreSQL. Forget I metioned Access because it doesn't apply to the following. Most sql's I had in my MySQL code worked for both but I used a few MySQL specific syntax's that were not standard, so I changed as much as I could to work with both. I also was able to add some MySQL syntax to "functions" in PostgreSQL. Bottom line, I'm still haunted by writing new code to work with both databases. For the most part it is just structure changes that I need seperate code.
I would first look at why are you using Access in the first place? Is it already implemented where you work, or for this app etc? If it is a new app and no need for Access, I recomend PostgreSQL as a good free db. This is an entirely different discussion that could go on and on, but first think about why Access? Especialy if you already see SQL Server in the future? If SQL Server is a necesity in the future, then perhaps look into their free version for now. Its better to pick the right one now than try to make both work simultaneously down the road.

Tom

 

[gmmastros]

I was in a similar situation. I first started my app 13 years ago. At the time, it was VB6 and Access. Several years later, I decided to use SQL server, so there was 1 version of my app that supported both versions (Access and SQL Server). After that version, I went SQL Server all the way. The free versions of SQL Server are easy to install and use.

So... in my app....

The installer has 3 options: Server, Client, and Database Only.

If the customer already has a sql server, they install the Database Only option on their server, which simply copies the DB, makes it "not read-only" and attaches it to their SQL Server instance.

The Server option installs SQL Express and the database.

The Client option installs the VB6 app and it's associated DLL's and OCX's.

The beauty of this approach is, they get a free version of the database engine if they don't already own SQL Server. If they want to put my DB on an existing instance of SQL Server, they can. If they choose a full version of SQL Server, they must purchase it separately.

Since my customers are school districts, they qualify for lower prices from Microsoft and can purchase SQL Server Standard (or Enterprise) cheaper than I can resell it to them, so it works out well for everybody.

One tip.... if you choose to follow this route, I would encourage you to install the free database as a named instance. We use our company name as the named instance. This makes it easier and more palatable for IT people at the customer site to give elevated SQL Permissions for doing things like copying a database and adding additional users.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom