Data dropping from short text field that has time duration

[jpl458]

I have a table that is created from a csv file. In the file is a field named Duration and in the CVS file it looks like this:

,"2m 32s",, which is 2 minutes and 35 seconds for that record

I use that CSV file as input to a make table query, and the result is good with the duration field, which is Short Text, and looks like this:

Next I use that table as input to a table where the data reordered, Aand the Duration field is still Short Text, but the Duration field in the new table looks like this:

If I run a query against the first table and only look at duration it works fine. there are no masks or formats involved in any of the tables or queries, so i can't figur out why the duration is not displayed in its original form.

 

[June7]

Post your SQL and any other relevant code.

 

[plog]

I don't really know what your issue is. I mean, if you sort Short Text in Ascending order, all the '0s' records are going to be at the top. In the table you are having issue with sort it Duration by Descending and you should get records other than '0s' at the top.

However, I don't think your process is correct:

I use that CSV file as input to a make table query, and the result is good with the duration field, which is Short Text, and looks like this:

What's the point of that make table if you are not getting your data into the correct type? I understand importing a file and making a table based on that, but the point of that make table should be to get your data correct. Your time field should either be a Date/Time field or a numeric field where you convert everything into seconds. It should not be a Short Text field. Again, I understand it coming in that way, but the first make table query should fix the data types.

 

[Pat Hartman]

when you import, parse the field and create a minutes field and a seconds field and only import the number. That allows you to sort and do arithmetic.

 

[The_Doc_Man]

The first question that comes to mind is, what did you want the field to look like in the final table?
Show us a few items you would see from input and tell us what you want to see as the corresponding final values from the process.

 

[jpl458]

Post your SQL and any other relevant code.

The problem was with me, not the data. It turns out that the data I selected should have a zero in the duration field. Pilot error.

Thanks

 

[jpl458]

The first question that comes to mind is, what did you want the field to look like in the final table?
Show us a few items you would see from input and tell us what you want to see as the corresponding final values from the process.

The problem was with me, not the data. It turns out that the data I selected should have a zero in the duration field. Pilot error. Sorry for the inconvenience.

 

[The_Doc_Man]

Ah, I've heard of those. Sometimes we give ourselves the bumpiest ride we've ever had due to pilot error. I can sympathize completely.

 

[jpl458]

Ah, I've heard of those. Sometimes we give ourselves the bumpiest ride we've ever had due to pilot error. I can sympathize completely.

Sometimes it pays to LOOK at what you are doing instead of assuming how things work.