Automation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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 said:
Instead of Date +1 try Dateadd("d", 1, [Date])
Let me know how that goes.

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
Click to expand...
Click to expand...
 
Try this correction to Anthos' suggestion:
Dateadd("d", 1, Date)

Notice I've removed the square brackets surrounding Date.

Barry

fester said:
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 said:
Instead of Date +1 try Dateadd("d", 1, [Date])
Let me know how that goes.

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

:

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

:

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

:

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

:

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

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

Fester

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

Notice I've removed the square brackets surrounding Date.

Barry

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

:

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

:

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

:

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

:

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

All fields are set up as date/time. The dates entered are all set out as
"dd/mm/yyyy".

Let me know if there is any way to maybe use a macro set up or something or
if you can help me further.

Thanks
Fester
 
Okay,
For some reason the database doesn't think they are date type fields

So we might try and force in into thinking that it's a date..
Try

Dateadd("d", 1, Cdate(Date))
 
I'm stumped. There's something else going on here that I can't understand
without seeing it. If you want, you can zip your mdb and email it to me and
I'll see if I can figure it out.

barry dot gilbert at hunterdouglas dot com

Barry
 
Thanks for the correction Barry.
You where correct to remove the [] from my previous post.

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

Notice I've removed the square brackets surrounding Date.

Barry

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

:

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

:

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

:

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

:

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