473,221 Members | 1,943 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 software developers and data experts.

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

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
5 2523
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

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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've...
3
by: Radu | last post by:
Hi. I have lots of processing to do on the server - from the client (Access) I call a sproc which returns a recordset (the sproc is essentially a big "select"). With the obtained data , I need to...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
2
by: google | last post by:
Hello everyone, I am having an issue using the "Multi Select" option in a list box in MS Access 2003. I am making a form that users can fill out to add an issue to the database. Each issue can...
4
by: bill salkin | last post by:
The code below creates a dataset containing a table called "Customers" from the Northwind database Later on in my code, after the database connection is closed, I need to access this dataset to...
2
by: cephelo | last post by:
I have no problems outputting the attribute value when the node is in context, for example, @id when an <status> node is in context. However, I am having trouble outputting it in a <xsl:value-of...
1
by: U S Contractors Offering Service A Non-profit | last post by:
Craig Somerford to New, Harvard, (bcc:Natural) More options 7:56 pm (0 minutes ago) " Working in Faith " " SNOWING over New York City Today November 7th 2006 "
1
by: U S Contractors Offering Service A Non-profit | last post by:
" Mentor-ship applied for November 8th 2006 " Craig Somerford to Harvard, Apple, Google, (bcc:Natural), (bcc:Matthew), (bcc:National), (bcc:Letters) Hide options 9:41 am (0 minutes ago) From:...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.