Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting values by format?

    Hi everyone! Not even sure if this is possible... but I need your help, yet again! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    I am stripping some large reports and the program I'm using is not exactly perfect... I have some fields where the data needs to be cleared out once it gets to Access, if it does not meet a certain format...

    Specifically... I need to know how (if there is a way) to say in a query...

    If [Maturity]'s format is (numeric,numeric,"/",numeric,numeric,"/",numeric,numeric)
    Then just trim it and leave it there...
    Otherwise clear the field to blanks...

    My present query states:

    Maturity: Trim(IIf(InStr(1,[Mat],"/"),Trim([Mat]),""))

    ...but as it turns out, I am being left with incorrect field values such as "EXP 8/9/" and "3/01 RES"

    Sorry if this is a silly question... As always, thanks in advance! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting values by format?

    Just to let everyone know... I figured out a way to do this editing... I should have thought of this before!

    Maturity: Trim(IIf((Mid([Mat],3,1)="/" And Mid([Mat],6,1)="/"),[Mat],""))

    Thanks to everyone who thought about it... and sorry for wasting your time...

    Have a terrific day all! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Selecting values by format?

    If all you need to do is make sure the field contains a date, you can use the IsDate function:

    Maturity: IIf(IsDate([Mat]),[Mat],"")
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Selecting values by format?

    As Charlotte says, if you're really testing for dates, use IsDate.

    For general string testing, I usually find that the built-in pattern matching is clearer than extracting bits of the string with Left, Mid etc.

    E.g. If [Maturity] Like "##/##/##" Then ...

    will test for 2 digits followed by / followed by 2 digits followed by / followed by 2 digits...

    Jeremy

  5. #5
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting values by format?

    Thanks Charlotte... I didn't use IsDate because the field itself is Text format in the table... I thought it had to be Date/Time data type to use IsDate... Thanks for your help!

  6. #6
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting values by format?

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Thanks Jeremy! I had no idea that I could use the # sign in an expression like that! ...Now that I know how to find numeric digits, is there a symbol I could use to find an alphanumeric character (a-z or A-Z)? ...You'd think I would have read about this somewhere in the MANY books I have used for reference the past four months! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> ... Actually, come to think of it, I did see something about that in the formatting or input mask sections... but I didn't connect it to this... <img src=/S/baby.gif border=0 alt=baby width=15 height=15> Thanks again!

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Selecting values by format?

    Hi Alexya,

    yes you can in fact use Like "[A-Z]*" for example to match a string beginning with a letter in the range A to Z.

    Pattern matching is one of the great features of Access queries (and VBA if you get on to using that).

    Look up Like in the Access help, then select Like operator (Microsoft Jet SQL) and you'll see loads of examples of how to use it (this is from the Access 97 help but if you have 2000, there should be something similar).

    Jeremy

  8. #8
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting values by format?

    Wow, now I know that browsing through the messages is not a waste of time!

    If you can now tell me that you can parse strings into different variables, I will be one very happy camper - in PERL I can do it with pattern(pattern1)pattern(pattern2), which will give me pattern1 in $1 and pattern2 in $2, which can then be copied to myVar1 and myVar2. Can VB do this in some way?

    I love PERL, but not everyone has the interpreter on their computers, so I need to use something that is ubiquitous to be able to delegate some activities!

    kiwi44

  9. #9
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting values by format?

    Thanks a million Jeremy!! ...The examples are terrific! ...
    You're the best! Have a terrific day!!! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

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

    Re: Selecting values by format?

    You can use general expressions in VB, but Access VBA doesn't support them directly.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Selecting values by format?

    I think if you need to parse the string you still need to use the dreaded Mid$; Like is great for testing how a string looks but won't split it for you..

    I'm sure I've seen a post somewhere that suggests you can get a PERL add-in for VBA that would let you invoke its facilities, but it was a while ago and I don't have the details.

    Jeremy

Posting Permissions

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