Crosstab Date Format

J

Joseph

I have a crosstab query that work just great. All the data that I require
shows up. But I need to change the first day of the week in my [Date]. I have
tried:

Format$([Date],dddd,vbFriday)
Format([Date],dddd,vbFriday)

Both with the same results, the query tries to put vbFriday in brackets. I
have changed it in both the Design View and the SQL view, and no luck. Is
there any way that I can fix this or is there a go around?

Thank you
 
J

John Spencer

Your question is unclear to me. Perhaps if you posted a date and what you
wanted it to be then someone could help you.

DO you mean that you want to change the date to Monday of the week for all
dates from Monday to Sunday?
Look at the DateAdd function
DateAdd("d",1-WeekDay([SomeDate],vbFriday),[SomeDate])

Do you mean you want to display the name of the day? Or do you want to
display the date in a certain format?

Since you are doing this in a query, you cannot use the Visual Basic
constants, but will have to use the actual value for VBFriday (6)

So perhaps what you want is
DateAdd("d",1-WeekDay([SomeDate],6),[SomeDate])

TO get the syntax correct for what you posted you would use. Although
specifying the first day of the week in this case would have no effect on
what was displayed. The last two arguments for a date format only cause
changes in the display of the week number and have little or no effect on
other parts of the date.
Format([Date],"dddd",6)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
M

Michel Walsh

Queries cannot read the definition of the VBA constants. Use 6, not
vbFriday. You can get the numerical value of a constant in the immediate
(debug) window:


? vbFriday
6



Hoping it may help,
Vanderghast, Access MVP
 
J

Joseph

The Format procedure

Format(<<expr>>,<<format>>,<<firstdayoftheweek>>,<firstweekoftheyear>>)

I need to make the first day of the week Friday, instead of the default
Sunday.



John Spencer said:
Your question is unclear to me. Perhaps if you posted a date and what you
wanted it to be then someone could help you.

DO you mean that you want to change the date to Monday of the week for all
dates from Monday to Sunday?
Look at the DateAdd function
DateAdd("d",1-WeekDay([SomeDate],vbFriday),[SomeDate])

Do you mean you want to display the name of the day? Or do you want to
display the date in a certain format?

Since you are doing this in a query, you cannot use the Visual Basic
constants, but will have to use the actual value for VBFriday (6)

So perhaps what you want is
DateAdd("d",1-WeekDay([SomeDate],6),[SomeDate])

TO get the syntax correct for what you posted you would use. Although
specifying the first day of the week in this case would have no effect on
what was displayed. The last two arguments for a date format only cause
changes in the display of the week number and have little or no effect on
other parts of the date.
Format([Date],"dddd",6)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Joseph said:
I have a crosstab query that work just great. All the data that I require
shows up. But I need to change the first day of the week in my [Date]. I
have
tried:

Format$([Date],dddd,vbFriday)
Format([Date],dddd,vbFriday)

Both with the same results, the query tries to put vbFriday in brackets.
I
have changed it in both the Design View and the SQL view, and no luck. Is
there any way that I can fix this or is there a go around?

Thank you
Click to expand...
Click to expand...
 
J

John Spencer

Using FORMAT will have not change the date.

As I said earlier, can you post some examples of dates and what you want
the result to be.

April 19, 2007 is a Thursay
April 20, 2007 is a Friday
April 21, 2007 is a Saturday.

What do you want the format procedure to return with those dates. About
the only thing that might change would be the week number of the year.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The Format procedure

Format(<<expr>>,<<format>>,<<firstdayoftheweek>>,<firstweekoftheyear>>)

I need to make the first day of the week Friday, instead of the default
Sunday.



John Spencer said:
Your question is unclear to me. Perhaps if you posted a date and what you
wanted it to be then someone could help you.

DO you mean that you want to change the date to Monday of the week for all
dates from Monday to Sunday?
Look at the DateAdd function
DateAdd("d",1-WeekDay([SomeDate],vbFriday),[SomeDate])

Do you mean you want to display the name of the day? Or do you want to
display the date in a certain format?

Since you are doing this in a query, you cannot use the Visual Basic
constants, but will have to use the actual value for VBFriday (6)

So perhaps what you want is
DateAdd("d",1-WeekDay([SomeDate],6),[SomeDate])

TO get the syntax correct for what you posted you would use. Although
specifying the first day of the week in this case would have no effect on
what was displayed. The last two arguments for a date format only cause
changes in the display of the week number and have little or no effect on
other parts of the date.
Format([Date],"dddd",6)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Joseph said:
I have a crosstab query that work just great. All the data that I require
shows up. But I need to change the first day of the week in my [Date]. I
have
tried:

Format$([Date],dddd,vbFriday)
Format([Date],dddd,vbFriday)

Both with the same results, the query tries to put vbFriday in brackets.
I
have changed it in both the Design View and the SQL view, and no luck. Is
there any way that I can fix this or is there a go around?

Thank you
Click to expand...
Click to expand...
Click to expand...
 
J

Joseph

I tried the to use the number 6, but it still doesn't work. Is there any
other way to make Friday the first day of the week?


Michel Walsh said:
Queries cannot read the definition of the VBA constants. Use 6, not
vbFriday. You can get the numerical value of a constant in the immediate
(debug) window:


? vbFriday
6



Hoping it may help,
Vanderghast, Access MVP


Joseph said:
I have a crosstab query that work just great. All the data that I require
shows up. But I need to change the first day of the week in my [Date]. I
have
tried:

Format$([Date],dddd,vbFriday)
Format([Date],dddd,vbFriday)

Both with the same results, the query tries to put vbFriday in brackets.
I
have changed it in both the Design View and the SQL view, and no luck. Is
there any way that I can fix this or is there a go around?

Thank you
Click to expand...
Click to expand...
 
M

Michel Walsh

Well, can still add two days where we need to compute the weekday: that
added 2 days will artificially push a Friday as a Sunday. You will still
have to use the really date, not incremented by 2, when the date itself, not
its week number, has to be displayed.


Hoping it may help,
Vanderghast, Access MVP


Joseph said:
I tried the to use the number 6, but it still doesn't work. Is there any
other way to make Friday the first day of the week?


Michel Walsh said:
Queries cannot read the definition of the VBA constants. Use 6, not
vbFriday. You can get the numerical value of a constant in the immediate
(debug) window:


? vbFriday
6



Hoping it may help,
Vanderghast, Access MVP


Joseph said:
I have a crosstab query that work just great. All the data that I
require
shows up. But I need to change the first day of the week in my [Date].
I
have
tried:

Format$([Date],dddd,vbFriday)
Format([Date],dddd,vbFriday)

Both with the same results, the query tries to put vbFriday in
brackets.
I
have changed it in both the Design View and the SQL view, and no luck.
Is
there any way that I can fix this or is there a go around?

Thank you
Click to expand...
Click to expand...
Click to expand...
 
Top