Re: Create Weekly and Monthly Flag in database/table

"Lucas_london via AccessMonster.com" <u35944@uwe> wrote in
7779dded54462@uwe:">news:7779dded54462@uwe:

Bob Quintal wrote:
Hi Bob,

[quoted text clipped - 38 lines]
FROM Data
WHERE field1 In ('LLOY','RSA');

the weekday(datefield [,optional first day of week)) returns a
number which represents the day of the week, Without the optional
number, fridays are 6.

In the query designer if you put your field2, then surround it wth
the weekday function, you can set the 6 in the criteria row.

Access will add a label, in the form of expr1:, you can change
that to any name you want for the column (within reason)

Now the issue with the missing fridays, one approach is to make a
small table with the holidays that fall on a friday. and join that
table on the dates, changing the join to show all records from
your main table ano the ones from the holidays table where the
dates are equal.

now you can add one to the weekday if the holiday table date is
not null.

so your query becomes something close to

SELECT *
From Data
LEFT Join Holidays
ON data.field2 = Holidays.holiday
WHERE field1 In ('LLOY','RSA');
AND weekday(field2)
+ iif( isnull(Holidays.holiday),0,1)
= 6;

Q

Hi

[quoted text clipped - 18 lines]
instructions on how to write the query you need. Please supply a
few more details.

------------------------------------------------------------------
-
----------- ------
Hi Bob,

Thanks very much for the code and explaining how it works. As per
my orginal posting, I also need to devise a similar query to bring
back monthly data - is there a similar function I could use for
this? I've checked the functions available in Access but could not
see one. Again, just likely the weekly query, for each month I
want to bring back the row for the last available date in that
month. I suspect I will need to use a combination of functions to
get the result I need but maybe I'm over complicating things. Any
ideas on the best approach?

Many Thanks,

Lucas

the dateadd() function will point you in the right direction
if you subtract 1 from the first of any month, you get the last day
of the previous month. The dateserial builds a date from numbers.
The trick is to get the month numbers, from somewhere, perhaps a
small table with the first of each month, or the numbers 1to 12

LastOf Month = dateAdd("d",-1,dateserial(year(now(),month(now(),1)
will return the last day of july since we are in August.




--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

.

Relevant Pages

  • Re: Create Weekly and Monthly Flag in database/table
    ... WHERE field1 In; ... the weekday(datefield [,optional first day of week)) returns a ... In the query designer if you put your field2, ... small table with the holidays that fall on a friday. ...
    (comp.databases.ms-access)
  • RE: Networking days, weekends, holidays and delivery days cancelle
    ... I was able to get the module to work, but when I try to query the results, I ... It excludes Saturdays, Sundays, and any date with an entry in the Holidays ... Dim dtmDate As Date ... On Error GoTo CountWeekDays_Error ...
    (microsoft.public.access.queries)
  • Re: Is it possible to link two text boxes...
    ... The only thing I can think of would be to divide the text in the query. ... SELECT MyTextField AS Field1, MyTextField AS Field2 ... question is that I have two images on the page and one is justified left ...
    (microsoft.public.access.reports)
  • RE: DateFunction in Query
    ... I'm all of a sudden getting this error when I open the query, ... Undefined function 'CalcWorkDays' in expression ... dtmEnd As Date) As Integer ... 'Subtract the Holidays ...
    (microsoft.public.access.queries)
  • Re: Networkdays Formula in Access
    ... Specifically look at the second method since it takes holidays into account. ... The second approach has the advantages of being more flexible and requiring no VBA, but does require more upfront maintenance. ... In order to completely eliminate the need for VBA from the process of populating tCalendar, add another table called tNumbers, to contain numbers from 0 to 1000 to use in calculations in a field called ThisNumber with datatype Number. ... You can use a query to set the weekend flag. ...
    (comp.databases.ms-access)