473,886 Members | 2,366 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Date parameters in queries

Hello

I'm using Access 2003, and I have a query (written in SQL) which has
two parameters and asks the user for two dates. This has been working
fine. Today I modified the query, removing the paramerters and hard-
coding some dates in, in dd/mm/yy format. To my surprise I got a
different number of records returned. I then, as an experiment, change
the date format to dd/mm/yyyy. This again returned a different number
of records, different to both previous instances. When I use the
parameters I get the same result, irrespective of the date format
used. Has anybody come across anything liek this before - I've
searched here and can't see and previous postings, but apologies if
this has been covered before. The query is only using one table, so
it's nothing particularly complicated. The full query (with
parameters) is pasted below.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT q01_Clients.*
FROM q01_Clients
WHERE (
(
q01_Clients.clt _A11c_AuthFormR ecDate<=[Start Date]
AND
clt_A21a_Record Type=10
AND
(
clt_A21_RecordS tatus=10 OR (clt_A23_DateCl osed>=[Start Date])
)
AND
(
(q01_Clients.cl t_A24_Departmen t=10 AND clt_A98_Transfe rred = FALSE)
OR
(q01_Clients.cl t_A24_Departmen t=10 AND clt_A99_DateTra nsferred
>=[Start Date])
OR
(q01_Clients.cl t_A24_Departmen t=20 AND
(clt_A45_Resett lementOpenDate> =[Start Date] OR
clt_A45_Resettl ementOpenDate is NULL))
))
OR
(
q01_Clients.clt _A11c_AuthFormR ecDate BETWEEN [Start Date] AND [End
Date] AND q01_Clients.clt _A21a_RecordTyp e=10
));

Many thanks in anticipation!

Stephen

Oct 12 '07 #1
2 2369
When you hard code it in the SQL are you wrapping it in #ddmmyyyy#?

Also, I believe access ALWAYS stores dates in American format, mmddyyyy
regardless of your localization settings.....ah h I found it...heh its even
got a reference to the access legend who I learned it from...

Function SQLDate(varDate As Variant) As String
'Got this from Allen Browne master of Access
'http://users.bigpond.n et.au/abrowne1/ser-36.html
'Access forcess american date formate for all SQL!
'#mm/dd/yyyy hh:mm:ss#
If IsDate(varDate) Then
SQLDate = "#" & Format$(varDate , "mm\/dd\/yyyy") & "#"
End If
End Function

Hope that helps....

"Stevienash aa" <st**********@y ahoo.co.ukwrote in message
news:11******** **************@ k35g2000prh.goo glegroups.com.. .
Hello

I'm using Access 2003, and I have a query (written in SQL) which has
two parameters and asks the user for two dates. This has been working
fine. Today I modified the query, removing the paramerters and hard-
coding some dates in, in dd/mm/yy format. To my surprise I got a
different number of records returned. I then, as an experiment, change
the date format to dd/mm/yyyy. This again returned a different number
of records, different to both previous instances. When I use the
parameters I get the same result, irrespective of the date format
used. Has anybody come across anything liek this before - I've
searched here and can't see and previous postings, but apologies if
this has been covered before. The query is only using one table, so
it's nothing particularly complicated. The full query (with
parameters) is pasted below.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT q01_Clients.*
FROM q01_Clients
WHERE (
(
q01_Clients.clt _A11c_AuthFormR ecDate<=[Start Date]
AND
clt_A21a_Record Type=10
AND
(
clt_A21_RecordS tatus=10 OR (clt_A23_DateCl osed>=[Start Date])
)
AND
(
(q01_Clients.cl t_A24_Departmen t=10 AND clt_A98_Transfe rred = FALSE)
OR
(q01_Clients.cl t_A24_Departmen t=10 AND clt_A99_DateTra nsferred
>>=[Start Date])
OR
(q01_Clients.cl t_A24_Departmen t=20 AND
(clt_A45_Resett lementOpenDate> =[Start Date] OR
clt_A45_Resettl ementOpenDate is NULL))
))
OR
(
q01_Clients.clt _A11c_AuthFormR ecDate BETWEEN [Start Date] AND [End
Date] AND q01_Clients.clt _A21a_RecordTyp e=10
));

Many thanks in anticipation!

Stephen

Oct 14 '07 #2
Thanks John (and Allen!). I've now recoded the dates the other way
around (they were already in #'s) and it works a treat. It's obvious
when you know - I'd thought I'd changed all date settings to UK
format, but I didn't realise that it did this - so thanks very much
for taking the time to sort this for me.

Stephen

Oct 15 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2104
by: jmev7 | last post by:
I had a query with a criteria in a date field reading "> And <", and when run, it correctly prompted first for the param, and then for the param. I then created functions to replace these params using an input box so that I could control the default date in each case (based on the current day of week and other variables). Each function works correctly, but for some reason, the first prompt is for , and the second prompt is for , which...
2
2820
by: Adam | last post by:
Hi All, This may be a really obvious thing that I'm missing ... but if anyone can help, I'd appreciate it. I have MS Access 2000: I'm using it for a CRM type database. I have a table with names, date they first became a customer, and about 3 fields of addional info. I.e.
7
2468
by: Nicolae Fieraru | last post by:
Hi All, I have a table tblProducts where I have four fields:\ Index, ProductName, EnterDate (as Date/Time - Medium Date), PurchaseDate (Date/Time - Medium Date) The EnterDate is automatically filled (with Now()) and the purchase date is entered manually. Meantime I became aware that instead of Now() I should use Date() for the date
7
21652
by: Zlatko Matić | last post by:
Let's assume that we have a database on some SQL server (let it be MS SQL Server) and that we want to execute some parameterized query as a pass.through query. How can we pass parameters to the server ? Is it possible to use parameters in pass-through queries ? An additional question: Is it possible to connect to a database on MySQL or PostgreSQL using ADO ? Is it possible to execute pass-through queries with parameters, using ADO...
6
17937
by: Tony Miller | last post by:
All I have an aggregate query using the function Month & Year on a datereceived field ie: TheYear: Year() TheMonth: Month() These are the group by fields to give me a Count on another field by year & month When I try to place a date filter 'Between x And y ' on an expression field
9
6030
by: mharrison | last post by:
Hello, I am developing a small java web-based car-pool booking system app which interacts with an access database. I am trying to write 2 queries: The first which will specify whether a given car is available on a given date range e.g. from: 1/12/05 to 12/12/05. the second which will run if the first query is unsuccessful e.g. a list of other cars available on the chosen dates. I have been looking at a Microsoft page which I believe may help...
6
1901
by: Mark | last post by:
Hi, i have an application which works with date. The regional settings of the computer (XP prof. dutch version) are set to French (Belgium). Asp.net and Sql server take the short date format of the regional settings (e.g. 2/08/2007 or 13/08/2007). I checked both: that's ok. When i try to insert a date in a datetime field in sql server which is e.g.
2
3407
by: Jim Devenish | last post by:
I wish to create a crosstab query as the record source for a report. It needs to count data between selected dates which are entered by the user in a popup window. The following Select query works: SELECT Tasks.EnquirySourceID, Tasks.BusinessUnitID, Count(Tasks.TaskID) AS CountOfTaskID FROM Tasks WHERE (((Tasks.TaskDate)>=!!)) GROUP BY Tasks.EnquirySourceID, Tasks.BusinessUnitID;
3
2758
by: RoadRunner | last post by:
Hi, I am having a problem. I have a very simple employee database. The client needs to see everything on a form before any updates or deletions can be made. I have a form that loads with two parameters (date range) a start date and a end date. I have two queries - Separation Update (adds to the separation table) and Separation Delete (deletes from the main table), they both run with the same parameters as the form. Is there a way that...
0
9957
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10770
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10873
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10430
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9593
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5809
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6010
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4632
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4236
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.