Date query

  • Thread starter Thread starter Mark
  • Start date Start date
M

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
 
You might use a criteria on your description field like:

Like "*AF*"

and on your end date field like

<=DateAdd("m",6,Date())
 
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.
 
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
Click to expand...

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.
 
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
 
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.
Click to expand...

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.
Click to expand...

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
 
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
Click to expand...
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.
Click to expand...

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



.
Click to expand...
 
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.



Mark said:
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
Click to expand...
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.
Click to expand...

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



.
Click to expand...
Click to expand...
 
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
Click to expand...
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



.
Click to expand...
Click to expand...


.
Click to expand...
 
OK. Try

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

instead.


Mark said:
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



.
Click to expand...


.
Click to expand...
Click to expand...
 
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
Click to expand...
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
Click to expand...
([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
.



.
Click to expand...
Click to expand...


.
Click to expand...
 
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
Click to expand...
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
Click to expand...
([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
.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Did you set the criteria to

<>0

for this calculated field?

Mark said:
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
.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top