alter a week number ww/yyyy recorded as a text field

[Redfang87]

Im recording a week number as defined from a date as a constant in a table ( Live_week )

this is then used for reference through many areas, what Im struggling with is simply adding 1 to the week number

so to show the current live_week I have a text box with

=DLookUp("[live_week]","[Live_Week]","[ID]=1")

that form then uses where format(date,"ww/yyyy") like "text box" to query records matching the current live

I was a text box to show a +1 to the week

=DLookUp("[live_week]"+1 <ww+1/yyyy>,"[Live_Week]","[ID]=1")

Is there some way I can do this? it would likely be easier if I didn't have the year recorded but that makes issues on records over a year. If I cant show an altered week number, Perhaps there is a better way to record a week number as a constant than being a text format field, as far as im aware I cant record a ww/yyyy as a date format itself

 

[BeeJayEff]

Try something like
(left(Live_week,2)+1)&mid(Live_week,3,5)
instead of messing around with Dlookup ?

Don't forget to cater for week 52 !

 

[Redfang87]

I couldn't get yours to work as it is , Im guessing because im not looking at any specific record in the table with the week constant but incorporating what you gave into the dlookup has worked perfectly.

=DLookUp("((Left([Live_week],2)+1) & Mid([Live_week].[Live_Week],3,5))","[Live_week]","[ID]=1")

You have also added a notch to my self teaching SQL in how to look at specific characters in a field so thank you very much.