Suppress zeros in a query

[bearcub]

I have a query which shows a zero in phone field even though the table setting are set to short text. I have 5 data bases that are built off of the same template but 2 of 5 are showing zeros in the phone fields. Is there a way to suppress zeros from being shown in the query even though that zero is actually text not numeric.

I looked at the Excel sheet where the table is linked to and it is formatted as General. I looked at the Access table data type and they are formatted as short text. Is there some way to suppress these zeros from showing in the query so the zeros will be changed to "".

Thank you for your help,

Michael

 

[Joe4]

Maybe a calculated field something like:

Phone:IIF([FieldName]="0","",[FieldName])

or

Phone:IIF([FieldName]=0,"",[FieldName])

 

[Micron]

make sure the form field doesn't have a default value set (properties sheet, data tab). Also, the fact that a table field is set to text doesn't prevent you from setting a default of zero (0) in that field, but I presume you would have checked that already.

 

[bearcub]

Yes, I already checked that. I think what happened is that I had imported linked Excel files in Access overwriting the existing linked table. Does this happen often?

 

[Micron]

Not sure what you're saying. If in Access, you link a spreadsheet as a table, it gets updated automatically. However, one of the inherent problems with this is that it's generally accepted that Access bases the data type estimate (at least for imported, non-linked tables) based on a certain number of rows when importing. This seems to be an accepted fact, but the number of rows reported varies. I think it is 8. So for a column of numbers, an empty spreadsheet cell or one that contains anything that isn't a number, can coerce the data type in Access to be text if that occurs within that number of rows. Access will even automatically set a default of 0 in a number field when the table is created. Thus the reason you can end up with zeros in your form can be caused by things other than the way you set up a table initially. Actions that you take later can alter the initial design (such as over writing the table and ending up with a default in the new table).

This is why it's often said that it's better to have a native table in addition to the linked (or imported spreadsheet) and populate the "main" table via a query. As long as the table is designed in a way that will accommodate your possibilities, the data should go into the main table and maintain its proper type/format. An exception would be if your table field must only contain numbers and someone puts text in the related spreadsheet column, and that text bears no resemblance to a number.

 

[bearcub]

That's interesting. This sounds like what happened. Since I imported and over rode the existing table with a new linked table it must have added "0" to the text field.

Thank you for the explanation,

Michael