How do I set a specific date

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

Guest

I am automating an internal report process using access and I need to set a
date field to always be the Sunday most prior to the current date. I can't
seem to find a date function that will allow me to do this?

I can't use the '-' or '+' after the Date() function because the report
won't always be run on the same day of the week, but the date must always be
the Sunday immediately prior to the current date. Any ideas on how this can
be done?

Thanks!
 
This is the formula :

date()-format(date(),"w",vbMonday)

What should it do when the date would be a sunday? If it should give the
previous sunday then the formula above is ok otherwise you have to use :

date()-iif(format(date(),"w",vbMonday)<7,format(date(),"w",vbMonday),0

- Raoul
 
pskrab said:
I am automating an internal report process using access and I need to
set a date field to always be the Sunday most prior to the current
date. I can't seem to find a date function that will allow me to do
this?

I can't use the '-' or '+' after the Date() function because the
report won't always be run on the same day of the week, but the date
must always be the Sunday immediately prior to the current date. Any
ideas on how this can be done?

Thanks!
Click to expand...

If the current date is a Sunday, would you want to set the date field to
that date? If so, then I think this expression would yield the date you
want:

=Date() - Weekday(Date()) + 1
 
Thanks, JaRa.

I'm actually trying to use this code in two places; 1. to insert the date
into a table and, 2. to add the date to the end of a file name that I'm
copying to a network drive through a macro "transfer text" command.

In the first option above, I tried using the code below in an INSERT INTO
query. When I try to run the query, I get prompted for the vbMonday value.

When I try to use it in the second option, transfer txt, I get an error
message that access cannot find the expression, "vb Monday".

Maybe this code can't be used under these two circumstances - I don't have
much programming experience so maybe I'm not asking the right questions. Can
this code be used under these two scenarios?

Thanks so much for your help.
pskrab
 
The following code will return the prior Sunday everytime:

PriorSunday: Date()-Weekday(Date())+1

-SPARKER
 
SPARKER - Thank so much for your help. The code you suggested below worked!

Thanks to everyone for their help!
 
I got the following code to work in a query: Date()-Weekday(Date())+1
However, when I try to use this within a macro with a transfer text command,
the date is formatted as 3/13/2005 and I receive an error that the file path
is invalid. I've tried to somehow add the format option in the above command
to remove the "/" from the date but I get sparsing errors.

Here is the code I currently have in the transfer text macro on the File
Name line:

="C:\DEMO\FILENAME_" &Date()-Weekday(Date())+1 & ".CSV"

Do you know how I can incorporate the format command into this code to have
the date written in the file name as mmddyyyy. Ultimately, the file name
should look like: FILENAME_03132005.CSV.

Thanks for your help.
 
Do you know how I can incorporate the format command into this code to have
the date written in the file name as mmddyyyy. Ultimately, the file name
should look like: FILENAME_03132005.CSV.
Click to expand...

Use the Format() function to explicitly force the desired format:

="C:\DEMO\FILENAME_" & Format(Date()-Weekday(Date())+1,"mmddyyyy") &
".CSV"


John W. Vinson[MVP]
 
Thank you so much, John. That worked!

John Vinson said:
Use the Format() function to explicitly force the desired format:

="C:\DEMO\FILENAME_" & Format(Date()-Weekday(Date())+1,"mmddyyyy") &
".CSV"


John W. Vinson[MVP]
Click to expand...
 
Back
Top