Date query

[Mark]

Hi,
I am attempting to write a query that must select the
following records:
All records that expire within a 6 month time frame from
the end date whenever the query is run and contain the
uppercase letters "*AF*" somewhere in the description
field. This could include combinations like GAF, GAF's,
FAF, etc.
The end date data type is set up as Date/Time and the
description data type is set up as text and I am using
A97. Any help would be greatly appreciated. Thanks!
Mark

 

[Brian Camire]

You might use a criteria on your description field like:

Like "*AF*"

and on your end date field like

<=DateAdd("m",6,Date())

 

[Dan S.]

I would probably use the DateAdd function for this.
Basically, for the criteria for the End Date field, I
would put < DateAdd("m",6,Date()). This is basically
saying that you want everything where the end date is less
than six months from the system date (today's date).

For the criteria in your description field, just put
like "*AF*"; that should do the trick for you.


HTH,

Dan.

 

[John Vinson]

Hi,
I am attempting to write a query that must select the
following records:
All records that expire within a 6 month time frame from
the end date whenever the query is run and contain the
uppercase letters "*AF*" somewhere in the description
field. This could include combinations like GAF, GAF's,
FAF, etc.
The end date data type is set up as Date/Time and the
description data type is set up as text and I am using
A97. Any help would be greatly appreciated. Thanks!
Mark

It sounds like you just need two query critera: the description is
easy - just put

LIKE "*AF*"

on the criteria line.

For the expiration date, just what do you mean by "records that expire
within a 6 month time frame from the end date"? What determines that a
record "expires"? GUESSING here, if you mean that you want to retrieve
those records where [End Date] is more than six months ago, a
criterion on [End date] of

< DateAdd("m", -6, Date())

will do the trick.

 

[Mark]

Thanks. I've tried both of these in the criteria field,
but they do not work.
< DateAdd("m",6,Date()) returns all records that expire
within 6 months and all records that have already expired.
like "*AF*" returns all records where AF are together - I
only want the uppercase AF.
Mark

 

[John Vinson]

Thanks. I've tried both of these in the criteria field,
but they do not work.
< DateAdd("m",6,Date()) returns all records that expire
within 6 months and all records that have already expired.

What in your table determines whether a record has expired or not?

like "*AF*" returns all records where AF are together - I
only want the uppercase AF.

Ah. You didn't SAY that. Access queries are *not* case sensitive!
You'll need a more complex expression:

LIKE "*AF*" AND StrComp(Mid([fieldname], InStr([fieldname], "AF"), 2),
"AF") = 0

 

[Mark]

Thanks, John
I got the date problem to work by using; Between Date()
And DateAdd("m",6,Date()). However, when I use LIKE "*AF*"
AND StrComp(Mid([fieldname], InStr([fieldname], "AF"), 2),
"AF") = 0, Access is placing the zero in quotes and giving
me an 'Invalid Procedure Call' error. Any ideas? Thanks.
Mark

-----Original Message-----

Thanks. I've tried both of these in the criteria field,
but they do not work.
< DateAdd("m",6,Date()) returns all records that expire
within 6 months and all records that have already

expired.

What in your table determines whether a record has expired or not?

like "*AF*" returns all records where AF are together - I
only want the uppercase AF.

Ah. You didn't SAY that. Access queries are *not* case sensitive!
You'll need a more complex expression:

LIKE "*AF*" AND StrComp(Mid([fieldname], InStr ([fieldname], "AF"), 2),
"AF") = 0



.

 

[Brian Camire]

OK. Instead of use the criteria LIKE "*AF*" on your description field, you
might enter the following expression

InStr(1, [fieldname], "AF")

in the Field row of an empty column of the grid in query design view, and
then, in that same column, enter

<>0

in the Criteria row.

Thanks, John
I got the date problem to work by using; Between Date()
And DateAdd("m",6,Date()). However, when I use LIKE "*AF*"
AND StrComp(Mid([fieldname], InStr([fieldname], "AF"), 2),
"AF") = 0, Access is placing the zero in quotes and giving
me an 'Invalid Procedure Call' error. Any ideas? Thanks.
Mark

-----Original Message-----

Thanks. I've tried both of these in the criteria field,
but they do not work.
< DateAdd("m",6,Date()) returns all records that expire
within 6 months and all records that have already

expired.

What in your table determines whether a record has expired or not?

like "*AF*" returns all records where AF are together - I
only want the uppercase AF.

Ah. You didn't SAY that. Access queries are *not* case sensitive!
You'll need a more complex expression:

LIKE "*AF*" AND StrComp(Mid([fieldname], InStr ([fieldname], "AF"), 2),
"AF") = 0



.

 

[Mark]

Thanks, Brian, but I am still picking up all of the
lowercase combinations of "AF".
Mark

-----Original Message-----
OK. Instead of use the criteria LIKE "*AF*" on your description field, you
might enter the following expression

InStr(1, [fieldname], "AF")

in the Field row of an empty column of the grid in query design view, and
then, in that same column, enter

<>0

in the Criteria row.

Thanks, John
I got the date problem to work by using; Between Date()
And DateAdd("m",6,Date()). However, when I use LIKE "*AF*"
AND StrComp(Mid([fieldname], InStr([fieldname], "AF"), 2),
"AF") = 0, Access is placing the zero in quotes and giving
me an 'Invalid Procedure Call' error. Any ideas? Thanks.
Mark

-----Original Message-----
On Thu, 20 Nov 2003 10:48:55 -0800, "Mark"

Thanks. I've tried both of these in the criteria field,
but they do not work.
< DateAdd("m",6,Date()) returns all records that expire
within 6 months and all records that have already expired.

What in your table determines whether a record has expired or not?

like "*AF*" returns all records where AF are

together -
I

only want the uppercase AF.

Ah. You didn't SAY that. Access queries are *not* case sensitive!
You'll need a more complex expression:

LIKE "*AF*" AND StrComp(Mid([fieldname], InStr ([fieldname], "AF"), 2),
"AF") = 0



.

.

 

[Brian Camire]

OK. Try

InStr(1, [fieldname], "AF", 0)

instead.

Thanks, Brian, but I am still picking up all of the
lowercase combinations of "AF".
Mark

-----Original Message-----
OK. Instead of use the criteria LIKE "*AF*" on your description field, you
might enter the following expression

InStr(1, [fieldname], "AF")

in the Field row of an empty column of the grid in query design view, and
then, in that same column, enter

<>0

in the Criteria row.

Thanks, John
I got the date problem to work by using; Between Date()
And DateAdd("m",6,Date()). However, when I use LIKE "*AF*"
AND StrComp(Mid([fieldname], InStr([fieldname], "AF"), 2),
"AF") = 0, Access is placing the zero in quotes and giving
me an 'Invalid Procedure Call' error. Any ideas? Thanks.
Mark
-----Original Message-----
On Thu, 20 Nov 2003 10:48:55 -0800, "Mark"

Thanks. I've tried both of these in the criteria field,
but they do not work.
< DateAdd("m",6,Date()) returns all records that expire
within 6 months and all records that have already
expired.

What in your table determines whether a record has
expired or not?

like "*AF*" returns all records where AF are together -
I
only want the uppercase AF.

Ah. You didn't SAY that. Access queries are *not* case
sensitive!
You'll need a more complex expression:

LIKE "*AF*" AND StrComp(Mid([fieldname], InStr
([fieldname], "AF"), 2),
"AF") = 0



.

.

 

[Mark]

Sorry, Brian, still didn't work. This time when I show the
expression every line reads "#Error".

-----Original Message-----
OK. Try

InStr(1, [fieldname], "AF", 0)

instead.

Thanks, Brian, but I am still picking up all of the
lowercase combinations of "AF".
Mark

-----Original Message-----
OK. Instead of use the criteria LIKE "*AF*" on your description field, you
might enter the following expression

InStr(1, [fieldname], "AF")

in the Field row of an empty column of the grid in

query
design view, and

then, in that same column, enter

<>0

in the Criteria row.



Thanks, John
I got the date problem to work by using; Between Date ()
And DateAdd("m",6,Date()). However, when I use LIKE "*AF*"
AND StrComp(Mid([fieldname], InStr

([fieldname], "AF"),
2),

"AF") = 0, Access is placing the zero in quotes and giving
me an 'Invalid Procedure Call' error. Any ideas? Thanks.
Mark
-----Original Message-----
On Thu, 20 Nov 2003 10:48:55 -0800, "Mark"

Thanks. I've tried both of these in the criteria field,
but they do not work.
< DateAdd("m",6,Date()) returns all records that expire
within 6 months and all records that have already
expired.

What in your table determines whether a record has
expired or not?

like "*AF*" returns all records where AF are together -
I
only want the uppercase AF.

Ah. You didn't SAY that. Access queries are *not* case
sensitive!
You'll need a more complex expression:

LIKE "*AF*" AND StrComp(Mid([fieldname], InStr
([fieldname], "AF"), 2),
"AF") = 0


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps? loc=us&access=public
.



.

.

 

[Mark]

My mistake, the expression does not show "#Error", but it
still returns all records.

-----Original Message-----
OK. Try

InStr(1, [fieldname], "AF", 0)

instead.

Thanks, Brian, but I am still picking up all of the
lowercase combinations of "AF".
Mark

-----Original Message-----
OK. Instead of use the criteria LIKE "*AF*" on your description field, you
might enter the following expression

InStr(1, [fieldname], "AF")

in the Field row of an empty column of the grid in

query
design view, and

then, in that same column, enter

<>0

in the Criteria row.



Thanks, John
I got the date problem to work by using; Between Date ()
And DateAdd("m",6,Date()). However, when I use LIKE "*AF*"
AND StrComp(Mid([fieldname], InStr

([fieldname], "AF"),
2),

"AF") = 0, Access is placing the zero in quotes and giving
me an 'Invalid Procedure Call' error. Any ideas? Thanks.
Mark
-----Original Message-----
On Thu, 20 Nov 2003 10:48:55 -0800, "Mark"

Thanks. I've tried both of these in the criteria field,
but they do not work.
< DateAdd("m",6,Date()) returns all records that expire
within 6 months and all records that have already
expired.

What in your table determines whether a record has
expired or not?

like "*AF*" returns all records where AF are together -
I
only want the uppercase AF.

Ah. You didn't SAY that. Access queries are *not* case
sensitive!
You'll need a more complex expression:

LIKE "*AF*" AND StrComp(Mid([fieldname], InStr
([fieldname], "AF"), 2),
"AF") = 0


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps? loc=us&access=public
.



.

.

 

[Brian Camire]

Did you set the criteria to

<>0

for this calculated field?

My mistake, the expression does not show "#Error", but it
still returns all records.

-----Original Message-----
OK. Try

InStr(1, [fieldname], "AF", 0)

instead.

Thanks, Brian, but I am still picking up all of the
lowercase combinations of "AF".
Mark
-----Original Message-----
OK. Instead of use the criteria LIKE "*AF*" on your
description field, you
might enter the following expression

InStr(1, [fieldname], "AF")

in the Field row of an empty column of the grid in query
design view, and
then, in that same column, enter

<>0

in the Criteria row.



message
Thanks, John
I got the date problem to work by using; Between Date ()
And DateAdd("m",6,Date()). However, when I use
LIKE "*AF*"
AND StrComp(Mid([fieldname], InStr ([fieldname], "AF"),
2),
"AF") = 0, Access is placing the zero in quotes and
giving
me an 'Invalid Procedure Call' error. Any ideas? Thanks.

Mark
-----Original Message-----
On Thu, 20 Nov 2003 10:48:55 -0800, "Mark"

Thanks. I've tried both of these in the criteria
field,
but they do not work.
< DateAdd("m",6,Date()) returns all records that
expire
within 6 months and all records that have already
expired.

What in your table determines whether a record has
expired or not?

like "*AF*" returns all records where AF are
together -
I
only want the uppercase AF.

Ah. You didn't SAY that. Access queries are *not* case
sensitive!
You'll need a more complex expression:

LIKE "*AF*" AND StrComp(Mid([fieldname], InStr
([fieldname], "AF"), 2),
"AF") = 0


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps? loc=us&access=public
.



.

.