There must be a more efficient way to do this

[TheSearcher]

See attached db.
The Get_WeekNo() sub gives me the results I need - but there must be a more efficient way of accomplishing this that I just can't think of at the moment.
I'm trying to update the TheWeekNo field to the proper week number. Each new week begins on a Monday. As I said the procedure works but what am I missing in terms of logic? I shouldn't have to run 364 queries.
Note: To set TheWeekNo field back to null just run the Update_WeekNo query.

Thanks in advance,
TS

 

[theDBguy]

Hi. Haven't downloaded your file yet, but does a simple DatePart or Format call not work for you at all?

 

[TheSearcher]

Thanks DBguy. Can you please elaborate?

 

[GPGeorge]

DatePart Function

Format Function

 

[theDBguy]

Thanks DBguy. Can you please elaborate?

Here's a sample from my Immediate Window.

Will that work for you?

 

[TheSearcher]

No. If you can peak at my code it's very simple. I'm missing some logic somewhere. Didn't sleep well last night.

 

[theDBguy]

No. If you can peak at my code it's very simple. I'm missing some logic somewhere. Didn't sleep well last night.

I'll take a look later. Can you please explain why the above simple approach won't work for your situation? Is the result wrong? Is today not the 25th week in your business calendar? Just curious...

 

[plog]

If you can peak at my code it's very simple.

To you it is. Actually to you in the present it is. You in the future may have no idea what you tried to accomplish with this code. Comment your code. Explain the purpose, explain the methodology. It will help others and your future self.

With that said--why? What does storing this data in a table help you accomplish? Generally if you can calculate values (which is the point of this post) you should calculate those values when you need and not store them. So, give us the big picture--what is the bigger picture of what this database does? And why do you want to store all that calculated data?

 

[TheSearcher]

Plog - Thanks. I'll spend the next few months writing a treatise explaining the big picture so that, one day, you and I can have an abstract conversation about motives and methods of application development. Stay tuned!

 

[plog]

Delicious. No effort is too much when writing dismissive comments about using good coding practices, but actually performing good coding practices is a bridge too far.

 

[TheSearcher]

DBguy - For some reason the datepart function doesn't recognize vbMonday. If it did I think this would solve my problem.

 

[theDBguy]

DBguy - For some reason the datepart function doesn't recognize vbMonday. If it did I think this would solve my problem.

In that case, try it this way.

DatePart("ww", Date(), 2, 2)

Hope that helps...

 

[TheSearcher]

Perfect! Thank you so much!!

 

[theDBguy]

Perfect! Thank you so much!!

You're welcome. Good luck with your project.

 

[GPGeorge]

Plog - Thanks. I'll spend the next few months writing a treatise explaining the big picture so that, one day, you and I can have an abstract conversation about motives and methods of application development. Stay tuned!

Perhaps there is a more diplomatic way to express the problem, but the reality is that many questions get posted on this, and many other forums, with no more information than "It doesn't work. Why?" or "Is this the best way to do X?", or "Review my code and tell me how to improve it."
Well, sometimes experienced Access developers can guess the missing information, and sometimes they do have plenty of free time to review, study and evaluate someone else's undocumented code. However, sometimes it's not so simple and the only way to move forward is to ask for more information.

In this case, for example, it's sort of hard to guess why and how this table would be useful. So it's hard to get too enthusiastic about reviewing the code and creating an alternative to it that accomplishes the same thing.

If you need the WeekNumber, WeekStartDate and WeekEndDate for any date between, oh, say, January 1, 2000 and December 31, 2050, a query is going to return those values WITHOUT needing any code at all. The table seems to add nothing, at least in an abstract sense. Hence the hesitency to dip into it.

By the way, this query generates the values identified for ANY valid date you use:

SELECT t_2023.TheDate
,DatePart('ww',[thedate]) AS WeekNo
,DateAdd('d',(DatePart('w',[TheDate])*-1)+1,[TheDate]) AS WeekStart
,DateAdd('d',6,DateAdd('d',(DatePart('w',[TheDate])*-1)+1,[TheDate])) AS WeekEnd
FROM t_2023
ORDER BY t_2023.TheDate

 

[Isaac]

Thanks DBguy. Can you please elaborate?

When someone gives you a function, and they suggest it's highly relevant to your situation, you do the work of testing the waters, reviewing the MSDN documentation and trying it out, rather than trying to force them to spoon feed it to you by just immediately asking them to keep posting until the full solution is done for you.

Development takes that 4 letter word: W.O.R.K. Don't be lazy on forums, you'll wear out your welcome in a hurry.

 

[sxschech]

W.O.R.K.
Will Only Reuse Knowledge

Therefore, perhaps, the question is reasonable and not lazy?

 

[Pat Hartman]

Maybe it's a generational thing. If someone recommended a function as being relevant to my question, I would find the documentation on the function and read it. If I were such a novice that I didn't understand the documentation, I would ask for clarification. Usually the documentation includes an example and that tends to clarify the syntax but not always. If I understood the syntax, I would at least try the function before I asked for more help. But, that's just me.

PS, if you use the DatePart() syntax with the literals rather than vbMonday and vbFirstFourDays, I would also document that in the code so I don't have to keep looking up the syntax to be able to understand what the DatePart() is doing.

 

[TheSearcher]

Thank you sxschech and dbGuy for not being rude and presumptuous – and thank you to everyone else for responding to my post.

If every moderator and generous contributor to this forum were to be assigned the same task we would all approach the task in our own unique way, utilizing our own unique vision and life experiences to arrive at a solution that works. This is something that should be celebrated – not criticized. I realize that coding for a living, after many years, can make one grumpy (myself included) and that years of practicing the same techniques can, unfortunately, result in severe tunnel vision but it’s prudent to be open to new ideas and approaches. It would be unreasonable for everyone who posts to justify, in an internet forum, why they’re taking a particular approach to a problem. If they did then every post would be 30 pages long. I was very familiar with the functions suggested in response to my post. I was just looking for a more readable way to accomplish the same thing. That being said, thanks again to everyone for your help.

 

[Pat Hartman]

It would be unreasonable for everyone who posts to justify, in an internet forum, why they’re taking a particular approach to a problem

Generally, you are asked "why" when the expert is trying to convince you that your method is either outright wrong or perhaps just not best practices, not because they're nosey or even care. The objective is to make YOU think about the decision process that brought you to this poor choice.