Querying the two digit month in a table with full date (1 Viewer)

Wapug · Nov 6, 2017

So, I have a table with a date column populated as mm/dd/yyyy this table will store rows from dates throughout the year only (meaning at the end of 2017 a new table with 2018 dates will be created). I have a query that I am creating in access from that table (the table is called tTeamScores) and I would like that query to look at the date field (the field name is DATEWRKD) and pull only those dates for whatever two digit month I enter into the form (its called FORM1 and the date text box is called DATEMO) prior to pushing the button to run the query. How can I do this? I cant seem to get the wording for the Expression. I want to be able to type 10 in my form and have the query only pull up rows from the table where the record has a DATEWRKD from the month of October.

Pat Hartman · Nov 6, 2017

(meaning at the end of 2017 a new table with 2018 dates will be created)

-- Seriously bad idea. You are treating Access like a spreadsheet.

Dates are stored internally as double precisoun numbers. They are NOT strings. Dates re formatted as strings when being displayed for human consumption.

Whenever you work with dates, you should use the appropriate date function to get the date part you need. In this case, you would use the Month() function.

Where Month(DATEWRKD) = Forms!yourform!txtSelectedMonth

Of course, if you follow my advice and don't recreate the BE every year, then you need to consider year as well.

Where Month(DATEWRKD) = Forms!yourform!txtSelectedMonth AND Year(DATEWRKD) = Forms!yourform!txtSelectedYear

NauticalGent · Nov 6, 2017

Pat Hartman said:
-- Seriously bad idea. You are treating Access like a spreadsheet.

The hardest hurdle to clear...

Wapug · Nov 6, 2017

Pat Hartman said:
-- Seriously bad idea. You are treating Access like a spreadsheet.

Dates are stored internally as double precisoun numbers. They are NOT strings. Dates re formatted as strings when being displayed for human consumption.

Whenever you work with dates, you should use the appropriate date function to get the date part you need. In this case, you would use the Month() function.

Where Month(DATEWRKD) = Forms!yourform!txtSelectedMonth

Of course, if you follow my advice and don't recreate the BE every year, then you need to consider year as well.

Where Month(DATEWRKD) = Forms!yourform!txtSelectedMonth AND Year(DATEWRKD) = Forms!yourform!txtSelectedYear

So if I do this, then on my form how would I have the date formatted?

Wapug · Nov 6, 2017

NauticalGent said:
The hardest hurdle to clear...

Obviously I’m missing your point here. What is the hurdle I may need to clear?

plog · Nov 6, 2017

The biggest hurdle you need to clear is understanding that Access isn't Excel With Forms. We get a ton of people with that misconception on this site and you are heading down that road.

Tables are not equivalent to Excel tabs, but you are starting to treat them as such. You wouldn't create new tables for every single year of data you have, you would throw all years data into a table and then use logic (via a query) to extract just the data from the year you wanted.

Also, you don't really format dates in a table. You simply use a date/time field to store the data, then in a query you can format it as you see fit. By doing that, you are able to use all the Date function Access (https://www.techonthenet.com/access/functions/index.php) has native to it. With those functions you can build queries to extract just certain Month, Year, Quarter, etc. date periods.

George Moore · Nov 6, 2017

Hi Wapug

Have you considered just using a parameter query instead of a form ?

This isn't for purists but if you Copy and Paste this SQL into your query it will ask you to enter the month and then filter accordingly

Code:

PARAMETERS [Enter Month] BYTE;
 
SELECT * 

FROM tTeamScore
 
WHERE
Month(DATEWRKD) = [Enter Month]

NauticalGent · Nov 6, 2017

Wapug said:
Obviously I’m missing your point here. What is the hurdle I may need to clear?

Plog’s answer is what I would have said. Only Plog did a much better job of it than I would have...

Pat Hartman · Nov 6, 2017

Since forms and reports are what your users will be interacting with, you would have a control on each form to select a specific year. This will be an unbound control and you can set it to the current year in the Open Event of the form so the user doesn't have to enter anything unless he wants to view a different year.

Me.txtYear = Year(Date())

The RecordSource query of the form would reference this filter.

Select ... From .. Where Year(DATEWRKD) = Forms!yourform!txtYear

To tie this together, add a requery in the AfterUpdate event of the Year field. This makes the form rerun its RecordSource query.

Me.Requery

Querying the two digit month in a table with full date (1 Viewer)

Wapug

Registered User.

Pat Hartman

Super Moderator

NauticalGent

Ignore List Poster Boy

Wapug

Registered User.

Wapug

Registered User.

plog

Banishment Pending

George Moore

Access 2002,2010 & 2016

NauticalGent

Ignore List Poster Boy

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread