Blank Fields in query (Access 97,Access 2000)

Topic

New Reply

Hi
Can anyone help please, I have a form with Miles to and Total Time I enter the mileage in the Miles to and Total time contains the formula below.
But in the Query only some of the time show. any ideas why please

=Int(2*[MilesTo]/50+0.5)/2

Braddy

Reply | Quote

Replies

Are you posting the mileage in the query? Are there nulls in the field?

Post your query for all to see.

Reply | Quote

Hi Patt

When you say are there nulls in the field I am not quite sure what you mean. If you mean are there some fields with no mileage in the answer is yes,

Braddy

Attached the query

Reply | Quote

From your screenshot, it appears that TimeTo is a field in Machine. It shouldn’t be a field in a table, it should be a calculated field in the query:

TimeTo:Int(2*[MilesTo]/50+0.5)/2

Reply | Quote

Hi Hans

The confusing thing is some of the results appear, but others do not, when you say it should be a calculated field in the query do you mean actually put the formula in the query fiield, if you do, then I tried that and everything was blank.

Sorry if seem a little bit vague!

Braddy

Reply | Quote

If you open the Machine table by itself, is the MilesTo field filled? If not, there must be something you haven’t told us yet.
If it is filled, try creating a new query based on Machine. Add all fields to the query grid except for TimeTo, and add a calculated column
TimeTo:Int(2*[MilesTo]/50+0.5)/2
Switch to datasheet view. Is TimeTo filled now?

Reply | Quote

Hi Hans

Your on the right trail, it is not filled in on the table only the form, see attached

Braddy

Reply | Quote

OK Braddy,

You have a problem here. The MilesTo field is left-aligned, so it must be a text field. You can’t perform calculations on text fields.

Open the Machine table in design view. Change the field type of MilesTo to Numeric; if it is always a whole number, leave the field length as Long Integer; if it can have decimals, set the field length to Double Precision. Save the table design.
The calculated field TimeTo:Int(2*[MilesTo]/50+0.5)/2 should work now.

Reply | Quote

Hans

I did what you suggested but as you can see some fields work and other do not.

Braddy

Reply | Quote

Hi Hans

I works when I use a calculated field instead of the timeto field.

Thanks very much for your help

Braddy

Reply | Quote

What happens if you rerun the query, do you see all the times? It is not clear how you are using the query. If you have values in MilesTo, then you should get values in the calculated field unless something else is going on. It seems like a silly suggestion, but have you tried moving the TimeTo column to the right of MilesTo?

Reply | Quote

Hi Charlotte

I get the times in the form, It’s only when I run the query that I get Blanks.

Sorry I took so long to answer but I did not get an e-mail response like I usually do!!

I moved the moved the columns as you suggested but to no avail

Braddy

Reply | Quote