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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 com> wrote in message
news:espDe6zqGHA.4960@xxxxxxxxxxxxxxxxxxxxxxx

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







.