Access Databse "Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"

[Henning M]

Hi all,

I having some problems with Access and selecting records between dates..
When I try this in access, it works fine!!

"Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"

But when I try it from my vb.net app, I get ALL the records in the tabel??
What goes wrong?
I haven't been able to find any info on the net, besides others having
problems of the same nature..
I have tried to change the date format to "MM/DD/YYY" but with same result!!

Hope someone out there knows what is goint on

Thanks

Henning

 

[Phill W.]

I having some problems with Access and selecting records between dates..
When I try this in access, it works fine!!

"Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"

But when I try it from my vb.net app, I get ALL the records in the tabel??

Are you /certain/ that you're using the /same/ SQL in both cases?

It's not even that your dates could be interpreted differently between
Access and your application so you'd get different results (#1/5/2006#?
1st of May or Jan 5th?) - there's no month 31 (yet)!

In your application, [always] print out (or log) your SQL before
executing it.

Next question (from someone with many, many Data Sources on their PC) -
Are you certain that you're using the same /database/ in both cases.
I've lost /hours/ trying to match up dodgy data only to find I've been
looking in completely the wrong database!

HTH,
Phill W.

 

[jeff]

try changing your format to yyyy/mm/dd for the dates and see if that works
....

I have this thing in the back of my head reminding that some time ago ... a
long time ago, 10 yrs or so ... that access interpretes dates differently in
the query builder than odbc / jet access ... When coding things in access
vba, I had to make such that in any dynamically created SQL statement the
date formats were always yyyy/mm/dd. I am not sure if this is correct now
or not, but for the past 10 yrs ... I have been formating dates to
yyyy/mm/dd when building dynamic SQL against any database - ORACLE, SQL
Server, Access, Sybase and so on ... and have not had any problems - does
not matter what the user's regional date settings are ... it always works
for me - and where available, I set the date time formats in the connect
string ... for mssql server ... <DateTimeFormat=''\'yyyy-mm-dd
hh:mm:ss\'',DateFormat=''\'yyyy-mm-dd\'", TimeFormat=''\'hh:mm:ss'">. I may
be wrong, but it works and is simple to implement ...

Food thought.... if you are generating the SQL Dynamically, I would create a
class that translates the 'date' portion of the where clause to a string for
you ... the reason for this is, if you ever decide to move your database to
MSSQL Sever, the '#' will fail all your date parameters...

create a class...

clsConvertDateToString

Function fDateToString(byVal adtmDate as Date) as String

Dim lsReturn as String
Dim lsEnclosure as String

Select Case <typeof Connection>
Case "ACCESS"
lsEnclosure = "#"
Case "MSSQL"
lsEnclosure = "#"
...
End Select

lsReturn = lsEnclosure + adtmDate.ToString("s").SubString(0,10) +
lsEnclosure

RETURN lsReturn

END

If you need to include the time portion of the date, either overload the
function or create another function....

Function fDateTimeToString

....

Also, since dates are stored as Date / Time fields in Access...if you have a
time portion of the date, you need to worry about missing records on the
last day using the between function...

2006 / 01 / 31 ... assume a 12:00am time ... if you have a record with 2006
/ 01 / 31 1:30am ... your query will not pick this up ... you will need to
append times to the end of your date ...
ie dtmDate Between #2006/01/01 00:00:00# and #2006/01/31 23:59:59#

to ensure you get all the records you are after ... again, only a concern if
you store the time along with the date in the table ...
you can do this by adding a few more functions...
Function fStartDateToString (adtmDate as Date) as string
Function fEndDateToString (adtmDate as Date) as string
....

jeff.

 

[tommaso.gastaldi]

Access wants #USAformat# . Does not vary with culture.

-t

Henning M ha scritto:

 

[jeff]

oops the line

Case "ACCESS"
lsEnclosure = "#"
Case "MSSQL"
lsEnclosure = "#"
...
End Select

Case "MSSQL"
lsEnclosure = "'"

is the proper format...dates enclosed with a quotation mark...

 

[Henning M]

Thanks all for the quick respons..

As Phill W suggested using the SAME Database when trying out my Sql
statements and when running the app helped, and the last thing was the
format change from "ddmmyyyy" to "mmddyyyy"

Thanks all

Henning M

PS- Thanks Jess for the information about Access Vs. MSsql # as I later, DO
want the app to run with MSSql

I having some problems with Access and selecting records between dates..
When I try this in access, it works fine!!

"Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"

But when I try it from my vb.net app, I get ALL the records in the
tabel??

Are you /certain/ that you're using the /same/ SQL in both cases?

It's not even that your dates could be interpreted differently between
Access and your application so you'd get different results (#1/5/2006#?
1st of May or Jan 5th?) - there's no month 31 (yet)!

In your application, [always] print out (or log) your SQL before executing
it.

Next question (from someone with many, many Data Sources on their PC) -
Are you certain that you're using the same /database/ in both cases.
I've lost /hours/ trying to match up dodgy data only to find I've been
looking in completely the wrong database!

HTH,
Phill W.