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\'',Dat eFormat=''\'yyy y-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...
clsConvertDateT oString
Function fDateToString(b yVal 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.ToStri ng("s").SubStri ng(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 fDateTimeToStri ng
....
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 fStartDateToStr ing (adtmDate as Date) as string
Function fEndDateToStrin g (adtmDate as Date) as string
....
jeff.
"Henning M" <he*****@fys.ku .dkwrote in message
news:24******** *************** ****@news.arrow net.dk...
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
