Results 1 to 8 of 8

Thread: Query

  1. #1
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Angry Unanswered: Query

    I have table which stores Customer# and when i do query i would like to display result in Asc order. What do you do when u have customer#12 which Access will consider as #1 ?
    Skharva

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Give us a sample of the data in the table.

    In other words, is you data like this

    Customer Number
    1
    56
    3
    6
    4
    23
    123
    22

    and it is currently sorting like this

    1
    123
    22
    23
    3
    4
    56
    6

    and you want it to sort like this

    1
    3
    4
    6
    22
    53
    56
    123
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    Originally posted by PracticalProgram
    Give us a sample of the data in the table.

    In other words, is you data like this

    Customer Number
    1
    56
    3
    6
    4
    23
    123
    22

    and it is currently sorting like this

    1
    123
    22
    23
    3
    4
    56
    6

    and you want it to sort like this

    1
    3
    4
    6
    22
    53
    56
    123
    Yes, this is right. I know i can use In("1",....."56") for criteria of Order#
    But is there any other way to do this ?
    Skharva

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Is this field defined as text, or as a number?

    Does this field have anything in it other than numbers?

    Bottom line, if you have a field of numbers, but define that field as text, you are instructing Access to ignore the fact that these are numbers and treat them as text. So, in other words, you have specifically instructed Access to not treat these as numbers. Is that what you wanted to do?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    Originally posted by PracticalProgram
    Is this field defined as text, or as a number?

    Does this field have anything in it other than numbers?

    Bottom line, if you have a field of numbers, but define that field as text, you are instructing Access to ignore the fact that these are numbers and treat them as text. So, in other words, you have specifically instructed Access to not treat these as numbers. Is that what you wanted to do?
    This field is define as Text data type.

    Example of my data: Visit Type
    -----------
    2 Week Visit
    12 Month Visit
    6 Week Visit
    Skharva

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Unfortunately, if you have defined the field as text, you cannot expect the computer to recognize the values as numbers, unless you do some machinations.

    Why did you define it as text?

    Wait a second, I though we were talking about customer numbers. Now we are talking about visit frequencies.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    Originally posted by PracticalProgram
    Unfortunately, if you have defined the field as text, you cannot expect the computer to recognize the values as numbers, unless you do some machinations.

    Why did you define it as text?

    Wait a second, I though we were talking about customer numbers. Now we are talking about visit frequencies.
    I was just trying to give example as Customer#
    Skharva

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...so you have the wrong data type for visit frequency

    given that A stores dates as doubles (days dot fractional days) you could make a new field "interval" (double) and set it to
    14 (your two weeks)
    365.25 (sort-of-annual)
    42 (6 weeks)
    30.4 (sort-of-month)

    then you need to parse your current field into the new field

    instr() looking for "month" or "week" or "day"
    and note which of them you have and where it starts (and here you hope the text data entry has been reasonbly regular)

    then left$() to remove everything from the start of "month" or "week" or "day"

    then (possibly not needed) rtrim$(ltrim$()) to remove the spaces

    then cint() to get a number

    then multiply by a suitable constant depending on what you found("month" or "week" or "day")

    now you have an interval in "days" (e.g. which you can add to LastVisit (date) to find the date of the next visit)

    izy
    currently using SS 2008R2

Posting Permissions

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