Solved Query which pulls Values from a forms Textboxes and Displays data between the two Values in those Textboxes

[Bean Machine]

Hi All!

I am struggling to find a way to do this. Essentially I want a query to figure out what is in two textboxes on a form and display only the data that occurs between those two values. In this case between two years as seen in the attached image below.

Likely something using the Between function and the Like function, but I am at a loss. Any help would be greatly appreciated.

 

[cheekybuddha]

SELECT
  *
FROM YourTable
WHERE DateField >= DateSerial([cboYearStart], 1, 1)
  AND DateField < DateSerial([cboYearEnd] + 1, 1, 1);

 

[June7]

BETWEEN AND is inclusive for both ends. So build end date for 12/31/xxxx, not 1/1/xxxx+1

 

[Bean Machine]

BETWEEN AND is inclusive for both ends. So build end date for 12/31/xxxx, not 1/1/xxxx+1

Good info thank you for this.

 

[Bean Machine]

SELECT
  *
FROM YourTable
WHERE DateField >= DateSerial([cboYearStart], 1, 1)
  AND DateField < DateSerial([cboYearEnd] + 1, 1, 1);

Thank you for this! It worked! Have a great day!

 

[June7]

or use: ... <= DateSerial([cboYearEnd], 12, 31)

or:

DateField BETWEEN DateSerial([cboYearStart, 1, 1) AND DateSerial([cboYearEnd), 12, 31)

or simply:

Year(DateField) BETWEEN [cboYearStart] AND [cboYearEnd]

An advantage is don't have to be concerned with date structure if not using U.S. date format.

 

[cheekybuddha]

or use: ... <= DateSerial([cboYearEnd], 12, 31)

or:

DateField BETWEEN DateSerial([cboYearStart, 1, 1) AND DateSerial([cboYearEnd), 12, 31)

These miss out on anything after 12:00:01am 31st December until midnight on New Year's day.

 

[June7]

Only if DateField is saving time components, otherwise works for me.

Granted, I seldom have need to save time components so I did overlook this.

 

[ebs17]

or simply:
Year(DateField) BETWEEN [cboYearStart] AND [cboYearEnd]

Simple but slow.

A leaflet should be mandatory here: This suggestion does not allow the use of a possibly existing index and therefore gives performance away.