Use Partial field value as criteria

[cplmckenzie]

Hello,



I have a quick question.

In a table I have a record field named TransDate , type is short text, format is mm/dd/yr.

Can I possible use the mm part of the field and use it as the criteria in the Query Builder "Criteria" section.

cplmckenzie

[orange]

Access has a Date/Time datatype specifically for Dates.

[June7]

Not against the full date value. Dates are actually stored as a number, even though you see default formatting of 12/31/2014, the actual value is 42004.

Create a field in query with expression that extracts the month and apply filter criteria to that field. Same if you need to also filter by year.

[Farida]

Easy option to extract month and years from dates are
month(TransDate)
year(TransDate)

And this can be used in criterias in your queries. Hope this helps.

[cplmckenzie]

Hello,

I attempted using the option that you suggested Farida in the following SELECT WHERE.....

SELECT [2014ExpenseList].[TransDate], [2014ExpenseList].[TransType], [2014ExpenseList].[TransCategory], [2014ExpenseList].[TransPayee], [2014ExpenseList].[TransCostCtr], [2014ExpenseList].[TransAmount] FROM 2014ExpenseList
WHERE [2014ExpenseList].[month(TransDate)] = txtMonth.value;

But access displayed an input box requesting the month(TransDate) parameter.

June7, could you give me a bit more detail on your suggestion that I may try it also.

cplmckenzie

[June7]

My suggestion is the same as Farida's. Just need to adjust your WHERE clause.


WHERE Month([TransDate]) = [enter month number];

or

WHERE Month([TransDate]) = Forms!formname.txtMonth;

[cplmckenzie]

Farida - June7,

Thanks to both of you. Problem solved!!!!

cplmckenzie