InfoPath Dev

Per your request, I am attaching the DB and InfoPath Template that I am having problems with.  Everything works except when I attemt to submit to the Access DB.  Entries without the decimal point work good.

This has reminded me why I hate Access.

I would seriously recommending a switch to SQL or SQL Express as these little format things are much simpler since it does not try to hold your hand on everything.

Two things.

One - you need data, at least one row in your table. InfoPath also uses the data to determine what type of field it is or to create in the main schema. 

Two - I switched the format of a number field (In Design view and in the properties for the field) to fixed so it showed two decimal places in Access.

Then run through the data connection wizard again - modify - next - etc.

You will notice then that it will change the field type in InfoPath to a float instead of an integer and you can set the decimal places etc.

Ironically I have made a very similar weight form before. If you are going to be distributing this to a wide audience you really need SQL, since it is made for such a use, and will perform better and make your life easier.

Is it possible you could send me back what you did?  I went in and did everything that you said but I don't see the field name change in InfoPath.  I would like to compare what you did to what I have done so I can better understand where I went wrong.  In the meantime, I will keep trying to figure out what I have missed.  Also, thanks so much for your advice about SQL.  I am working into this. .

Sure.

I updated the weight and height fields only to test.

My guess is you need to go through the data connection wizard again.  It will reevaluate the table automatically.

I apologize but try as I may, I feel somewhat defeated by what I thought shoiuld be so simple.  The databae is set correctly for a decimial point with 2 numbers following.  If 

my InfoPath form was not attached to a database, there woudl be no problem.  I am able to format my fields using the format function under properties to change to data type, and all my calculations work great with no validation errors.  However, because it is attached to my database, the properties box does not give me the option to use Format to change the data type (it is grayed out). 

If I enter whole numbers in the weight or height fields the formulas work perfectly in the WeightCal or HeightCal fields with no red validation lines.  If I use number with decimals like 150.35 or 70.5, the weight and height fields as well as the WeightCal and HeightCal fields all have valiation lines around them.  The final field (Curr BMI) has a red line around it regardless of what numbers are put in the other fields

I have attached a sample containing just the fields I have spoken to.  I figure if there is any way to get this working, I can go back to the original design.

Your problem is still in Access. Did I mention I don't like Access? :)

The fields are not formatted right, and therefore when you run the data connection in InfoPath it does not handle them right either.

Open your table in design mode.

Click on the first weight field and see the format entry of fixed I put in. Also notice how when you view the data it shows 2 decimal places. In your form's data source, the weight field I fixed is a float, where all the others are ints, which only allow whole numbers. This is because the data in your table only shows whole numbers. If you enter data it just rounds it even though it is setup for decimals. This is what bugs me about Access, you have to specifically format.

You need to change the rest of the fields to fixed also. So that they all show the decimal places in Access.

Then rerun your data connection wizard and they will all be floats in InfoPath so you can configure the decimal format and they can handle decimal values.

I still think BC is going to need a SQL database for this and it would make your life simpler in many ways.

Have Fun

Thanks for your response.  You know, I love Access but putting the 2 programs together like this is driving me nuts.  Did I tell you that I still look at myself as a "newbie" who learns something new everyday, mostly in forums from patient folks like yourself.  I have changed all the fields to fixed with 2 decimal places as appropriate, re-ran the data connection, but to now avail.  Attached is a picture of what it looks like. 

Forgive me for sounding a bit dense, but when you speak of a need for an SQL database, I guess you lost me.  Obviously, SQL is computer lanquage that is part of the database so what am I missing here?  It is pretty amazing that I can do a lot with coding within the database, but I am not clear on your direction here.  How would that differ from what I have?

Hello again.  Just wondering if you have had a chance to view my attachment following the appropriate changes.  thanks.

SQL Server or SQL Server Express, which is free and you can run on your workstation are similar to Access but are designed for multiple users and have some added features. I am sure you could transition pretty easily.

 So you can see all of your fields in Access with 2 decimal places?

You modified your data connection and went through it?

Your fileds now in your data connection should be of type opt_float rather than opt_int.  Can you verify this in properties?

Then you text box controls should allow you to select the format and how many decimal places.

 If you need some one on one instruction/training via a web meeting to get this straightened out you can purchase support credits at the Qdabra store http://www.qdabra.com/products_all.asp?cat=12

It is interesting that although everything was done as you have indicated, my fields in the data connection are of opt_int.  As an additional test, I created a new database, and from the blank shee went through the data wizard and set that up, and dragged in 2 fields for a test of the calculations.  Here again, the properties were opt_int.  Because of this, I went back to the database and started to do some testing/changing. 

Believe it or not, I finally got it all working.  Instead of using the long intreger, fixed and 2 decimals, I used double, fixed and Auto. When I ran the data connection again, my text box ccontrols then showed the type as optional_double where the format button finally brings me to a page where I can set the decimal places to 2.  I was not able to get to opt_float as you had indicated.

It all works, and you were right from the get go.........it was in the data base table.  It sure took a lot of figuring to get it to work.  A huge thank you for your assistance and for pointing out the many things to check and consider.  This is what altimately led me to solution.