Filter Calculated field in a parameter query

[JonathanT]

I have a form and I want to limit the number of fields a users needs to complete to add a record. I created 3 calculated date fields in Table1 that fill in the month, year and quarter that correspond to the date in the [CurrDate] field. I would like to be be able to filter by any one of the the 3 calculated fields in a parameter query (TableQry1) but I get no results when I try the normal way to filter a query. Not even sure if this can be done but I thought I would ask. DB attached

[June7]

Format property does not change actual value. Value of these calculated fields is either Null or full date from original CurrDate field, regardless of what Format property displays. I never set Format property in table.

If you want to filter on date portion, then need to extract from original date.

Year(CurrDate)

Month(CurrDate)

Format(CurrDate, "mmmm")

DatePart("q", CurrDate())
Assumes quarter numbering starts with January. If you want quarter numbering to start with another period, say fiscal year beginning July, that will call for a different approach.

Unfortunately, Format and DatePart functions are not available to table Calculated field. Do calcs in query or textboxes instead of table.

If field is Null, result will be Null.

[Micron]

there is no attachment. See "how to attach files" at the top of the page.
Usually, calculated fields in tables should be avoided. Calculations are best done on the fly. It should be possible to get any month, day or year value out of any date field using those intrinsic functions. Getting the quarter is a common question but I have to be honest; not sure how as I've never needed it.

[June7]

Micron, I see an attachment and downloaded file.

[Micron]

Not me. Firefox version 119.0
It's there in M$ Edge though.

[Micron]

Now I know why. It would appear that if someone is on your IL you can elect to view their posts, but attachments won't show. It showed in Edge because I wasn't logged in to the forum.

[NoellaG]

calculated fields in tables should be avoided. Calculations are best done on the fly.

I don't know that, depends if the fields are persistent or not. On SQL server we use persistent calculated fields because they are saved to disk and can be indexed, so it's much faster than do the calculation over and over again when running the query. So if Access works with persistent calculated fields, it's certainly better to do them in the table. Another advantage is that you have the calculated fields ready for every query you need to make, simplifying the query. This could solve the error 'query to complex to perform' you sometimes get.

[JonathanT]

Thanks for all the replies. Looks like I have to nix the calculated fields in the table. I can do what I want in the data entry form itself.