Week Order Query Help HELP HELP HELP :)

[Waddy]

Hi my name is Mark and have a good understanding of Access and learn new things all the time and feel quite confident in creating databases.

I hope you can help me.

I have a table called tblUploaddata and one field is called Week set as text, it has data like Week 1 P1W1 and Week 10 P10W4

I run a query which shows these weeks in a drop down box and is sorted A-Z

The query when I sort it goes like:

Week 1 P1W1
Week 10 P1W1
Week 2 P2W3

Etc etc which is logical

However I need to make it run in order like:

Week 1 P1W1
Week 2 P2W3
Week 10 P1W1

I would never use this type of format but its the data I have been given and cannot change.

I hope you can help and I have gave you enough data to assist me in this and you have all the information to assist me.

Thank you

 

[pbaldy]

Can you add fields to the table? I'd add fields for the 2 variable components and update them to contain just the number and the last bit. You can use the Split() function or a combination of Mid() and InStrRev() to grab those 2 parts.

FYI, I moved your thread out of the introductions forum to queries, where it's more appropriate. Welcome to AWF!

 

[MajP]

I would make a UDF and use in a query.
Untested, but something like.

Public Function GetSort(TheWeek as String) as integer
  GetSort = CINT(Split(TheWeek," ")(1))
end function

Then in your query "Order By GetSort([Week])"

The split function splits a string into an array on what you specify. In your case a space " ". So you turn this string "Week 1 P1W1" into an array of
Week
1
P1W1

But as mentioned if you can add to the table that would help.

 

[JHB]

Open the query in the attached database.

 

[June7]

The word Week is prefix in every value and always followed by a space and number? Can parse out the week number with:

Val(Mid([Week],6))

Parsing the PxWx will be trickier.

You show two PxWx for Week 10. Will there be multiple PxWx for each week? Do you want to sort on this part? If you want Week 10 P2W1 to sort before Week 10 P10W4, that will be difficult.

Yes, custom function may be needed.

 

[Waddy]

Open the query in the attached database.

Amazing thank you so much

 

[Waddy]

Thank you so much, this site and the people is amazing, thank you for everyone who took the time to assist me in this

No I must return the favor to someone else.

 

[JHB]

Amazing thank you so much

You're welcome, good luck!

 

[Waddy]

The word Week is prefix in every value and always followed by a space and number? Can parse out the week number with:

Val(Mid([Week],6))

Parsing the PxWx will be trickier.

You show two PxWx for Week 10. Will there be multiple PxWx for each week? Do you want to sort on this part? If you want Week 10 P2W1 to sort before Week 10 P10W4, that will be difficult.

Yes, custom function may be needed.

Yes had them multiple times but put some vb code in to address this. Thank you!!!

 

[isladogs]

Waddy
Although this works perfectly for the data supplied, do bear in mind that if you have 2 records for the same week they may not sort 'correctly' as previously mentioned. For example

If that's likely to be an issue, you will need a more complex solution

 

[JHB]

The below would do the job.

SELECT tblWeek.Week, Int(Mid([Week],6,InStr(6,[Week]," ")-6)) AS Sorted, Int(Mid([Week],InStr(6,[Week],"W")+1,Len([Week])-InStr(6,[Week],"W"))) AS SecondSort
FROM tblWeek
ORDER BY Int(Mid([Week],6,InStr(6,[Week]," ")-6)), Int(Mid([Week],InStr(6,[Week],"W")+1,Len([Week])-InStr(6,[Week],"W")));

 

[isladogs]

Hi JHB
I was just trying to make the OP aware but thanks for updating it

I'm not trying to be awkward but that will sort by the W part of the end string but not the P part.
So P10W3 will still come before P2W1.
It's all easily solvable ...but only if the OP needs to worry about it of course.