Re: Access Databse "Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"
- From: "jeff" <jhersey at allnorth dottt com>
- Date: Wed, 19 Jul 2006 07:21:21 -0700
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" <henning@xxxxxxxxx> wrote in message
news:2403c$44be0055$3e3d8433$15022@xxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- References:
- Prev by Date: Re: Argument not specified
- Next by Date: Re: Access Databse "Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"
- Previous by thread: Re: Access Databse "Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"
- Next by thread: Re: Access Databse "Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"
- Index(es):
Relevant Pages
|
