changing a query from an entry in a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to set up a form from which the criteria of a query can be
changed, ie once a month the dates will need to be changed for the rest of
the users-so the intention is to set up a form to amend the query- Any ideas
on how to code this would be appreciated.
 
What are you using for dates? Couldn't you make them more dynamic? there
are lots of ways to change a query based on the current date. You could use
an IF statement to say, If it is after the 15th, the pull the current month,
if not, then pull last month (for example).

Tell us what your "rules" are and we can hellp you build it.

Otherwise, yes you can tie the criteria to an entry in a form.
 
Thanks Rick B
Thanks for your comments and assistance- sorry if I didnt explain very well,
let me try and explain - it is a database that colleagues enter details each
day- at the end of each month I have to make a report based on the entries
made by others during the period from the previous last friday in a month to
the current last friday in month - that query is all set up but I am manually
changing the query each month. If I am not there noone knows how to change
it- so wanted to make a form where they can just put in the dates required.
All thoughts and ideas appreciated

thanks
Sean
 
Well, there are codes that you can use to represent those dates. No need to
put in anything!

Unfortunately, I don't know them for something as specific as 'last friday'
of the previous month, but I bet you an MVP will have a suggestion for you.
Once you enter the right formula, you'll never have to touch that query
again!
 
After searching Google, it looks like you might be able to use the following
in your queri to do what you ask...


Between
DateAdd("d",1-DatePart("w",DateSerial(Year(Date()),Month(Date()),0),6),DateS
erial(Year(Date()),Month(Date()),0)) and
DateAdd("d",1-DatePart("w",DateSerial(Year(Date()),Month(Date())+1,0),6),Dat
eSerial(Year(Date()),Month(Date())+1,0))

Just put that in place of your current "between" statement.


I'm not 100% sure I have the first part of the statement right.
 
Sean:

I just tested it and it does work! make sure you put that entire string in
the criteria under your date field.
 
Back
Top