Search between 2 dates in a column (1 Viewer)

chizzy42

Registered User.
Local time
Today, 22:51
Joined
Sep 28, 2014
Messages
97
Hi, Happy new year and hope you're all well. I have a query searching between 2 dates, it was working ok last year but i think its not working due to formatting now we're in 2022(?). The query I have is :

Code:
Between DMin("[registered]","tblRma") And DMax("[registered]","tblRma")

where I'm looking to search for the min and max dates in a column (that aren't closed, that's taken care of in the query)
The only formatting examples I've seen are of the type

Code:
<#2/2/2012# or >#2/4/2012#

and not of the type using the field in a query. Im using UK format dd/mm/yyyy

Could someone let me know please if this looks like I'm using the query wrong

regards
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:51
Joined
May 7, 2009
Messages
16,129
it looks OK.

you can also hardcode it, since you are using the Earliest up to Latest records.

([closeField] = False) And ([registered] Between #1/1/1890# And #12/31/9999#)

or simply Leave it:

([closeField] = False)

if you are only interested on the Current year:

[registered] Between DateSerial(Year(Date()), 1, 1) And DateSerial(Year(Date()), 12, 31)
 

chizzy42

Registered User.
Local time
Today, 22:51
Joined
Sep 28, 2014
Messages
97
HI arenelgp thanks for the quick reply. The data I'm getting isn't matching to what I'm after. I was looking for the oldest top 15 in the query and I'm still getting the wrong data.
Here's the query I'm using to get the top 15 oldest

Code:
SELECT TOP 15 tblRma.text1, tblRma.text2, tblRma.text3, tblRma.text4, tblRma.comments, tblRma.registered, Round((Date()-[registered])) AS daysopen, tblRma.removed, tblRma.text5, tblRma.Line
FROM tblRma
WHERE (((tblRma.registered) Between DMin("[registered]","tblRma") And DMax("[registered]","tblRma")) AND ((tblRma.removed)=No))
ORDER BY tblRma.text4;

I seem to be getting recent data from this year instead of the oldest from last year, looking at the snapshots the table is showing the oldest being back to the 6th dec 15th and 23rd yet im picking up dat that has been recently entered.

regards
 

Attachments

  • table.gif
    table.gif
    23.2 KB · Views: 104
  • old.gif
    old.gif
    6.8 KB · Views: 107

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:51
Joined
May 7, 2009
Messages
16,129
what FielType is field "removed"?
if Yes/No, then you compare it with -1 (True) Or 0 (False)

... AND ((tblRma.removed)=0))
Or
... AND ((tblRma.removed)=False))



if you are looking for Last Year data:

SELECT TOP 15 tblRma.text1, tblRma.text2, tblRma.text3, tblRma.text4, tblRma.comments, tblRma.registered, Round((Date()-[registered])) AS daysopen, tblRma.removed, tblRma.text5, tblRma.Line
FROM tblRma
WHERE (((tblRma.registered) Between DMin("[registered]","tblRma", "Year([registered]) = Year(Date())-1") And DMax("[registered]","tblRma", "Year([registered]) = Year(Date())-1")) AND ((tblRma.removed)=False))
ORDER BY tblRma.text4;
 
Last edited:

chizzy42

Registered User.
Local time
Today, 22:51
Joined
Sep 28, 2014
Messages
97
Thanks again arnelgp for the help . i also changed the ORDER BY tblRma.text4; to ORDER BY tblRma.registered;
 

Users who are viewing this thread

Top Bottom