I don’t won’t to show a field when it is empty.

[waterdamage]

I don’t want to show a field when it is empty.

Can anyone help me with this small problem?

Using expression builder what expression do I use to not display the filed in my query if the field is formatted as currency and is blank (showing £00.00)

 

[The_Doc_Man]

In a query you don't have a lot of choices, but there is always an IIF that might help.

IIF( [x]=0.0, " ", FORMAT$( [x], {some-currency-format} ) )

Note that if the field is formatted for currency but is not stored as such, the above might not work as planned.

You might also consider this, which should suppress the field if it DISPLAYS as 0 even though it isn't really zero.

IIF ( FORMAT$( [x], {format-string} ) = "0.00", " ", FORMAT$( [x], {format-string} ) )

Not displaying the field in a query is a little questionable, though, unless you don't have a report to manage this display. In other words, working directly from a query and worrying about displayed format is a little odd since most of use work through FORMS when displaying real data.

 

[waterdamage]

Thanks for the reply.
Maybe I’m going around this the wrong way.

I want to produce a printed document that will end up as an estimate. I want to be able to enter data and have a price display to the right of the data for totaling later.
I may want to do this several times building the estimate; I estimate that the maximum amount of tiles I would ever want to do this would be 25.

So in my table I was thinking of having 25 fields for the text and 25 for the prices.
The problem is when I run my query I don’t want to show the fields that I’m not using.

Should I set this up in a different way?

 

[Pat Hartman]

You have your spreadsheet cap on and you need to put your relational dtabase cap on. The solution in a spreadsheet is to add columns but in a database the solution is to add rows. That means that instead of having a fixed 25 columns as you would in a spreadsheet, you will end up with a variable number of rows. So you only store and report on the data you have values for.

To "flatten" a vertical table into a horizontal spreadsheet, use a crosstab query.

 

[waterdamage]

Thanks very much for taking an interest in what I’m doing, yesterday when I read your reply “bingo”, and went off and played around with crosstab queries, now after staying up most of the night and spending time today, with my wife looking at them I’m not sure that they are what I need. I must say that I did at first think that they would work, one of the problems that I found is that you cannot use ( [ enter data] ) this in the criteria area of any of the fields, I tried to do a query on a crosstab query and still I could not perform this little trick, all in all, I’m just not sure I’m going around this in the correct manner.

Let me explain again what I wont to do maybe I have been misleading you.

I want to print an estimate for a customer
I want a table that holds customers data

Apart from the customer details and my company details the only other data will be the estimate data.



EstimateID
CustomerID
EstimateDate
DescriptionOfWorks
PriceOf Work

However I may have to enter up to 25 different descriptions and prices
Douse my tables have to have 25 fields for descriptionOfWork and 25 fields of PriceOf Work ?
Making my table 53 fields wide.

If this is so when I create a report to print I don’t won’t to print blank fields how I can stop this.

Thanks.

 

[Pat Hartman]

I understand what you're trying to do. I'm trying to tell you how to structure the table that holds your data. Crosstabs are not updatable recordsets because they aggregate data and Jet has no way of finding the record that goes with the pivoted columns. That means that your data entry form will most likely have a subform where each description and price will be entered on a separate row in the form. Only as many rows as you have data for will ever be created. That means that your reports will not have extraneous empty fields to worry about.

 

[waterdamage]

Over the last week I have played extensively with crosstabs as it has been suggested that this is the way for me to go. As suggested in the forum, looking through sample dbs is a good way to learn. I have found that the way the MS sample db Service Call Management.db ( attached if you don’t have it) sets out my problem exactly.

Clicking on the form workorders by customers brings up a form that looks to me to be a subform. This subform shows data of records that are summarized If you double click this data it takes you to a form to enter more data.
I’m assuming this is carried out without using crosstabs, as I cannot find any reference in the db to suggest that there are crosstab queries.
As this works so well I have decided to go with this format and have been able to get most of the actions to work in my new test db however I am stumbling on one point.

In my test db I can get the subform to run perfectly (not hard as the wizard sets it up) scroll through the contacts and the contact ID changes in the table linked to the subform showing that record. I invoke a macro so when I click on the data in the subform my Items Form is displayed. How do I get the estimate ID and contact ID data to display in the Items Form?
If it displays, will I be able to generate records in this form that will save the same estimate and contact IDs based on the estimate ID in the estimate subform that I just came from?

Hope I have explained myself correctly.