×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Join error for date range

Join error for date range

Join error for date range

(OP)
I am trying to join two tables on ID1, ID2 and when Date > Start AND < End but I keep getting an error when I add the date fields in. Just joining on ID1 and ID2 works fine, but I'm of course getting duplicated records because in my right table I can have two lines for ID1, ID2 with a different start and end date. I'm trying to join so I can grab a field from my right table and add it into my results.

Typically in SQL Server I would just do a join on LeftTable.ID1 = RightTable.ID1 AND LeftTable.ID2 = RightTable.ID2 AND LeftTable.Date >= RightTable.Start AND Date < RightTable.End, but I can't get that to work in Access. It's giving me a message saying I have an error in my Join expression is not supported. I know the SQL that Access supports is a little different than SQL server so if someone could help me out that would be great.

This works:

CODE --> SQL

FROM All_Claims LEFT JOIN tmp_Duration2 ON (All_Claims.RPID = tmp_Duration2.RPID) AND (All_Claims.PATID = tmp_Duration2.PATID) 

This doesn't:

CODE --> SQL

FROM All_Claims LEFT JOIN tmp_Duration2 ON (All_Claims.RPID = tmp_Duration2.RPID) AND (All_Claims.PATID = tmp_Duration2.PATID) AND (All_Claims.[DOSDATE]>=tmp_Duration2.Rvsd_Cnt_Start_Dt) AND (All_Claims.[DOSDATE]<DateAdd("y",1,tmp_Duration2.Rvsd_Cnt_Start_Dt) 

RE: Join error for date range

Hmmmmm, DOSDATE: is that a real date or a string?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Join error for date range

Please add carriage returns in your posting. It makes your SQL much easier to read.

Do you realize that DateAdd("y",1,tmp_Duration2.Rvsd_Cnt_Start_Dt) adds a day and not a year? If you want to add a year, use "yyyy".

I'm not sure what range you expect or if the fields are actual dates and if they contain time values. You might want to try:

CODE --> SQL

FROM All_Claims LEFT JOIN tmp_Duration2 ON (All_Claims.RPID = tmp_Duration2.RPID) 
 AND (All_Claims.PATID = tmp_Duration2.PATID) 
 AND (tmp_Duration2.[DOSDATE] Between tmp_Duration2.Rvsd_Cnt_Start_Dt AND 
 DateAdd("yyyy",1,tmp_Duration2.Rvsd_Cnt_Start_Dt) -0.0001) 

Duane
Hook'D on Access
MS Access MVP

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!

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