Query current week number (1 Viewer)

[tb5038]

Hi,

I need to a query on the week number, the week number is in the table. Imjust struggling with getting the current weeks data.

Thanks
Tj

 

[plog]

Weeks are funny, ill-defined thing. Here's everything everyone can agree on about them--they span exactly 7 days. That's it. You can usually build a consenus about them starting on Sunday, but that's not a hard-coded fact. When you start talking about weeks, its a fuzzy issue.

Then when you start numbering them, it gets even more ill-defined. There's no set standard for doing so.

You need to explain how you number your weeks, when your week starts and how you would determine the number of your current week. If you could post sample data from your table, including field and table names that would be helpful too.

 

[tb5038]

I have already got a query which gives me the current week number. I just need to run a query on the current week number. According to ms access were in week2 today!

 

[plog]

So what's the issue? What have you tried in your query?

 

[namliam]

Using the format function you can retrieve (easily) which ever weeknumber version you desire. The premise though, weeknumber isnt about "ms access says" it is what your company/business rules say and adjust accoording to those rules.

 

[tb5038]

I can get the current weeknumber by adding this in the query

Weekno: datepart("ww",[Datechk])

This gives me what week number my field datechk is.

I also have a text box on a0another form that gives me the current weekno if I could build a query that returns the records from the cuurent week that would be great

Heres what I use on another form to get the week no

=cint(format(now(),"ww"))

Like I say I use that above on a form, im just strugling with building the query criteria

Thanks
Tj

 

[tb5038]

Using the format function you can retrieve (easily) which ever weeknumber version you desire. The premise though, weeknumber isnt about "ms access says" it is what your company/business rules say and adjust accoording to those rules.

Thanks im happy to use ms access week number. Just need help with the coding of the query

Tj

 

[namliam]

Select *
from yourtable
where yourweeknumbercolumn = cint(format(date(),"WW"))

That what you are looking for?

 

[tb5038]

Really silly question. Where would i add that? In the sql view?

Tj

 

[namliam]

you make a new query
add your table to it
add your fields to it, including your weeknumber field
then in the criteria line put in cint(format(date(),"WW"))

 

[tb5038]

Thanks for that. All working now, think i might try a dcount next. Trying to count the records in the query.

Thanks for all your help with the week number!

Tj