×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

CASTing or date formatting in ODBC->Access 97

CASTing or date formatting in ODBC->Access 97

CASTing or date formatting in ODBC->Access 97

(OP)
Hello,

I am back again! Long time no see!

Once again I am using the Sun JDBC-ODBC module, connecting to an Access 97 database within iReport (Jasper Reports).

I am trying to do some date calculations but seem to be ending up with text fields and variants on CONVERT and CAST seem to not be implemented. Ideally, I'd like a date returned from:

IIF (tbl_PET_Pet.PET_CV2 >= now()+180, tbl_PET_Pet.PET_CV2 - 180, '' ) as vacdate2


In other words, if the vaccination date for a pet is >= 180 days in the future, return vacdate2 as that date - 180 days (as an approximation for a 6 monthly reminder).

I can;t seem to make this return a date value.

Thanks

 

RE: CASTing or date formatting in ODBC->Access 97




Hi,

What value IS it returning, if not a date?


 

Skip,

glassesJust traded in my old subtlety...
for a brand NUANCE!tongue

RE: CASTing or date formatting in ODBC->Access 97

(OP)
Hi Skip - I'm getting floating point numbers eg 184.xxxxx

 

RE: CASTing or date formatting in ODBC->Access 97




Well Date/Time values are numbers.

You might want to...

CODE

Select *
From tbl_PET_Pet
Where PET_CV2 Between
DateSerial(Year(Date()+180),Month(Date()+180),1) and
DateSerial(Year(Date()+180),Month(Date()+180)+1,0)
as your subset of reminders for the month.

 

Skip,

glassesJust traded in my old subtlety...
for a brand NUANCE!tongue

RE: CASTing or date formatting in ODBC->Access 97

(OP)
Thanks - what I was hoping to do was find clients with vaccinations due in 6 months time (eg: in June find clients with December dates) and then work out their actual reminder date as 6 months back from December eg: if next date is 12th Dec, return 12th June as their 6 monthly reminder date. I suppose I could cheat by just 'knowing' that Dec translates back to June etc without doing a specific calculation.

RE: CASTing or date formatting in ODBC->Access 97





Is there a question in there?

Skip,

glassesJust traded in my old subtlety...
for a brand NUANCE!tongue

RE: CASTing or date formatting in ODBC->Access 97

(OP)
Sure - the SELECT is working fine, but I was hoping to be able to generate a date (as per my original posting) so that the customer's message would end up as:

Your reminder date is 'vacdate2'

but since PET_CV2 - 180 ends up as a floating number, the customer would get:

Your reminder date is 184.7765

I will have a play around with the date functions but some seem not to be available within the query engine.

Thanks for the feedback so far.

RE: CASTing or date formatting in ODBC->Access 97





CODE

Select Format(PET_CV2 - 180,"yyyy/mm/dd") As VacDate2
 

Skip,

glassesJust traded in my old subtlety...
for a brand NUANCE!tongue

RE: CASTing or date formatting in ODBC->Access 97

(OP)
Thanks Skip - I'll try that.

I have also found that bringing in your suggestion of dateserial() has made the ireport realise the results need to be in date format too so I am sorted.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close