Re: Need Help with Criteria Expression

On Feb 29, 3:55 pm, "Conan Kelly"
<CTBarbarinNOS...@xxxxxxxxxxxxxxxxxxx> wrote:
"Chu Gai" <mushupo...@xxxxxxxxx> wrote in message

news:1fcbc382-5dd4-477e-b783-1d29f65c6adc@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Feb 29, 2:47 pm, "Conan Kelly"





<CTBarbarinNOS...@xxxxxxxxxxxxxxxxxxx> wrote:
"Chu Gai" <mushupo...@xxxxxxxxx> wrote in message

news:220ff0c8-82fd-40df-bc73-e2209cce4a83@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Feb 29, 1:11 pm, "Conan Kelly"

<CTBarbarinNOS...@xxxxxxxxxxxxxxxxxxx> wrote:
Chu Gai,

I would definately parse this field out into 3 separate fields. Create a
query that will do this and save this query. Then you can create another
query based off of this query that will be easier to work with.
Something
like this might help:

SELECT Left([TableName].[FieldName],2) as PersonsInitials,
DateValue(Mid(Left([TableName].[FieldName],3,InStr([TableName].[FieldName],­­­"#")))
as SentDate,
Right([TableName].[FieldName],Len([TableName].[FieldName])-InStr([TableName­­­].[FieldName],"#"))
as NumberSent
FROM [TableName]

Be sure to chang "TableName" and "FieldName" appropriately. You can add
any
other fields that you need to the query as well.

I have not tested this. I've typed it in to my response from memory, so
there is a chance for typos or incorrect use of functions. Let me know
how
it works out, and I can tweak it for you if it gives you trouble.

HTH,

Conan

"Chu Gai" <mushupo...@xxxxxxxxx> wrote in message

news:5c724097-a3df-42f1-9944-e6060c9b3ccf@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

One of the fields in a query has an alphanumeric term that looks like
this...

sb1/02/08#5

The first two characters represent the initials of the person who sent
the email. Different people have different intials but it's always two
characters.
The part in the middle that looks like a date is the date the email
was sent.
The #5, which could be #anynumber represents whether it was the first,
second, tenth, whatever email that was sent.

What I'd like to be able to do is to be able to pull the records for
the past two weeks starting from the current day. Kind of like
Date()-15. However, I'm striking out with figuring out how I can use
both wildcards along with a calculated date range.

I know this is ugly and maybe the data ought to be parsed into three
fields that could be concatenated after the fact, but this is the way
it's going to be for now. Any idea how to write the criteria
expression?- Hide quoted text -

- Show quoted text -
Well, I'll tell you what I did (BTW, I'm most definitely pretty
inexperienced in Access and just helping out the little lady here). I
wrote an expressions that looks like this...

Expr5: Format(Mid([CCC Control no],3,InStr(1,[CCC Control
No],"#")-3),"mm/dd/yyyy")

Now, this works fine with respect to pulling the date out of the
field. However, this then becomes my problem. I can't filter the
result to get data for the past two weeks like by >Date()-14. What I
get is a INVALID PROCEDURE CALL, the query comes up with #Names?. Got
an idea here?

Chu Gai,

If I remember correctly, the Format() function returns a string, so you
are
trying to filter text using a date. Try this:

Expr5: DateValue(Mid([CCC Control no],3,InStr(1,[CCC Control No],"#")-3))

That should convert it to an actual date, then your filtering method might
work.

Let me know how it works out.

HTH,

Conan- Hide quoted text -

- Show quoted text -
Well Conan, turns out the thing I wrote worked (your initial thoughts
reminded this dunderhead about the Instr function which led me to what
I posted above) AFTER I corrected a few typos that I found. I quite
agree with you about parsing the data into three fields, but at this
time, that just ain't gonna happen.

You've been enormously helpful Conan. Perhaps I can impose on you once
more. In a table, this particular field is called [CCC Control No]. No
brackets of course. Can you help me, based on my first post, with
setting a validation rule that's commensurate with the way data would
be entered?

Chu Gai,

Can you help me, based on my first post, with
setting a validation rule that's commensurate with the way data would
be entered?

YOU MEAN PEOPLE ARE ENTERING DATA THIS WAY!!!  This is not coming from some
other system/application?!?!?!?!  No offense, but this is a BAD design
(please forgive me if it is yours).  This table is storing 3 different
fields that should be 3 different data types in one field.

I strongly urge you to change this, but I guess I can understand, especially
if there are alot of queries/reports/forms that depend on this field.

I'm not too sure about a validation rule, but how about an input mask?
Maybe something like this:

<LL#0/00/00\#09;0;_

I'm not too sure about the first pound/number sign (#).  Help says "Digit or
space (entry not required; spaces are displayed as blanks while in Edit
mode, but blanks are removed when data is saved; plus and minus signs
allowed).", but I can't tell if the space is being removed.  It looks like
it is still there after the record is saved.  NEVER MIND!!!  I just tried
it.  If I actually enter a space, that is the same as entering a digit so it
stores the space.  If I skip over this character with the arrow key, then
the space is removed.

This input mask will allow for 1 digit months, 2 digit days, and 2 digit
years (if your dates are in the US format of m/dd/yy).  Also you won't have
to enter the date slashes or the pound sign, but it will store those
characters in the data.

If you want to require 2 digit months, then change it to:

<LL00/00/00\#09;0;_

Look up Input Mask in the Help for all of the details/codes that can be
used.

I'm don't think Validation Rule uses all of the same codes.  I don't know if
you could come up with a validation rule that will do the same thing.

HTH,

Conan- Hide quoted text -

- Show quoted text -

It's not mine Conan. There's a department that my wife has to deal
with and the way that department does things trumps matters and sets
the tone. Nothing that can be done about it. That's where it comes
from. Like I said, when I saw it, my thoughts echoed yours with
regards to three separate fields. This may still be implemented on her
end but right now, there's a lot of historical data to deal with.

I'll try your suggestion. Thanks again :)
.

Relevant Pages

  • Re: Need Help with Criteria Expression
    ... query that will do this and save this query. ... trying to filter text using a date. ... setting a validation rule that's commensurate with the way data would ... If I actually enter a space, that is the same as entering a digit so it ...
    (microsoft.public.access.queries)
  • Re: Editing Access Data
    ... > second query to an update query, and update both your phone field and your ... > last name, etc.), so why not combine that exercise with the "moving phone ... >> actual name is sometimes, also appended with a ten digit phone ...
    (microsoft.public.access.queries)
  • Re: Need Help with Criteria Expression
    ... query that will do this and save this query. ... setting a validation rule that's commensurate with the way data ... "Digit ... column (if it is not confidential and doesn't break any confidentiality ...
    (microsoft.public.access.queries)
  • Re: Insert a LIST into each line of 2nd file - WILL PAY $$$!
    ... the query edit window called "criteria". ... Being that one file has one field which is a 4 digit number ... I know I have more than sufficient disc space. ... I can avoid this part by simply removing records after the fact but ...
    (comp.databases.ms-access)
  • Re: Query for Left(String,5) - Access 2007
    ... Note that this query cannot use the query design view, but must be done in the SQL view. ... FROM ((boston INNER JOIN Name ON boston.Boston = Name.ZIP) INNER JOIN ... The only time I've used like is in the criteral when I'n only using the nameaddress table and then I'd type in all the likes manually - though usually there are only 4 or 5 zips. ... It will then find all matching 5 digits and all that start with the 5 digit code. ...
    (microsoft.public.access.queries)
Quantcast