DataBase - syntax for Week number
Asked By Revned
20-Mar-10 04:25 AM
Hi,
Can anyone help me how figure out a correct syntax for week no.
I have tblRev with fieldname:Date
I want access automatically display the week no. after
entering a Date in my field: Date.
Please can anyone help
Thanks
VBA
(1)
Report
(1)
DatePart
(1)
VbMonday
(1)
Sebastian
(1)
MuchJohn
(1)
Tuesday
(1)
DreamsI
(1)
sweet_dreams replied to Revned
Hi,
With Monday as a first day of week following code should be helpful:
Format(Date, "ww",vbMonday)
Regards,
Sebastian
John W. Vinson replied to Revned
First off... it is best to NOT use the reserved word Date as a fieldname!
Access can (and will!) get it confused with the builtin Date() function, and
give you strange errors.
That said, you can have a textbox on your form or report bound to the date
field, and set its Format property to "ww" to display the (1 to 54) week
number; for more control, you can use
=DatePart("ww", [datefield], <optional parameters>)
See the VBA help for DatePart; the parameters let you specify just what you
mean by the first week of the year (what day does the week start - Monday or
Sunday or some other day? Can the first week of the year be only one day long,
or should Week 1 be the week of the first Tuesday? etc.)
--
John W. Vinson [MVP]
Revned replied to John W. Vinson
Thank you very much
John W. Vinson and sweet_dreams
I really appreciate
Hello, My form must be corrupt, becuase the Date() function isn't being recognized in VBA. I am trying to work around it with this: Dim a As String a = Chr(34) & DatePart("m", Now()) & " \ " & DatePart("d", Now()) & " \ " & DatePart("yyyy", Now()) & Chr(34) [activity date] = CDate(a) Variable a evaluates to "3 \ 13 \ 2008 addressed. Thanks for any help and God Bless, Mark A. Sam Access Modules Coding Discussions VBA (1) Database (1) Report (1) DatePart (1) Autocorrect (1) Q4ljt3527uknh6eo1reui0boq7nbpevjqd (1) God (1) Anyhwere (1) If a builtin function is not Vinson [MVP] John, By workaround, I meant this: Dim a As String a = Chr(34) & DatePart("m", Now()) & " \ " & DatePart("d", Now()) & " \ " & DatePart("yyyy", Now()) & Chr(34) [activity date] = CDate(a) I
Bold report column based on month DataBase I have a report with a column for each month. (Jan, Feb, Mar. . .) I'd like to bold the data in the column that the report is run for. I placed this code in the on Format event of the report to test, the code is executing ok but the columns end up all in normal weight. m = DatePart("m", dt) Select Case m Case 1 Me.Jan.FontWeight = vbBold Me.Feb.FontWeight = vbNormal Me.Dec.FontWeight = vbNormal End Select Any help would be appreciated. Thanks Access Reports Discussions VBA (1) Report (1) FontWeight (1) DatePart (1) VbNormal (1) VbBold (1) StrM (1) Dec (1) Hi Chuck, My version of Access
Displaying the same data separately on a report DataBase My users want to see scheduled events for the current week and the next week on the same report - - but to have the weeks separated, e.g., Current Week, Next Week. I thought this would be simple with a report and subreport, but the problem is that there is no parent / child relationship between this week's and next week's data and The report / subreport jumbled the data strangely. I have two queries that pull the correct data. Here Any help would be appreciated. - - susan Access Reports Discussions CallDate (1) Tbl_Deployment_Details (1) Tbl_Deployment_Record (1) DatePart (1) MVP (1) MS (1) VBA (1) DateFieldName (1) If you can use a single query to get all the desired the data from my Current Week and Next Week queries. And I've created a report that groups on the week / year generated by Format([DateFieldName], "wwyyyy"). So that part works or when the weeks are in different years. Try this expression instead: Year(date) & Format(DatePart("ww", date), "00") How would you like to format the date? There is no need
How do I change my datetime format on my report DataBase I'm a beginner and I really need your help. I have a datetime my table called Birthdate with Short Date format which is 12 / 22 / 2007. On my report, I would like it to show exactly like this: on the 22nd day of December, 2007 Any help will be greatly appreciated. Thank you. Access Reports Discussions VBA (1) Report (1 DatePart (1) DateIn (1) MoIn (1) Birthdate (1) December (1) February (1) Copy and Paste the for If IsNull(DateIn) Then DateOrdinalEnding = "" Exit Function End If Dim dteX As String dteX = DatePart("d", DateIn) ' The next is all on one line dteX = dteX & Nz(Choose(IIf((Abs it, I suggest that you translate that logic to a public Function in a standard VBA module and just call the function from the text box. - - Marsh MVP [MS Access] keywords: How, do, I, change, my, datetime, format, on, my, report description: I'm a beginner and I really need your help. I have a datetime