how do I set a date to always be the Sunday prior to the current .

  • 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!
 
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?
Click to expand...

date() - Weekday(date(),vbSunday) + 1
 
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, Joan.
 
It definitely can't be used in queries, as they don't know about the
intrinsic VBA constants. Substitute 2 for vbMonday (although why you have
vbMonday when Joan said vbSunday is confusing...)

What's your actual code for the second case?
 
Douglas said:
It definitely can't be used in queries, as they don't know about the
intrinsic VBA constants. Substitute 2 for vbMonday (although why you have
vbMonday when Joan said vbSunday is confusing...)
Click to expand...

The OP could use this formula as the DEFAULT for the column in the
table definition, which would make INSERTs easier.

Jamie.

--
 
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.
 
Back
Top