By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 1,964 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Jul 19 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Henning M wrote:
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.
Jul 19 '06 #2

P: n/a

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.
"Henning M" <he*****@fys.ku.dkwrote in message
news:24***************************@news.arrownet.d k...
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

Jul 19 '06 #3

P: n/a
Access wants #USAformat# . Does not vary with culture.

-t

Henning M ha scritto:
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
Jul 19 '06 #4

P: n/a
oops the line

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

is the proper format...dates enclosed with a quotation mark...
"jeff" <jhersey at allnorth dottt comwrote in message
news:es**************@TK2MSFTNGP04.phx.gbl...
>
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.
"Henning M" <he*****@fys.ku.dkwrote in message
news:24***************************@news.arrownet.d k...
>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


Jul 19 '06 #5

P: n/a
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

"Phill W." <p-.-a-.-w-a-r-d@o-p-e-n-.-a-c-.-u-kwrote in message
news:e9**********@yarrow.open.ac.uk...
Henning M wrote:
>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.

Jul 19 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.