Calculated Fields in Criteria Expression

[TheStriker]

Hello,

I have a calculated field named 'WeekOf' that calculates the beginning day of the week (Sunday). Now I want to grab all records using a "Between" statement with the calculated field as the beginning date of a date range.

Between [WeekOf] And Date()

Even though 'WeekOf' is a field it still prompts me to enter the WeekOf parameter. Am I missing something or does it NOT work with calculated field. If not, how can I get it to grab the records of a the week regardless of the date.

Thanks in advance

 

[mgolla]

To use the calculated field in a query you would have to create a second query that uses the first query as the recordsource.

HTH
Mike

 

[GoDawgs]

Just use the formula for the "WeekOf" field in place of [weekof] in the between statement.

Assuming your WeekOf field is this:

[DateField]-Weekday([DateField])+1

Then change your criteria statement to this:

Between ([DateField]-Weekday([DateField])+1) and Date()

Hope that helps.

Kevin

 

[TheStriker]

Thanks for your response, I have created the second query and that worked OK however, I am trying to use the calculated field as the beginning date of a 'BETWEEN' statement. Like this:

WeekOf:Format([myDateField]-Weekday([MyDateField])+1,'mm/dd/yyyy',1)

Between [WeekOf] And Date()

Result: All records are returned.

Even though I see that there are only about 5 records that meet this criteria, it still returns all records. Am I missing something?