Automation

I sent out a please help yesterday but i don't think i explained what i was
doing.
I am setting up a business database where i only have approx 200 clients but
it is constantly growing.
For this reason i need to send out birthday wishes, anniversary letters
after 3 months and 12 months as well as work out when someone comes off a
fixed price.
What i would like to do is have a report to tell me if one of these dates
matches todays date or tomorrows date so that a letter etc can be sent.
The fields i need to automate are currently called
Birthdate
Birthdate2
Settlement Date
Settlement Date2
Fixed Expiry Date

Could someone let me know in the simplest way exactly what i need to do to
automate a report so that it will also show the client number(automated
primary key already), surname and first name.

Thanks

It sounds like you need a bunch of OR's in your criteria. Each row of the
criteria section of the query designer is considered an OR so you want it to
look something like this:

Birthdate Birthdate2 Settlement Date
Date
Date +1
Date
Date +1
Date
Date +1

One potential issue is that Date + 1 will return the next calendar date,
which mightr not be the next working date. You'll have to account for
birthdates that fall on a Sunday when you run the report on a Friday, so you
might want to also look for dates that are two days in the future.

Barry

"fester" wrote:

I sent out a please help yesterday but i don't think i explained what i was
doing.
I am setting up a business database where i only have approx 200 clients but
it is constantly growing.
For this reason i need to send out birthday wishes, anniversary letters
after 3 months and 12 months as well as work out when someone comes off a
fixed price.
What i would like to do is have a report to tell me if one of these dates
matches todays date or tomorrows date so that a letter etc can be sent.
The fields i need to automate are currently called
Birthdate
Birthdate2
Settlement Date
Settlement Date2
Fixed Expiry Date

Could someone let me know in the simplest way exactly what i need to do to
automate a report so that it will also show the client number(automated
primary key already), surname and first name.

Thanks

Barry,

Thanks for the help.

When i set up the fields and i input the Date or Date+1 i then hit "RUN" and
a little box comes up saying "input mismatch in criteria field".

Could you tell me how i can make it so that it will understand the job i
want it to do.

Thanks
Fester

"Barry Gilbert" wrote:

It sounds like you need a bunch of OR's in your criteria. Each row of the
criteria section of the query designer is considered an OR so you want it to
look something like this:

Birthdate Birthdate2 Settlement Date
Date
Date +1
Date
Date +1
Date
Date +1

One potential issue is that Date + 1 will return the next calendar date,
which mightr not be the next working date. You'll have to account for
birthdates that fall on a Sunday when you run the report on a Friday, so you
might want to also look for dates that are two days in the future.

Barry

"fester" wrote:

I sent out a please help yesterday but i don't think i explained what i was
doing.
I am setting up a business database where i only have approx 200 clients but
it is constantly growing.
For this reason i need to send out birthday wishes, anniversary letters
after 3 months and 12 months as well as work out when someone comes off a
fixed price.
What i would like to do is have a report to tell me if one of these dates
matches todays date or tomorrows date so that a letter etc can be sent.
The fields i need to automate are currently called
Birthdate
Birthdate2
Settlement Date
Settlement Date2
Fixed Expiry Date

Could someone let me know in the simplest way exactly what i need to do to
automate a report so that it will also show the client number(automated
primary key already), surname and first name.

Thanks

Not sure if this is an issue, but it looks like my indenting didn't take. If
it wasn't clear, each set of Date and Date + 1 should be offset under each
succesive column.

Are these fields set up as Date/Time fields in your table?

Barry

"fester" wrote:

Barry,

Thanks for the help.

When i set up the fields and i input the Date or Date+1 i then hit "RUN" and
a little box comes up saying "input mismatch in criteria field".

Could you tell me how i can make it so that it will understand the job i
want it to do.

Thanks
Fester

"Barry Gilbert" wrote:

It sounds like you need a bunch of OR's in your criteria. Each row of the
criteria section of the query designer is considered an OR so you want it to
look something like this:

Birthdate Birthdate2 Settlement Date
Date
Date +1
Date
Date +1
Date
Date +1

One potential issue is that Date + 1 will return the next calendar date,
which mightr not be the next working date. You'll have to account for
birthdates that fall on a Sunday when you run the report on a Friday, so you
might want to also look for dates that are two days in the future.

Barry

"fester" wrote:

I sent out a please help yesterday but i don't think i explained what i was
doing.
I am setting up a business database where i only have approx 200 clients but
it is constantly growing.
For this reason i need to send out birthday wishes, anniversary letters
after 3 months and 12 months as well as work out when someone comes off a
fixed price.
What i would like to do is have a report to tell me if one of these dates
matches todays date or tomorrows date so that a letter etc can be sent.
The fields i need to automate are currently called
Birthdate
Birthdate2
Settlement Date
Settlement Date2
Fixed Expiry Date

Could someone let me know in the simplest way exactly what i need to do to
automate a report so that it will also show the client number(automated
primary key already), surname and first name.

Thanks

Barry,

Thanks so much for your time.

The exact message i recieve is "data type mismatch in criteria expression".

Do you have any suggestions?

Thanks

"Barry Gilbert" wrote:

Not sure if this is an issue, but it looks like my indenting didn't take. If
it wasn't clear, each set of Date and Date + 1 should be offset under each
succesive column.

Are these fields set up as Date/Time fields in your table?

Barry

"fester" wrote:

Barry,

Thanks for the help.

When i set up the fields and i input the Date or Date+1 i then hit "RUN" and
a little box comes up saying "input mismatch in criteria field".

Could you tell me how i can make it so that it will understand the job i
want it to do.

Thanks
Fester

"Barry Gilbert" wrote:

It sounds like you need a bunch of OR's in your criteria. Each row of the
criteria section of the query designer is considered an OR so you want it to
look something like this:

Birthdate Birthdate2 Settlement Date
Date
Date +1
Date
Date +1
Date
Date +1

One potential issue is that Date + 1 will return the next calendar date,
which mightr not be the next working date. You'll have to account for
birthdates that fall on a Sunday when you run the report on a Friday, so you
might want to also look for dates that are two days in the future.

Barry

"fester" wrote:

I sent out a please help yesterday but i don't think i explained what i was
doing.
I am setting up a business database where i only have approx 200 clients but
it is constantly growing.
For this reason i need to send out birthday wishes, anniversary letters
after 3 months and 12 months as well as work out when someone comes off a
fixed price.
What i would like to do is have a report to tell me if one of these dates
matches todays date or tomorrows date so that a letter etc can be sent.
The fields i need to automate are currently called
Birthdate
Birthdate2
Settlement Date
Settlement Date2
Fixed Expiry Date

Could someone let me know in the simplest way exactly what i need to do to
automate a report so that it will also show the client number(automated
primary key already), surname and first name.

Thanks

Instead of Date +1 try Dateadd("d", 1, [Date])
Let me know how that goes.


fester wrote:
Barry,

Thanks so much for your time.

The exact message i recieve is "data type mismatch in criteria expression".

Do you have any suggestions?

Thanks

"Barry Gilbert" wrote:

Not sure if this is an issue, but it looks like my indenting didn't take. If
it wasn't clear, each set of Date and Date + 1 should be offset under each
succesive column.

Are these fields set up as Date/Time fields in your table?

Barry

"fester" wrote:

Barry,

Thanks for the help.

When i set up the fields and i input the Date or Date+1 i then hit "RUN" and
a little box comes up saying "input mismatch in criteria field".

Could you tell me how i can make it so that it will understand the job i
want it to do.

Thanks
Fester

"Barry Gilbert" wrote:

It sounds like you need a bunch of OR's in your criteria. Each row of the
criteria section of the query designer is considered an OR so you want it to
look something like this:

Birthdate Birthdate2 Settlement Date
Date
Date +1
Date
Date +1
Date
Date +1

One potential issue is that Date + 1 will return the next calendar date,
which mightr not be the next working date. You'll have to account for
birthdates that fall on a Sunday when you run the report on a Friday, so you
might want to also look for dates that are two days in the future.

Barry

"fester" wrote:

I sent out a please help yesterday but i don't think i explained what i was
doing.
I am setting up a business database where i only have approx 200 clients but
it is constantly growing.
For this reason i need to send out birthday wishes, anniversary letters
after 3 months and 12 months as well as work out when someone comes off a
fixed price.
What i would like to do is have a report to tell me if one of these dates
matches todays date or tomorrows date so that a letter etc can be sent.
The fields i need to automate are currently called
Birthdate
Birthdate2
Settlement Date
Settlement Date2
Fixed Expiry Date

Could someone let me know in the simplest way exactly what i need to do to
automate a report so that it will also show the client number(automated
primary key already), surname and first name.

Thanks

Anthos

Thanks for the help but when i hit the run button it now brings up a box
with a title "Enter Parameter Value". it then says Date and is asking to
enter a date.

Let me know if you can tell me what to do here.

Thanks

Fester

"Anthos" wrote:

Instead of Date +1 try Dateadd("d", 1, [Date])
Let me know how that goes.


fester wrote:
Barry,

Thanks so much for your time.

The exact message i recieve is "data type mismatch in criteria expression".

Do you have any suggestions?

Thanks

"Barry Gilbert" wrote:

Not sure if this is an issue, but it looks like my indenting didn't take. If
it wasn't clear, each set of Date and Date + 1 should be offset under each
succesive column.

Are these fields set up as Date/Time fields in your table?

Barry

"fester" wrote:

Barry,

Thanks for the help.

When i set up the fields and i input the Date or Date+1 i then hit "RUN" and
a little box comes up saying "input mismatch in criteria field".

Could you tell me how i can make it so that it will understand the job i
want it to do.

Thanks
Fester

"Barry Gilbert" wrote:

It sounds like you need a bunch of OR's in your criteria. Each row of the
criteria section of the query designer is considered an OR so you want it to
look something like this:

Birthdate Birthdate2 Settlement Date
Date
Date +1
Date
Date +1
Date
Date +1

One potential issue is that Date + 1 will return the next calendar date,
which mightr not be the next working date. You'll have to account for
birthdates that fall on a Sunday when you run the report on a Friday, so you
might want to also look for dates that are two days in the future.

Barry

"fester" wrote:

I sent out a please help yesterday but i don't think i explained what i was
doing.
I am setting up a business database where i only have approx 200 clients but
it is constantly growing.
For this reason i need to send out birthday wishes, anniversary letters
after 3 months and 12 months as well as work out when someone comes off a
fixed price.
What i would like to do is have a report to tell me if one of these dates
matches todays date or tomorrows date so that a letter etc can be sent.
The fields i need to automate are currently called
Birthdate
Birthdate2
Settlement Date
Settlement Date2
Fixed Expiry Date

Could someone let me know in the simplest way exactly what i need to do to
automate a report so that it will also show the client number(automated
primary key already), surname and first name.

Thanks

Try this correction to Anthos' suggestion:
Dateadd("d", 1, Date)

Notice I've removed the square brackets surrounding Date.

Barry

"fester" wrote:

Anthos

Thanks for the help but when i hit the run button it now brings up a box
with a title "Enter Parameter Value". it then says Date and is asking to
enter a date.

Let me know if you can tell me what to do here.

Thanks

Fester

"Anthos" wrote:

Instead of Date +1 try Dateadd("d", 1, [Date])
Let me know how that goes.


fester wrote:
Barry,

Thanks so much for your time.

The exact message i recieve is "data type mismatch in criteria expression".

Do you have any suggestions?

Thanks

"Barry Gilbert" wrote:

Not sure if this is an issue, but it looks like my indenting didn't take. If
it wasn't clear, each set of Date and Date + 1 should be offset under each
succesive column.

Are these fields set up as Date/Time fields in your table?

Barry

"fester" wrote:

Barry,

Thanks for the help.

When i set up the fields and i input the Date or Date+1 i then hit "RUN" and
a little box comes up saying "input mismatch in criteria field".

Could you tell me how i can make it so that it will understand the job i
want it to do.

Thanks
Fester

"Barry Gilbert" wrote:

It sounds like you need a bunch of OR's in your criteria. Each row of the
criteria section of the query designer is considered an OR so you want it to
look something like this:

Birthdate Birthdate2 Settlement Date
Date
Date +1
Date
Date +1
Date
Date +1

One potential issue is that Date + 1 will return the next calendar date,
which mightr not be the next working date. You'll have to account for
birthdates that fall on a Sunday when you run the report on a Friday, so you
might want to also look for dates that are two days in the future.

Barry

"fester" wrote:

I sent out a please help yesterday but i don't think i explained what i was
doing.
I am setting up a business database where i only have approx 200 clients but
it is constantly growing.
For this reason i need to send out birthday wishes, anniversary letters
after 3 months and 12 months as well as work out when someone comes off a
fixed price.
What i would like to do is have a report to tell me if one of these dates
matches todays date or tomorrows date so that a letter etc can be sent.
The fields i need to automate are currently called
Birthdate
Birthdate2
Settlement Date
Settlement Date2
Fixed Expiry Date

Could someone let me know in the simplest way exactly what i need to do to
automate a report so that it will also show the client number(automated
primary key already), surname and first name.

Thanks

Barry,

Comes back with the "Data type mismatch in criteria expression"

Fester

"Barry Gilbert" wrote:

Try this correction to Anthos' suggestion:
Dateadd("d", 1, Date)

Notice I've removed the square brackets surrounding Date.

Barry

"fester" wrote:

Anthos

Thanks for the help but when i hit the run button it now brings up a box
with a title "Enter Parameter Value". it then says Date and is asking to
enter a date.

Let me know if you can tell me what to do here.

Thanks

Fester

"Anthos" wrote:

Instead of Date +1 try Dateadd("d", 1, [Date])
Let me know how that goes.


fester wrote:
Barry,

Thanks so much for your time.

The exact message i recieve is "data type mismatch in criteria expression".

Do you have any suggestions?

Thanks

"Barry Gilbert" wrote:

Not sure if this is an issue, but it looks like my indenting didn't take. If
it wasn't clear, each set of Date and Date + 1 should be offset under each
succesive column.

Are these fields set up as Date/Time fields in your table?

Barry

"fester" wrote:

Barry,

Thanks for the help.

When i set up the fields and i input the Date or Date+1 i then hit "RUN" and
a little box comes up saying "input mismatch in criteria field".

Could you tell me how i can make it so that it will understand the job i
want it to do.

Thanks
Fester

"Barry Gilbert" wrote:

It sounds like you need a bunch of OR's in your criteria. Each row of the
criteria section of the query designer is considered an OR so you want it to
look something like this:

Birthdate Birthdate2 Settlement Date
Date
Date +1
Date
Date +1
Date
Date +1

One potential issue is that Date + 1 will return the next calendar date,
which mightr not be the next working date. You'll have to account for
birthdates that fall on a Sunday when you run the report on a Friday, so you
might want to also look for dates that are two days in the future.

Barry

"fester" wrote:

I sent out a please help yesterday but i don't think i explained what i was
doing.
I am setting up a business database where i only have approx 200 clients but
it is constantly growing.
For this reason i need to send out birthday wishes, anniversary letters
after 3 months and 12 months as well as work out when someone comes off a
fixed price.
What i would like to do is have a report to tell me if one of these dates
matches todays date or tomorrows date so that a letter etc can be sent.
The fields i need to automate are currently called
Birthdate
Birthdate2
Settlement Date
Settlement Date2
Fixed Expiry Date

Could someone let me know in the simplest way exactly what i need to do to
automate a report so that it will also show the client number(automated
primary key already), surname and first name.

Thanks

This usually happens when you pass in a value that doesn't match the data
type of the column you're reading from. Did you check to see that the
Birthdate and SettlementDate fields are set to DateTime data type?

Barry

"fester" wrote:

Barry,

Comes back with the "Data type mismatch in criteria expression"

Fester

"Barry Gilbert" wrote:

Try this correction to Anthos' suggestion:
Dateadd("d", 1, Date)

Notice I've removed the square brackets surrounding Date.

Barry

"fester" wrote: