halp!!! weekly report

[Guest]

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????

 

[ruralguy via AccessMonster.com]

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

 

[Guest]

"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

 

[ruralguy via AccessMonster.com]

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]

 

[Guest]

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:

 

[Guest]

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????

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Guest]

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????

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

The module should be named basDateStuff but the Function needs to be:

Public Function ToMonday(StartDate As Date) As Date

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

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]

 

[ruralguy via AccessMonster.com]

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]

 

[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

 

[Guest]

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????

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

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]

 

[Guest]

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

 

[Guest]

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????

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[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????

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

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.

yeah....

It worked...



thank you verry much ;D

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

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

[quoted text clipped - 19 lines]

 

[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.