Access Query Issue

  • Thread starter Thread starter nsellers
  • Start date Start date
N

nsellers

Problem I have multiple queries that pull info from one table. Each
query pulls a different work sections schedule out of the table. The
issue is the schedule is based on a work week of monday thru friday I
would like to be able to update the criteria in the all the queries
automatically somehow. Either by a module that runs once a week to
update each query or by inputing the new dates into one place and have
each query capture it. Any suggestions would be greatly appreciated.
 
You can use a format trick inside a query to isolate a day of the week.
Example:

SELECT Format([YourDateField],"dddd") AS Expr1
FROM [YourTable]
WHERE (((Format([YourDateField],"dddd"))="Monday"));

If I understand correctly, you want to do something based on a range of date
values and a certain day of the week, so your query might look like this:

SELECT Format([YourDateField],"dddd") AS Expr1, [YourDateField]
FROM [YourTable]
WHERE (((Format([YourDateField],"dddd"))="Monday")) and YourDateField
between FirstDateValue and SecondDateValue;

Linda
 
i'm still a beginner when it comes to Access and VB. I understand both
for the most part but when it comes to actually getting vb to do stuff
in access is where i'm a little lost. For instance I can edit a button
a form to do some sort of action. But say I want to write a vb script
that will get the date from the computers calendar and use that to know
that a week has passed and it is time to change the range of dates in
the access query. Now I'm sure I can figure out the code to write but
how do I actually get the module to run thru its acctions and
accomplish what it is supposed to do. I mean can I link it somehow to a
button that runs that code or will it just do that automatically for
me. I know that was a loaded question. Perhaps you could just recommend
a book for me to buy. I already have access 2003 for dummies. Thnx
 
Here is one method.

Field: SomeDateField
Criteria: Between Date() - WeekDay(Date(),vbMonday) + 1 And Date() -
Weekday(Date(),vbMonday) + 5

This takes the system date and adjusts it by subtracting the day of the week to
get Sunday and then adding 1 (or 5) to get the Monday and Friday of the current
week.

Another method would be to use a control on a form to get the date and then
reference that in your queries. Here is a VERY simplified overview.

Create a simple form - name the form - MyInputForm
Add a control to it (name the control - MyStartDate)
Enter the date you want to use
Leave the form open

Change your query to
Field: SomeDateField
Criteria: Between [Forms]![MyInputForm]![MyStartDate] And
[Forms]![MyInputForm]![MyStartDate] + 4
 
Nsellers,

It seems to me you might be making this unnecessarily complicated.
Access has a built-in function called Date() to identify the current
date. If you want your query to always run for a period such as
"between Mondat this week and next Sunday" or whatever, there is no need
for and fancy VBA code or modifications to the query criteria, a simple
expression will do the trick. Perhaps if you could give some more
detailed information, including examples, of what you are trying to
accomplish, someone may be able to advise more explicitly.
 
Back
Top