filter date by year only

[hunterjj]

I have a database and one of the fields is a date field. I would like to be able to select records for my combobox using only a 4 position year. If that year appears in the date, then I would like the record to show in my combobox.

 

[ajdesalvo]

the combobox RowSource would be something like this:

"Select [YourDateField] from [YourTable] WHERE Format([YourDateField], "yyyy&quot = '2003' "

If the year is something you enter on the form then:

"Select [YourDateField] from [YourTable] WHERE Format([YourDateField], "yyyy&quot = '" & Me![FormYearField] & "' "

Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[hunterjj]

I tried it and all I got was a single record which was the very first record on the database.

This is what I am using:
SELECT [Tournaments].[TNameDateTime]
FROM Tournaments
WHERE Format([Tournaments].[TDate],"yyyy&quot="' & Selection![YearSel] & '";

Any idea why it won't work?

 

[ajdesalvo]

It is hard to tell on the screen, but do you have single quotes outside of the double-quotes? If they are they should be switched.

SELECT [Tournaments].[TNameDateTime]
FROM Tournaments
WHERE Format([Tournaments].[TDate],"yyyy&quot='" & Selection![YearSel] & "'; "


Is this a query or are you are you using VBA code?

Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[hunterjj]

The single quotes are enclosed inside of the double quotes. This is a query for the RowSource of my combobox.

If I use an actual date(ie. '2003') it works better but not perfect. The combobox shows the first record on the database(which is a 2002 date) and when I do a pulldown, it shows all 2003 dates.

No matter what I do, I can't get it to work when using a field that is on a form. I even tried to use a Public field and moved my form entry to it. That didn't work either.

 

[ajdesalvo]

Try this:

SELECT [Tournaments].[TNameDateTime]
FROM Tournaments
WHERE Year([Tournaments].[TDate])='" & Selection![YearSel] & "'; "

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[hunterjj]

Still no joy. I even tried putting in the Forms! in front of my formname to see if that would help.
Here is what I'm presently using:

SELECT [Tournaments].[TNameDateTime]
FROM Tournaments
WHERE Year([Tournaments].[TDate])="' & Forms!Selection![YearSel] & '";

After the = is " and ' -and- I end with ' and "

 

[ajdesalvo]

I missed the WHERE clause problem, you are selecting TNameDateTime from the table and using TDate in the WHERE clause. This should be TNameDateTime, too.

SELECT [Tournaments].[TNameDateTime]
FROM Tournaments
WHERE Year([Tournaments].[TNameDateTime])="' & Forms!Selection![YearSel] & '";

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[hunterjj]

TNameDateTime is a String field containing a Name concatenated with a Date concatenated with a Time. This is what I want to show in the combobox. Date is another field in my database that I'm trying to filter on. Is this not possible?

 

[hunterjj]

Also, why does it work when I use a hard coded date? the only time it doensn't work is when I use a field variable.

 

[ajdesalvo]

Let do some testing. Create a query by pasting the code from your rowsource into the SQL designer in query designer.

SELECT [Tournaments].[TNameDateTime]
FROM Tournaments
WHERE Year([Tournaments].[TDate])="' & Forms!Selection![YearSel] & '";

Open the form and enter the year in the field, make sure you tab past the field so the data is saved in the field.

Next run the query to see if you get results, you may be able to tweak the query in the query designer.

Then you can either use this query as the rowsource, or copy the sql code in the rowsource field.

Let me know! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[hunterjj]

This code is already in a query. My Rowsource points to the query name. I've shown the value of the field in a Msgbox in the "on current" vba code, so I know it contains the correct value. As I said before, this works if I use a hard-coded date.

 

[ajdesalvo]

Can you email me the form, the Tournaments table, and the rowsource query? (adesalvo@scotttechllc.com)

If you can, please zip the files up so the get through my firewall.

I would be more than happy to debug it here. Sometimes this is the only way I can get to the issue.

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[ajdesalvo]

In the criteria of the query, remove the ampersands and quotes. The criteria should be:

[Forms]![Selection]![YearSel]

Then you are good to go. Sometimes it takes getting the stuff in fornt of you!

Code on!



Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[hunterjj]

It now works just as if I entered a hard-coded date. It also responds as I indicated before. The combobox shows the 1st record in my database. When I do a pull-down, it shows me only the years that I am looking for. Is there a way to get the initial combobox setting to show the 1st record of the year that I selected instead of the very 1st record in the database?

 

[ajdesalvo]

You have an OnCurrent event:

Combo26 = TNameDateTime

So it sets it to that when the form first opens. Is that the first record?

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[hunterjj]

If you test with my database you will see that it shows the first record in the database and not the first record based on the selected year.

 

[ajdesalvo]

Jim,

Glad to hear that the recordsource was the issue.

Good luck in the future!

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"