Noobie question about Dates and Between or >= And <=

[tenderfoot]

Hello there,

I have been bashing my head off of my laptop trying to get a query to return records where a date field is between two dates.

1. The field has a datatype of date/time
2. There are only two records in the database where the field Client.Refdate of record one is equal to 05/12/2007 and record two is equal to 05/01/2008.
3. The SQL was like this:

SELECT Client.clientMarital, Client.clientFirstname, Client.clientMiddlename, Client.clientSurname, Client.clientGender, Client.clientAddress1, Client.clientPostcode, Client.clientRefdate
FROM Client
WHERE (((Client.clientRefdate)>(#04/12/2007#) And (Client.clientRefdate)<(#06/12/2007#)));

However when I ran the query no results are returned.

I searched the forums and found this post: http://www.access-programmers.co.uk/forums/showthread.php?t=139619&highlight=dates

So amended the sql like so but to no avail:

SELECT Client.clientMarital, Client.clientFirstname, Client.clientMiddlename, Client.clientSurname, Client.clientGender, Client.clientAddress1, Client.clientPostcode, Client.clientRefdate
FROM Client
WHERE (((Client.clientRefdate)>DateAdd("d",1,#04/12/2007#) And (Client.clientRefdate)<DateAdd("d",1,([Client].[clientRefdate])<#06/12/2007#)));

Can anyone point out my error, or any potential things I have missed...I am going bonkers!


tenderfoot

 

[tenderfoot]

Ah hah!

Now I noticed something, when I type the date into the "Criteria" field in design view of the query I type this #04/12/2007#, but after I run the query and vuew the SQL the date is like this #12/04/2007#....
Could this be my issue?

How can I make the date stay in a UK format?


Cheers,


Tenderfoot

 

[tenderfoot]

Dont worry guys,

I have played with the sql and realised that the dates have to be entered as a parameter query in a US format! It was the join type that was causing me an issue also! As I was joining the tables on an inner join, no results were displayed, even though I was now entereing dates in US format.

SELECT Count(*) AS [Any Service Clients]
FROM (((Client LEFT JOIN faces ON Client.clientId = faces.facesClientId) LEFT JOIN health ON Client.clientId = health.healthClientId) LEFT JOIN maternity ON Client.clientId = maternity.matClientId) LEFT JOIN moveon ON Client.clientId = moveon.moveonClientId
WHERE (((Client.clientRefdate) Between [Enter Start Date] And [Enter End Date]));

Hope this helps someone somewhere anyway!

 

[tenderfoot]

Actually, I shot my mouth of too soon - further problem!

OK, say I have a record in my database such as 05/12/2007 (this needs to be interpreted as 12/05/2007)

My SQL is like this:

SELECT Count(*) AS [Any Service Clients]
FROM (((Client LEFT JOIN faces ON Client.clientId = faces.facesClientId) LEFT JOIN health ON Client.clientId = health.healthClientId) LEFT JOIN maternity ON Client.clientId = maternity.matClientId) LEFT JOIN moveon ON Client.clientId = moveon.moveonClientId
WHERE (((Client.clientRefdate)>=[Enter Start Date] And (Client.clientRefdate)<=[Enter End Date]));

However id I type into the parameter box start dates like "12/04/2007" and "12/06/2007", the count is zero. Why? I tried adding DateAdd("d", 1,) but this did not work.

Any ideas?


Tenderfoot

 

[JoeyY]

Not sure if this will help, but...

Have you tried using the Format function?

ex. Format([Enter Start Date],"dd/mm/yy")

 

[tenderfoot]

Wow, thanks JoeyY, that sorted the format issue and the incorrect results!