Problem with Format of fields in query

[rnutts]

Hi

I have two queries, one captures the last test date, formatted at the minute as a 'general number' using Format([Field],"0") on a piece of plant and the other captures the interval between tests on same piece of plant
When I add a spcific number to either field in my joining query they act as numbers, however if I try to add them together they add together as text

Example

DOPTestdate = 40629 - as a number. If I add 10 to this in another expression on the query it returns 40639
DOPRecuringDate = 180 - as a number. If I add 10 to this in another expression on the query it returns 190

If I create the expression [DOPTestdate]+[DOPRecuringDate] I get 40629180.
In the build up queries I have made sure that the fields are number format.

Can someone give me some pointers, I have had a look around the net and tried the Format([field]),"0") solution but to no effect.

Many thanks

Richard

 

[plog]

Format() returns a string. It may have only numeric characters, but once you throw something through Format() you've got a string. When you add strings together, it simply appends the second string to the first.

My suggestion is to not use Format() in a query at all, or at least not a query that will be the building block of other queries. Save Format() for reports.