Re: Need Help with Criteria Expression


"Chu Gai" <mushupork1@xxxxxxxxx> wrote in message
news:6e9ae220-ccda-41c5-9cd2-f78db204d0c2@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Feb 29, 6:10 pm, Chu Gai <mushupo...@xxxxxxxxx> wrote:
On Feb 29, 6:00 pm, "Conan Kelly"





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

news:29ccdea5-e87c-4b69-b530-d3da79444e7f@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Feb 29, 4:05 pm, Chu Gai <mushupo...@xxxxxxxxx> wrote:

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@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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 :)- Hide quoted text -

- Show quoted text -
OK, I went with your method of representing the date since it sorts
correctly while mine...well it doesn't ;)

However, if I then type into the criteria that I want to see dates
later than a particular time like this...
#2/1/2008# or >Date()-14 (I'm assuming that means for the past 2
weeks),
then what I get is a message that says, "Date Type Mismatch in
Criteria
Expression". OK, got a fix > > my new found friend? :)

Chu Gai,

Date()-14 (I'm assuming that means for the past 2 weeks)

Yes, that should calculate the last 2 weeks from today's date.

Just out of curiosity, why both >#2/1/2008# and > Date()-14. If
returning
everything from 2/1/2008 and later, that includes the last 2 weeks. Have
you tried each criteria separately, by itself, to see if one will work
vs.
the other.

I don't know. I tried your criteria on a date field here and it worked
fine, but then, I didn't recreate the exact situation you have. If it is
possible, could you send me a small sample of the data in this funky
3-in-1
column (if it is not confidential and doesn't break any confidentiality
agreements) and the exact expresion you used to parse out the date from
that
mess. If you can, I'll mess with it here and see if I can figure out
what
is going on.

I'm guessing that AC still may not be returning a Date data type and it
can't implicitly cast to a date. Or maybe one of the records has data in
it
that is not a valid date so it can't make a date out of it.

You might try using the IsDate() function to test this new date field to
see
if every record is returning a valid date.

HTH,

Conan- Hide quoted text -

- Show quoted text -

As to why both, I was playing with various ideas. I'll have to check
with the little lady to find out just how confidential it is. Again
Conan, I thank you for your patience.- Hide quoted text -

- Show quoted text -

Using the IsDate on the calculated field returned a value of -1.

It returned a -1 for all records? If so, I don't know what else it could be
without working with the data hands on. Sorry.



.

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: 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: 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 would ...  If I actually enter a space, that is the same as entering a digit so it ... This input mask will allow for 1 digit months, 2 digit days, and 2 digit ...
    (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 would ... then what I get is a message that says, "Date Type Mismatch in Criteria ... column (if it is not confidential and doesn't break any confidentiality ...
    (microsoft.public.access.queries)