query to show entries between 2 points (1 Viewer)

[Mr_Si]

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:

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]

Try:

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]

Hi there,
Sadly it was giving me a syntax error on your modified code.

Thanks,
Simon

 

[bob fitz]

How about:

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]

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.