Results 1 to 11 of 11
  1. #1
    KathiRay is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    3

    Replace values in a query or report

    Hello, I couldn't find this through any search so hopefully someone can help, please!



    I want to show the result as different than the actual field in a query. For example, If the field says the month written out (January, February, March ...) and in the query or report result, can it be displayed as the month's number instead (1, 2, 3 ...), how is that accomplished? This would be not changing it in any original format.

    Thanks so much!

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,233
    might be an idea to show some example data and the required output. Normally, you store a date and a date can be formatted to show January/February/etc or 1/2/etc or pretty much any date type value you could wish for.

    e.g.
    ?format(date,"m")
    1
    ?format(date,"mm")
    01
    ?format(date,"mmm")
    Jan
    ?format(date,"mmmm")
    January

  3. #3
    KathiRay is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    3
    Hello, thanks for the response. Not a formatting situation really. I would like for it to display data differently than what is actually in the field. Not change the original field in any way. I've seen this done before but can't figure it out again and I apologize if I'm not explaining it well. Something that couldn't be changed only by adjusting the formatting of the field. Let me try this example: If the original field shows horse, the resulting display in the query should show as animal. And if original field shows daisy, the resulting display would show flower. Does that make sense and is it doable?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,852
    You need some sort of conversion routine.
    One approach could be a lookup table to substitute a value for a specific value.
    Code:
    FieldValue  SubstituteValue
    horse       animal
    dog          animal
    cow          animal
    rose         flower
    daisy        flower
    You would replace occurrences of fieldvalue with the associated substituteValue. You would not change the original values.
    What you would do is create a query and replace found values with the substituted values and use that query as the recordsource of the report. Your original data would not be changed.

    Consider an original, saved record

    Mary had a little lamb, she also owned a horse

    then using the substitution routine (yet defined but concept)
    converted for further processing
    Mary had a little lamb, she also owned a animal

    but the original remains
    Mary had a little lamb, she also owned a horse


    Simple routine for this example
    Code:
    Sub testsub4()
    Dim str As String
    str = "Mary had a little lamb, she also owned a horse"
    Debug.Print Replace(str, "Horse", "animal") & vbCrLf & str
    End Sub

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,070
    That's a head scratcher for sure - going from month values to flora and fauna for the same issue. I hesitate to suggest Month() function as a result but there it is. Even a query can do this for any form or report based on it: MonthOnly: Month(someDateFieldHere)
    I doubt it would matter in your case, but Format() will change your date value to a string.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,852
    Yes, it would be helpful if we had the actual data to see the issue and requirement rather than analogies.

  7. #7
    KathiRay is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    3
    My actual "data" was an assessment I was taking to gauge my level of Access knowledge. So the task I was given was to show, in a query, monthly as "12" and semi-monthly as "6". I didn't believe that I could do that just through formatting. That was the example I was given.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,226
    Whatever 'conversion' you want to do - it is done in query or textbox by: 1) format property setting or 2) calculation or 3) table join.

    Do you mean field has values 6 and 12 and you want to show "monthly" and "semi-monthly" or vice versa? Do this with an IIf() expression.

    IIf([field]=12, "Monthly", "Semi-monthly")

    If you want to change numbers 1 - 12 to month name: Format([fieldname] & " 1 2020", "MMMM")

    Going from month name to number: Month([fieldname] & " 1 2020")

    Or build lookup table as suggested by Orange.

    This is all really basic Access functionality.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,852
    Do you have a specific question or has your post been solved?
    SELECT queries are ReadOnly so won't change the values in your table.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,226
    @Orange, why do you say SELECT queries are ReadOnly? It is certainly possible to edit data via SELECT query.

    I think what you mean is opening a SELECT query does not itself modify data, unlike an UPDATE action SQL.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,852
    Yes - Select by itself, I didn't mention nor mean to mention action queries. The OP mentioned "query" in post #7.

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

Similar Threads

  1. Replies: 13
    Last Post: 03-22-2019, 04:09 PM
  2. Replies: 9
    Last Post: 02-22-2018, 08:11 AM
  3. Replies: 3
    Last Post: 10-30-2016, 05:50 AM
  4. Replace data when running report
    By Ekhart in forum Reports
    Replies: 1
    Last Post: 06-25-2016, 03:35 AM
  5. Replies: 3
    Last Post: 06-07-2012, 07:05 AM

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 - Senior Forums