Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Blank Fields in query (Access 97,Access 2000)

    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
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Blank Fields in query (Access 97,Access 2000)

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

    Post your query for all to see.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blank Fields in query (Access 97,Access 2000)

    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?
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Blank Fields in query (Access 97,Access 2000)

    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
    If you are a fool at forty, you will always be a fool

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Blank Fields in query (Access 97,Access 2000)

    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
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Blank Fields in query (Access 97,Access 2000)

    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
    If you are a fool at forty, you will always be a fool

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blank Fields in query (Access 97,Access 2000)

    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?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blank Fields in query (Access 97,Access 2000)

    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

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Blank Fields in query (Access 97,Access 2000)

    Hi Hans

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

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blank Fields in query (Access 97,Access 2000)

    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.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Blank Fields in query (Access 97,Access 2000)

    Hans

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

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Blank Fields in query (Access 97,Access 2000)

    Hi Hans

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

    Thanks very much for your help

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •