selecting date in query (1 Viewer)

murray83

Games Collector
Local time
Today, 21:47
Joined
Mar 31, 2017
Messages
737
have a simple question i think, this is in my current query

Between Date() And Date()-6

and it works fine and selects stuff between the dates so for example stuff from 1/11/23, BUT

I want it to NOT select these so that the same word ( as its for a hangman game ) is not picked for that week again, example is attached

i did try adding NOT to the start of the function but of course not that easy
 

Attachments

  • hangman.zip
    911.4 KB · Views: 67

cheekybuddha

AWF VIP
Local time
Today, 21:47
Joined
Jul 21, 2014
Messages
2,329
I can't test your db since it's in a too new version of Access for me, but you can try:
SQL:
SELECT TOP 1
  WordID,
  WordText,
  Rnd([WordID]*Timer()*-1) AS RndWordNumber,
  DatePicked
FROM WordT
WHERE DatePicked > Date()
  AND DatePicked < Date() - 6
  AND WordLen > 1
  AND WordLen < 6
ORDER BY Rnd([WordID]*Timer()*-1);
 

murray83

Games Collector
Local time
Today, 21:47
Joined
Mar 31, 2017
Messages
737
I can't test your db since it's in a too new version of Access for me, but you can try:
SQL:
SELECT TOP 1
  WordID,
  WordText,
  Rnd([WordID]*Timer()*-1) AS RndWordNumber,
  DatePicked
FROM WordT
WHERE DatePicked > Date()
  AND DatePicked < Date() - 6
  AND WordLen > 1
  AND WordLen < 6
ORDER BY Rnd([WordID]*Timer()*-1);

ok ill give it a go, cheers
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:47
Joined
Sep 21, 2011
Messages
14,472
have a simple question i think, this is in my current query

Between Date() And Date()-6

and it works fine and selects stuff between the dates so for example stuff from 1/11/23, BUT

I want it to NOT select these so that the same word ( as its for a hangman game ) is not picked for that week again, example is attached

i did try adding NOT to the start of the function but of course not that easy
I would have thought you would need to store the word in a 'used' table, perhaps with a date, and then clear it out weekly?
 

murray83

Games Collector
Local time
Today, 21:47
Joined
Mar 31, 2017
Messages
737
I would have thought you would need to store the word in a 'used' table, perhaps with a date, and then clear it out weekly?
That makes more sense, ill shall try that avenue cheers all
 

murray83

Games Collector
Local time
Today, 21:47
Joined
Mar 31, 2017
Messages
737
sorry for dp but here it is as suggested with a table to remove and then clean at later date enjoy
 

Attachments

  • hangman.zip
    1 MB · Views: 67

Users who are viewing this thread

Top Bottom