Issues with >= And <= query .. and text data.

[edmscan]

I have a table with a field called TX (text 15) and I would like to run a query against that field.



SELECT DISTINCT TAFL.TX, COMPANY.ADDRESS FROM COMPANY INNER JOIN TAFL ON COMPANY.COMPANY = TAFL.COMPANYCODE WHERE TAFL.TX >= [Forms]![frmMain]![txtLow] AND TAFL.TX <= [Forms]![frmMain]![txtHigh] ORDER BY TAFL.TX

Sample data ..

00.27220
05.60540
35.88000
122.70000

For example .. if I put in 30.00000 and 98.00000 in the txtLow and txtHigh fields .. it does not return the desired values.

I know .. it is a text field. But .. I cannot change it to a number field as the data is imported from a flat file that I have to massage even to get the data to the above.
The moment I change it to a double for example .. it corrupts the data.

A sample from the flat file is 000122700000 is really 122.70000

Looking for suggestions.


Thanks in advance ...
Kevin

[June7]

Try:

Format([fieldname], "0000.00000")

Now sort and filter on that constructed field.

SELECT DISTINCT TAFL.TX, COMPANY.ADDRESS FROM COMPANY INNER JOIN TAFL ON COMPANY.COMPANY = TAFL.COMPANYCODE WHERE Format(TAFL.TX, "0000.00000") BETWEEN [Forms]![frmMain]![txtLow] AND [Forms]![frmMain]![txtHigh] ORDER BY Format(TAFL.TX, "0000.00000");

Will have to make sure the values input into the form textboxes have the same structure. Maybe should use comboboxes.

[edmscan]

I should indicate a few things .. but I think we are close

I have the user enter the data .. in the text box, and I pad the data to ensure it is "32.40000" .. basically some text with 5 decimal places.

So .. 30 would be 30.00000 and 98.4 to 98.40000

I should note ... anything over 100.00000 works just fine. It is only numbers < 100.00000 that I get the wrong results.

But I am getting in the results ... using 30.00000 and 98.00000

30.42000
304.20000
311.20400

Query right now is ..

SELECT DISTINCT TAFL.TX, COMPANY.ADDRESS FROM COMPANY INNER JOIN TAFL ON COMPANY.COMPANY = TAFL.COMPANYCODE WHERE Format(TAFL.TX, '#####.00000') BETWEEN [Forms]![frmMain]![txtLow] AND [Forms]![frmMain]![txtHigh]

I took out the order by for now .. to make it easier.

[edmscan]

SELECT DISTINCT TAFL.TX, COMPANY.ADDRESS FROM COMPANY INNER JOIN TAFL ON COMPANY.COMPANY = TAFL.COMPANYCODE WHERE Format(TAFL.TX, '#####.00000') BETWEEN [Forms]![frmMain]![txtLow] AND [Forms]![frmMain]![txtHigh] and Format(TAFL.TX, '#####.00000') < 100

This seems to work .. for numbers < 100

So .. I will just use it for both numbers < 100 .. but I need to work on when the higher number is > 100

[June7]

Don't use # use 0.

The string needs placeholder leading zeros. That's why I use 0. How high can the values go? Use however many 0's needed to assure consistent length string.

An alternative may be to use Val() function to convert to a numeric value and use that constructed field for sorting/filtering.

[edmscan]

The number range from 00.20720 to 992.00000

I am almost there .. I can get the data I need if both numbers are < 100 .. or both numbers are > 100 (those 2 are fine)

But I need to get a Select that works where the LOW < 100 and HIGH > 100 .. this is the only one that does not work.

[June7]

I edited my previous post to offer another option. Might look at again.

[edmscan]

Ok. I will try it .. but probably will be a few days. Run out of time for right now.

Thanks ..

[June7]

The Format() approach works if you use what I suggested.

[edmscan]

The Format() approach works if you use what I suggested.

I have to go .. but my brain is not functioning well. Wondering if you could post a select for me .. ? (only the Low < 100 and High > 100 is the issue now .. the rest are working fine).

Thanks June .. I will work at it some more later tonight.

[June7]

Don't understand.

Are you using the query example I show in post 2? It will work for all values less than 10000.

Or, use the Val() function and will work for any number value (up to Access limitations for working with numbers).

[edmscan]

Ah, the interesting thing, I managed to find a much much better solution that makes this all go away.

This whole thing just amazed me from the start .. that is that the data got corrupted when I tried to change it to a numeric data type.

Well .. the solution, create a 2nd field (a real number this time) and do an update statement to fill that field from the data from the 1's field.
(the first field I will just keep to import the data from the flat file). There is only 80,000 rows .. so I am fine.

There is no problem anymore. The 2nd field is now a proper number and no more issues.

I have been up til the early morning building this app for days .. 95% done now. No more issues now that I have real live good data.

Thanks June

[June7]

This is a periodic import? So you will have to run the update each time. Yes, that is one way to deal with. Glad you have a working solution. Congratulations on your discovery.

[edmscan]

This is a periodic import? So you will have to run the update each time. Yes, that is one way to deal with. Glad you have a working solution. Congratulations on your discovery.

The data will be maybe every 3 months .. so not that often. I have the import and needed data manipulation all done. One button click is all I need.

Yes I am very happy .. as it will obviously work as needed now.

As always thanks for your help. It has been .. like a week of all nighters getting this app into production phase.