If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Automation
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 "Barry Gilbert" wrote: 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: |
#12
|
|||
|
|||
Automation
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)) fester wrote: 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 "Barry Gilbert" wrote: 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: |
#13
|
|||
|
|||
Automation
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 "fester" wrote: 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 "Barry Gilbert" wrote: 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: |
#14
|
|||
|
|||
Automation
Thanks for the correction Barry.
You where correct to remove the [] from my previous post. 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 |
|
Thread Tools | |
Display Modes | |
|
|