Converting string to date

[dhoover via AccessMonster.com]

I'm using the following code to piece together a due date. The problem I'm
having is that Access does not recognize the final product, DueDate as a date.
The field shows correctly, however when I try to pull clients who's duedate
is greater than another date, I get the message that data type is mismatched
in the criteria expression. I've tried using CVDate and DateValue with no
luck.


Month: DatePart("m",[reportingdate])
Year: DatePart("yyyy",[ReportingDate])
Date1: IIf(Not IsNull([ReptsDueBy]),[month] & "/" & [reptsdueby] & "/" &
[year],"")
DueDate: DateAdd("m",1,[date1])

 

[Jeff Boyce]

Try "building" the date using a function that returns a date. For example,
see DateSerial() in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dhoover via AccessMonster.com]

I was able to do that and Access recognizes it as a date, but when I try to
add 1 month to it using DateAdd("m",1,[date1]), I get that error message
again.

Try "building" the date using a function that returns a date. For example,
see DateSerial() in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using the following code to piece together a due date. The problem
I'm

[quoted text clipped - 12 lines]

[year],"")
DueDate: DateAdd("m",1,[date1])

 

[Jeff Boyce]

Instead of using two steps, use DateSerial to do it in a single step. You
could use something like:

DateSerial([However You Do Year], [However You Do Month] + 1, [However
You Do Day])

Regards

Jeff Boyce
Microsoft Office/Access MVP

I was able to do that and Access recognizes it as a date, but when I try to
add 1 month to it using DateAdd("m",1,[date1]), I get that error message
again.

Try "building" the date using a function that returns a date. For
example,
see DateSerial() in Access HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using the following code to piece together a due date. The problem
I'm

[quoted text clipped - 12 lines]

[year],"")
DueDate: DateAdd("m",1,[date1])

 

[dhoover via AccessMonster.com]

perfect, thank you!

Instead of using two steps, use DateSerial to do it in a single step. You
could use something like:

DateSerial([However You Do Year], [However You Do Month] + 1, [However
You Do Day])

Regards

Jeff Boyce
Microsoft Office/Access MVP

I was able to do that and Access recognizes it as a date, but when I try to
add 1 month to it using DateAdd("m",1,[date1]), I get that error message

[quoted text clipped - 14 lines]

[year],"")
DueDate: DateAdd("m",1,[date1])