Coversion of Text to a number

[Accessosaurusrex]

I have a database that has been working fine but one field was listed as text. As it turns out the field is used strictly for numbers and we would like the reports generated to be grouped by a sequential list of numbering from that field. I simply converted the field to a number type rather than a text field. Looked good until I noticed many of the barcoded reports started failing. On the barcode reports I have a text box with the following data:

=("*"+[AllQueryNoNS.Element]+"-"+[AllQueryNoNS.Mark]+"-"+[AllQueryNoNS.Weld]+Nz([AllQueryNoNS.RorRR])+"/T/"+[AllQueryNoNS.Root]+"*")

This results in a barcode which states for example *23A-65-6R/T/X*

Now that I have converted the "Weld" field to a number only, I get errors. Is there another way to handle a numerical field value in this report to achieve the end result? All other fields are text fields. The 6 just before the "R" in the above example is the field for weld.

If not is there a way to tell Access to sort the text field in a numerical fashion instead of text? For example 1, 2, 3, 10, 11, 20 instead of 1, 10, 11, 2, 20, 3? If so I can reconvert that field to text.

Thanks for your time.

 

[BBQ Kittens]

Try this.

=("*"&"[AllQueryNoNS.Element]"&"-"&"[AllQueryNoNS.Mark]"&"-"&"[AllQueryNoNS.Weld]"&"Nz([AllQueryNoNS.RorRR])"&"/T/"&"[AllQueryNoNS.Root]+"*")

/fingers crossed

 

[Alansidman]

23A-65-6R/T/X

Is this the field that you wish to sort numerically? If so, which part of the field will dicatate the sort? Is the layout always in this format and length? ie. ##L-##-#L/L/L.

 

[Accessosaurusrex]

To BBQ Kittens, it was very close, I simply removed the quotations around the field brackets and used the & symbol in lieu of the + symbol and it worked. Thanks you!

To Alansidman, the sort order is done with the numerical value in the middle of the sort. These fields describe components and the weld number describes which weld out of many on the component. We wanted to keep all the welds on the same component together but weld number is only part of the description. So element is the first sort, mark is the second and weld is the third part, the last two are merely additional information for easy access. The layout is always in the format shown. If there is a better way, I am all ears...lol!

Thank you both very much for your time!

 

[Alansidman]

I asked because, I thought with some data manipulation in some helper columns(new fields) in a query you manage a sort. For example if you wanted to sort first on the number that represents the weld number, you could do something like this in a new field:

Helper:Right(CurrentFieldName,6)
Then in another column
Sort: Left(Helper,1)

This would parse out the 6 and you could then use this as your first sort column. Uncheck the display boxes in your query.

Just a thought on how to work around this issue.

 

[vbaInet]

To Alansidman, the sort order is done with the numerical value in the middle of the sort.

So element is the first sort, mark is the second and weld is the third part, the last two are merely additional information for easy access.

I don't understand what field you are looking to sort by exactly. Those two statements are conflicting. Do you want to sort by all three fields or just by Weld?

If it's just by Weld, simply sort by Weld. Weld is Numeric. The R is just a concatenated value from RorRR field.

If you want to sort by the three fields, get the numeric value of element using Val(), sort by that, then sort by element, followed by mark then weld.