'Between with And' Error (1 Viewer)

locchio

New member
Local time
Today, 01:01
Joined
Jul 23, 2021
Messages
9
I'm getting a 'between without and' error when I run this query. I am trying to return MeetingDate from tblMeetingDates where the MeetingDate is between 3 days and 30 days older that the LetterDate field in tblCustomerMailing. I tried brackets and parenthesis I still get the error. Thanks for any insight!


SELECT tblCustomerMailing.Account, qryAquaInfo.Premise, qryAquaInfo.[Aqua Name], qryAquaInfo.[Aqua Address], [qryAddressesOldVsNewCTA CUSTOMERS].New_Address AS Service_Address, [qryAddressesOldVsNewCTA CUSTOMERS].SortCity AS Service_City, tblCustomerMailing.Name, [tblCustomerListing(Billing)].CO, [tblCustomerListing(Billing)].Address, [tblCustomerListing(Billing)].City, [tblCustomerListing(Billing)].State, [tblCustomerListing(Billing)].Zip, qryPaymentCalculations.[Rate Total], qryPaymentCalculations.Current, qryPaymentCalculations.[Over 30], qryPaymentCalculations.[Over 60], qryPaymentCalculations.[Over 90], qryPaymentCalculations.Total, qryPaymentCalculations.TotalAsOf, qryPaymentCalculations.[Past Due], qryPaymentCalculations.[10% Past], qryPaymentCalculations.[Late Fee], qryPaymentCalculations.[On Balance], qryPaymentCalculations.[Total Pay], qryPaymentCalculations.[Pay Plan], qryPaymentCalculations.Payments, qryPaymentCalculations.[Short Payment], qryPaymentCalculations.Amount_Due, qryPaymentCalculations.[Last Date], qryPaymentCalculations.[Last Amount], tblCustomerMailing.LetterDate, tblCustomerMailing.Letter, tblLetterTypes.[Reply Days], [letterDate]+[reply days] AS DateCalc, "19:00:00" AS MeetTime, "Wilburton Catering Hall" AS MeetLocation, "" AS Notes, [qryAddressesOldVsNewCTA CUSTOMERS].lastoforiginal_full AS OriginalAdd, qryAquaInfo.[Parcel#], "" AS Atty, "" AS Cert, qryAquaInfo.[Status Date], "" AS [System Note], qryPaymentCalculations.[Total Pay 2], qryPaymentCalculations.[Total Pay 3], [letterdate]+3 AS LetterDate3, [letterdate]+30 AS LetterDate30, qryPaymentCalculations.Test, tblMeetingDates.MeetingDate
FROM ((((((tblCustomerMailing LEFT JOIN [qryAddressesOldVsNewCTA CUSTOMERS] ON tblCustomerMailing.Account = [qryAddressesOldVsNewCTA CUSTOMERS].[CTA Acct]) LEFT JOIN [tblCustomerListing(Service)] ON tblCustomerMailing.Account = [tblCustomerListing(Service)].Acct) LEFT JOIN qryAquaInfo ON tblCustomerMailing.Account = qryAquaInfo.Acct) INNER JOIN [tblCustomerListing(Billing)] ON tblCustomerMailing.Account = [tblCustomerListing(Billing)].Acct) LEFT JOIN tblLetterTypes ON tblCustomerMailing.Letter = tblLetterTypes.Letter) LEFT JOIN qryPaymentCalculations ON tblCustomerMailing.Account = qryPaymentCalculations.Acct) INNER JOIN tblMeetingDates ON tblMeetingDates.MeetingDate Between [tblCustomerMailing].[LetterDate]+3 and [tblCustomerMailing].[LetterDate]+30 ORDER BY tblCustomerMailing.Account, [tblCustomerListing(Billing)].City;
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:01
Joined
Oct 29, 2018
Messages
17,177
Are you trying to use Between/And as a JOIN? Have you tried using >= AND <= instead?
 

locchio

New member
Local time
Today, 01:01
Joined
Jul 23, 2021
Messages
9
Brain fart. Thanks!
 

locchio

New member
Local time
Today, 01:01
Joined
Jul 23, 2021
Messages
9
Are you trying to use Between/And as a JOIN? Have you tried using >= AND <= instead?
What am I missing? OMG.. too many hours working on this DB today

ON tblMeetingDates.MeetingDate >= tblCustomerMailing.LetterDate+3 and tblMeetingDates.MeetingDate=< tblCustomerMailing.LetterDate+30
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:01
Joined
Oct 29, 2018
Messages
17,177
What am I missing? OMG.. too many hours working on this DB today

ON tblMeetingDates.MeetingDate >= tblCustomerMailing.LetterDate+3 and tblMeetingDates.MeetingDate=< tblCustomerMailing.LetterDate+30
Not sure, perhaps you want a criteria instead of JOIN.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:01
Joined
Feb 19, 2013
Messages
13,582
Don’t think you can use between as a none standard join but you can use <= and >=
 

Users who are viewing this thread

Top Bottom