selecting date in query

[murray83]

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

 

[cheekybuddha]

I can't test your db since it's in a too new version of Access for me, but you can try:

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]

I can't test your db since it's in a too new version of Access for me, but you can try:

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]

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]

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]

sorry for dp but here it is as suggested with a table to remove and then clean at later date enjoy