query to show entries between 2 points (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 05:56
Joined
Dec 8, 2007
Messages
163
Hi all,

I'm struggling with a query syntax.
I'm trying to use a where clause to make only records in the last two years show up:

This is invoked via VBA.

My SQL is as follows:

Code:
SELECT tblShowAwardEntrant.lngzShowID, tblShowAwardEntrant.lngzAwardID, tblShowAwardEntrant.lngzEntrantID, tblShow.dtmShowDate, FROM tblShow INNER JOIN tblShowAwardEntrant ON tblShow.idsShowID = tblShowAwardEntrant.lngzShowID WHERE (((tblShowAwardEntrant.lngzAwardID)=24)) AND dtmShowDate Between " & Me.lngzShowID.Column(1) & " AND Datepart('YYYY','" & Me.lngzShowID.Column(1) & "',-2) ORDER BY tblShow.dtmShowDate DESC;
But no results show and I'm not sure if my code is correctly written. But I only want to utilise the year part of the date.

When I parse it in to a message box, the code looks as per the image attached.




Now, there are 2 entries with earlier dates - 1 per year (which is what happens in reality).

Please can someone assist?

Is it because I've put the earlier date after the later date?

Thanks.
Simon
 

bob fitz

AWF VIP
Local time
Today, 05:56
Joined
May 23, 2011
Messages
4,165
Try:

Code:
SELECT tblShowAwardEntrant.lngzShowID, tblShowAwardEntrant.lngzAwardID, tblShowAwardEntrant.lngzEntrantID, tblShow.dtmShowDate, FROM tblShow INNER JOIN tblShowAwardEntrant ON tblShow.idsShowID = tblShowAwardEntrant.lngzShowID WHERE (((tblShowAwardEntrant.lngzAwardID)=24)) AND dtmShowDate Between " & Me.lngzShowID.Column(1) & " AND " & Datepart('YYYY','" & Me.lngzShowID.Column(1) & "',-2) & " ORDER BY tblShow.dtmShowDate DESC;
 

Mr_Si

Registered User.
Local time
Today, 05:56
Joined
Dec 8, 2007
Messages
163
Hi there,
Sadly it was giving me a syntax error on your modified code.

Thanks,
Simon
 

bob fitz

AWF VIP
Local time
Today, 05:56
Joined
May 23, 2011
Messages
4,165
How about:
Code:
SELECT tblShowAwardEntrant.lngzShowID, tblShowAwardEntrant.lngzAwardID, tblShowAwardEntrant.lngzEntrantID, tblShow.dtmShowDate, FROM tblShow INNER JOIN tblShowAwardEntrant ON tblShow.idsShowID = tblShowAwardEntrant.lngzShowID WHERE (((tblShowAwardEntrant.lngzAwardID)=24)) AND dtmShowDate Between " & Me.lngzShowID.Column(1) & " AND " & Datepart('YYYY',' & Me.lngzShowID.Column(1) & ',-2) & " ORDER BY tblShow.dtmShowDate DESC;
 

Mr_Si

Registered User.
Local time
Today, 05:56
Joined
Dec 8, 2007
Messages
163
Got it working! Thanks for the assistance, but did it differently in the end, used an extra field as a datepart expression in a query that controlled the form and did the arithmetic based on that instead.

Thanks for your ideas though.
 

Users who are viewing this thread

Top Bottom