Results 1 to 10 of 10
  1. #1
    ChazF is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2020
    Posts
    4

    Converting text and Nulls to numbers for a new field

    lost once again
    Last edited by ChazF; 09-08-2020 at 05:09 PM.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your description is not very clear - which of your descriptions is a field, which a table, what fields in which table

    Rather than describing, show us - Suggest show your relationships, some example data and what you want to see from that example data

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    If your trouble concerns empty fields, try the NZ function: example, for numeric fields NZ([Fieldname],0) returns the field value if it has a value and 0 if it's empty

  4. #4
    ChazF is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2020
    Posts
    4

    Re: Converting text and Nulls to numbers for a new field

    lost once again
    Last edited by ChazF; 09-08-2020 at 05:09 PM.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    really confused - where does the 3 come from on the first row? Is that from the column heading? and is the 2 from the column heading or the player code? It may make sense to you, but I'm struggling to see how this is supposed to work. At the moment I suspect your data is not normalised which makes things at best difficult or at worst impossible.

    So repeat my request for the relationships and what you want to see from that example data

    you say

    The fields [runner_1b], [runner_2b], [runner_3b] show the IDs of runners on 1st, 2nd, and 3rd base. If the filed is blank, then there is no one on that base.
    which I understand but then you say

    The fields [dest_runner1st], [dest_runner_2nd], [dest_runner_3rd] all show where the runners end up after the batter has got on base or made an out.
    - which says this is after something has happened - which is the 'current position' after this event?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Seems to me that you are trying to use a field name (or part of it) to generate data in another field if that field has a value in it. If that's the case, there must be a better way. Decisions about what to put in a field should be governed by the data in some other field and not the name of a field just because it has data in it.

    EDIT - maybe what you need is the Switch function, but I kind of doubt it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    ChazF is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2020
    Posts
    4
    lost once again
    Last edited by ChazF; 09-08-2020 at 05:10 PM.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK,

    we want to help, but you do not provide the information requested. Your expressions make no sense to me your len expression will either be 8 or generate an error, you might as well use 1, 2, 3 and not bother with an expression.

    If you can get it to work in excel, suggest stick with that. Access is a completely different application and works in a completely different way.

    good luck with your project but I don't think I can help any more

  9. #9
    ChazF is offline Novice
    Windows 8 Access 2007
    Join Date
    Sep 2020
    Posts
    4
    If you really cannot figure out what I need to do from what I presented, then perhaps you should remove the appellation "VIP" from below your name. Sorry that to have bothered you all and to have taken up your time.

    AND, AS I HAVE ALREADY EXPLAINED, WITH 97 FIELDS AND CLOSE TO 200,000 RECORDS, EXCEL REALLY ISN'T THE ANSWER.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by ChazF View Post
    If you really cannot figure out what I need to do from what I presented, then perhaps you should remove the appellation "VIP" from below your name. Sorry that to have bothered you all and to have taken up your time.

    AND, AS I HAVE ALREADY EXPLAINED, WITH 97 FIELDS AND CLOSE TO 200,000 RECORDS, EXCEL REALLY ISN'T THE ANSWER.
    Huummmm...... All I've seen from you is
    Click image for larger version. 

Name:	ChazF.png 
Views:	7 
Size:	25.0 KB 
ID:	42949
    in Posts #1,4 & 7, then a rude post with some YELLING...

    I haven't seen anything about 97 fields. I am pretty sure with that attitude, you are going to be blocked by a lot of members.
    If I say "Car Broke", can you tell me how to fix it?

    It would be very helpful if you posted your dB with an explanation of how to find the error/problem.


    I hope you can find a solution for your problem, but I can't try and help you - no info......

    Hope you day gets better...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 05-15-2017, 09:41 PM
  2. Replies: 7
    Last Post: 03-18-2013, 04:54 PM
  3. Converting date field in numbers
    By mercapto in forum Queries
    Replies: 5
    Last Post: 04-19-2012, 11:35 AM
  4. Replies: 8
    Last Post: 02-22-2012, 11:58 AM
  5. querying and converting text to numbers
    By hollyh in forum Queries
    Replies: 2
    Last Post: 09-01-2011, 12:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums