Datatype Problem

[JimmyG]

My database contains a table of quotation details. One field specifies quantity. In most cases the higher the quantity quoted the lower the price offered. However in some circumstances a set price is offered regardless of quantity purchased.

If I set the datatype for the quantity field as a number I cannot type in ANY for quantity, but if I set the datatype as text, when I open a report controlled by the table the sorting of the numeric fields is wrong. The numbers are then treated as text so all the numbers beginning with 1 are kept together etc.

i.e.
100
1000
200
2000

instead of

100
200
1000
2000

Is there any way to keep the datatype as number with the exception of the word ANY or is there a way to keep the datatype as text and sort the records correctly in the report?

Thanks for any advice
Jim

 

[JimmyG]

My database contains a table of quotation details. One field specifies quantity. In most cases the higher the quantity quoted the lower the price offered. However in some circumstances a set price is offered regardless of quantity purchased.

If I set the datatype for the quantity field as a number I cannot type in ANY for quantity, but if I set the datatype as text, when I open a report controlled by the table the sorting of the numeric fields is wrong. The numbers are then treated as text so all the numbers beginning with 1 are kept together etc.

i.e.
100
1000
200
2000

instead of

100
200
1000
2000

Is there any way to keep the datatype as number with the exception of the word ANY or is there a way to keep the datatype as text and sort the records correctly in the report?

Thanks for any advice
Jim

 

[Mile-O]

You can't have a numeric field that stores specified text.

You can, use a text field, however to do the opposite but, as you've discovered, the sorting is textual and not numerical. In a query you can create a calculated field that converts text numbers to numbers and sort on that.

i.e.

SELECT IIf(IsNumeric([TextField]),CInt([TextField]),[TextField]) AS NewField
FROM MyTable;

 

[Brianwarnock]

If you keep the field as text and in the sort/grouping for the report enter =Val([fldquantity]) then I think all the ANY will sort first and the rest should be in numerical order.

Brian

 

[trucktime]

Sort

Assuming the report is based on a query you could try this.
Ad a new column to the query and in the Field Row put:

QuantityText: Str([Quantity])

This means you have converted Quantity to a string (text type)
Then sort this column Ascending

And please post your question in only one forum.

 

[JimmyG]

Thanks for your help but i'm unsure how to use this.

Which parts of the formula do I need to change to relate to my tables and fields?

Also do I enter this as a new field or does it become the criteria for my quantity field?

 

[Mile-O]

Have a look at the attached example:

 

[JimmyG]

Ok, I've integrated it into my query but it still doesn't sort the records as I need them.

I've checked the datasheet view of the example you created and that doesn't sort them out numerically either...

They are still appearing as text
100
1000
150
200
2000

Thanks for your help so far!

 

[Mile-O]

Sorry, didn't actually check if it worked. The only other thing I can think of right now is to replace the final [Example] in the query with -1.

When you add Text to numbers it automatically switches to textual sorting.

 

[Pat Hartman]

You should not be storing quantity as a text value. Quantity is used in calculations. Calculations require numeric fields. Use 0 in your lookup table to represent the "any" value. With the quantity stored as text, range checks will not work properly either for the same reason that sorting doesn't work as you expect.

 

[JimmyG]

If I keep the quantity as a numerical field then and use 0 to represent "any" is there any way to display 0 as the word "any" on my report? I'm not sure if this is possible in Access, but I think an IF formula would achieve what I need in Excel.

The problem is the report creates a current price list for customers which they receive. A "0" in the quantity field will confuse people!

Thanks again

 

[Brianwarnock]

You have 2 threads with the same name addressing the same problem, one in tables and one in reports, haven't checked out queries, general etc

Brian

 

[Mile-O]

• Have the quantity field hidden on your report. Call the textbox txtQuantity;
• Add an unbound textbox to your report
• Set the ControlSource of your unbound textbox to: =IIf([txtQuantity]=0,"Any",[txtQuantity])

 

[JimmyG]

Excellent!!!

Thanks for everyones help, I've got exactly what I needed now

Sorry for posting the question in tables and reports, I wasn't sure where the problem lay and wasn't sure whether my tables or report needed changing!

Thanks again

 

[Mile-O]

Both threads combined and moved to Reports forum.