Need Help with Criteria Expression

[Chu Gai]

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?

 

[Conan Kelly]

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]

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 addany
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

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

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?

 

[Conan Kelly]

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

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

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

 

[Chu Gai]

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.

- 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?

 

[Conan Kelly]

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.

- 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

 

[Chu Gai]

Chu Gai,
I would definately parse this field out into 3 separate fields. Createa
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

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.

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

 

[Chu Gai]

On Feb 29, 1:11 pm, "Conan Kelly"
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

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 alwaystwo
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,

YOU MEAN PEOPLE ARE ENTERING DATA THIS WAY!!!  This is not coming fromsome
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:

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 digitso 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'thave
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:

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.

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...

 

[Conan Kelly]

On Feb 29, 1:11 pm, "Conan Kelly"
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

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,

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:

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:

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.

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...

Chu Gai,

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

 

[Chu Gai]

On Feb 29, 2:47 pm, "Conan Kelly"
On Feb 29, 1:11 pm, "Conan Kelly"
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

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. WhatI
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...

Chu Gai,

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.

 

[Chu Gai]

"Chu Gai" <[email protected]> wrote in message

On Feb 29, 3:55 pm, "Conan Kelly"
On Feb 29, 2:47 pm, "Conan Kelly"
On Feb 29, 1:11 pm, "Conan Kelly"
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

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 canbe
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,

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 itis
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 datain 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 tosee
if every record is returning a valid date.

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.

 

[Conan Kelly]

"Chu Gai" <[email protected]> wrote in message

On Feb 29, 3:55 pm, "Conan Kelly"
On Feb 29, 2:47 pm, "Conan Kelly"
On Feb 29, 1:11 pm, "Conan Kelly"
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

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,

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.

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.

 

[Chu Gai]

I take it the value of -1 means it's a valid date. Phuck! OK to send
you a small set of data to work with Conan? I take it if I remove the
nospam stuff, it'll work.

 

[Conan Kelly]

Chu Gai,

-1 translates to TRUE (if I remember correctly). So that means everything
in your list is a valid date, but the IsDate() function might be able to
implicitly cast text to dates to test them, so I don't know for sure.

Yes, remove all of the "NOSPAM"s to send me email.

Conan

 

[John Spencer]

IsDate will return true if the value being checked is a legitimate date
string or datetime.

IsDate("12/1/2008") will return True
IsDate(#12/1/2008#) will return True
IsDate(Date()) will return True
IsDate(Null) will return false
IsDate("32") will return false


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================