halp!!! weekly report

[Emanuel Violante]

Hi everyone!!!

I have a database from wich i need to do a weekly report of costumers
created since last monday.

Each record has a field with the date.

What I need is a query that will return all costumers created sence last
monday.

Can anyone help me, please????

 

[Emanuel Violante]

"ruralguy via AccessMonster.com" escreveu:

Maybe you will find this function useful:


Public Function ToMonday(StartDate As Date) As Date
'-- convert the StartDate to the previous Monday's Date
ToMonday = StartDate - (DatePart("w", StartDate, vbMonday) - 1)

End Function

Thank you verry much for your post....
I'm newbie, so I don't exactly know where to place the function you
specified... :$

could you help me a little more??

Thanks in advance

Emanuel Violante Galeano
PORTUGAL

"ruralguy via AccessMonster.com" escreveu:

 

[grep]

In the query, under the date field, try using the following criteria:

=(Date()-Weekday(Date(),2)+1)

That should give you everything since Monday.

grep

 

[Emanuel Violante]

In the query, under the date field, try using the following criteria:
Thanks for your post... ;D

Is the formula correct?

I get an error message, that the syntax isn't correct

I'm using the french version of Access 2003


--
Thanks,

Emanuel Violante Galeano


"grep" escreveu:

 

[Emanuel Violante]

Here is the SQL of the query:

SELECT contr_index, contr_data, contr_out1, contr_out2
FROM contrato;

the contr_data field is the field that i want to filter

Sorry if my english isn't correct, but I'm from Portugal ;D

--
Thanks,

Emanuel Violante Galeano


"ruralguy via AccessMonster.com" escreveu:

Have you used the Query Builder yet? Post the SQL of your query and we can
work on it. The function I supplied will need to go into a standard module
so we can get to it any time we want. Just don't name the module ToMonday.
How about basDateStuff as a name for the module?

Maybe you will find this function useful:

[quoted text clipped - 3 lines]

End Function

Thank you verry much for your post....
I'm newbie, so I don't exactly know where to place the function you
specified... :$

could you help me a little more??

Thanks in advance

Emanuel Violante Galeano
PORTUGAL

Maybe you will find this function useful:

[quoted text clipped - 15 lines]

Can anyone help me, please????

 

[Emanuel Violante]

Here is the module code I created...
I named the code as basDateStuff

Is that what you wanted me to do?

Public Function basDateStuff(StartDate As Date) As Date
'-- convert the StartDate to the previous Monday's Date
ToMonday = StartDate - (DatePart("w", StartDate, vbMonday) - 1)

End Function




--
Thanks,

Emanuel Violante Galeano


"ruralguy via AccessMonster.com" escreveu:

Have you used the Query Builder yet? Post the SQL of your query and we can
work on it. The function I supplied will need to go into a standard module
so we can get to it any time we want. Just don't name the module ToMonday.
How about basDateStuff as a name for the module?

Maybe you will find this function useful:

[quoted text clipped - 3 lines]

End Function

Thank you verry much for your post....
I'm newbie, so I don't exactly know where to place the function you
specified... :$

could you help me a little more??

Thanks in advance

Emanuel Violante Galeano
PORTUGAL

Maybe you will find this function useful:

[quoted text clipped - 15 lines]

Can anyone help me, please????

 

[grep]

It works for me using the English version of Access 2003. I don't know -
how do you say WEEKDAY in French?

I used a table that lists students. The sql query is:

SELECT tblStudents.[Student ID], tblStudents.FirstName,
tblStudents.DateEntered
FROM tblStudents
WHERE (((tblStudents.DateEntered)>=(Date()-Weekday(Date(),2)+1)));


grep

 

[Emanuel Violante]

I get the following message:

Syntax error (missing operator) in query expression 'contr_data =>
ToMonday(Date).



--
Thanks,

Sorry if my english isn''t correct, but I''m from Potugal

Emanuel Violante Galeano


"ruralguy via AccessMonster.com" escreveu:

Try:
SELECT contr_index, contr_data, contr_out1, contr_out2
FROM contrato
WHERE contr_data => ToMonday(Date);

Here is the SQL of the query:

SELECT contr_index, contr_data, contr_out1, contr_out2
FROM contrato;

the contr_data field is the field that i want to filter

Sorry if my english isn't correct, but I'm from Portugal ;D

Have you used the Query Builder yet? Post the SQL of your query and we can
work on it. The function I supplied will need to go into a standard module

[quoted text clipped - 23 lines]

Can anyone help me, please????

 

[Emanuel Violante]

yeah....

It worked...



thank you verry much ;D

=(Date()-JourSem(Date();2)+1)

--
Thanks,

Sorry if my english isn''t correct, but I''m from Potugal

Emanuel Violante Galeano


"grep" escreveu:

It works for me using the English version of Access 2003. I don't know -
how do you say WEEKDAY in French?

I used a table that lists students. The sql query is:

SELECT tblStudents.[Student ID], tblStudents.FirstName,
tblStudents.DateEntered
FROM tblStudents
WHERE (((tblStudents.DateEntered)>=(Date()-Weekday(Date(),2)+1)));


grep

Thanks for your post... ;D

Is the formula correct?

I get an error message, that the syntax isn't correct

I'm using the french version of Access 2003

 

[Emanuel Violante]

I get prompted to insert Date value....



--
Thanks,

Sorry if my english isn''t correct, but I''m from Potugal

Emanuel Violante Galeano


"ruralguy via AccessMonster.com" escreveu:

Oops...try >= rather than =>.

I get the following message:

Syntax error (missing operator) in query expression 'contr_data =>
ToMonday(Date).

Try:

[quoted text clipped - 16 lines]

Can anyone help me, please????

 

[BruceM]

I may have lost track of what's going on in this thread, but if this is a
query expression, I think Date needs to have parentheses:
contr_data >= ToMonday(Date())

I get prompted to insert Date value....



--
Thanks,

Sorry if my english isn''t correct, but I''m from Potugal

Emanuel Violante Galeano


"ruralguy via AccessMonster.com" escreveu:

Oops...try >= rather than =>.

I get the following message:

Syntax error (missing operator) in query expression 'contr_data =>
ToMonday(Date).


Try:
[quoted text clipped - 16 lines]

Can anyone help me, please????

 

[Michael Gramelspacher]

Outstanding! At least one of the methods works. My method would probably
have worked if we used Date() instead of just Date. Thanks for posting back
with your success.

copy this to the Immediate window and run it:

?(#2007-05-05# >= (Date - (DatePart("w", Date, vbMonday) - 1)))

It returns False for me. Change date to #2007-05-07# and it returns True.

No Date() needed.

 

[BruceM]

The Date function uses parentheses when used in SQL or in something like a
text box control source expression, and no parentheses when used in VBA.
Either Date or Date() works in the immediate window, but in VBA Date() would
have been changed automatically to Date. There may be a setting that
controls whether or not that happens, but if so I can't recall what it is.
In any case it's the same Date function.