Filtering a Calculated Field

[Neilbees]

Hi

I've had a search through the forum but can't find anything to solve my issue. Hope someone can help with this one.

I want to filter a query (ultimately a report) based on date parameters entered by the user (start date & end date) on a form. I've done this plenty of times before with no problems. However, this time it's returning incorrect information - stuff that doesn't fall witin the date parameters of the filter.

I've checked other databases I've built and everything looks the same. The only thing I can think of is that I'm asking the query to filter a calculated field rather than a field with information entered by the user. Is this what might be causing the problem?

The field that the filter looks at is a calculated field that calculates a date 6 weeks before another date entered by the user.

Hope I've provided enough info for someone to think they know what's wrong!

Cheers, as always.

 

[Rabbie]

Are your dates stored as text fields or Date Fields. If you have used the Format function they will be in Text(String) format and don't sort/filter the way you expect.

This is very similar to several threads that have been posted in last 2 weeks. so it may help you to do a search. Unfortunately some threads do not have helpful titles.

 

[Neilbees]

Cheers Rabbie. I'll check the formatting and have another search of the forums.

 

[Jon K]

I want to filter a query (ultimately a report) based on date parameters entered by the user (start date & end date) on a form.
..................

The field that the filter looks at is a calculated field that calculates a date 6 weeks before another date entered by the user.

If the Format() function has not been used in the calculated field expression and parameters, then probably the filter expression has led Access to think that the parameters entered are two text strings.

You can try declaring the data type of the parameters used in the query. In query Design View, select menu Query, Parameters... In the dialog box that pops up, type each parameter in the left column and select the correct data type in the right column. Click Ok to close the dialog.
.

 

[Neilbees]

Hi again, can't seem to work this one out, despite searching through the forum and trying several suggestions.

To summarise what I have. In tblMaster I have a field called GoLiveDate - formatted as Date/Time in the table. In qryMaster I have a calculated field DateReceivedTarget that works out a date 6 weeks before the GoLiveDate using the DateAdd function. All works fine.

In a form frmReports I have 2 text boxes txtStartDate and txtEndDate for users to enter the date parameters for another query qryProductsDue_DateFiltered (to produce a report). In this query I have the DateReceivedTarget field with criteria set to Between [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate].

However, as an example, when I enter 01/07/2007 and 01/08/2007 in the text boxes, the query returns a record with a DateReceivedTarget of 25/05/2007!

Can anyone explain exactly what I need to do to sort this out? I'm still an Access novice so assume I have no idea whatsoever!

 

[neileg]

The query design window in Access is a graphical tool for writing SQL (structured query language). Unfortunately, it will allow you to create a query that doesn't produce good SQL, as you have found.

When you create a caculated field, you create what is known as an alias, eg:
MyNewField: [FieldA]*[FieldB]
In this case MyNewField is an alias.

When you apply a filter by adding a criterion to the field, the SQL that is generated uses the alias in the WHERE clause of the query. So if you wanted values greater than 10, the SQL would look like:
WHERE MyNewField >10
Unfortunately this doesn't work in SQL because until the query runs, MyNewField doesn't exist.

So what you have to do is substitute the calculation for the alias, so your SQL becomes:
WHERE ([FieldA]*[FieldB]) > 10

 

[Neilbees]

Thanks for the reply. I substituted the calculation for the alias as you suggested but I'm still getting records that do not correspond with the date filters entered in the form.

For example, I enter 01/08/2007-01/09/2007 and I get records between 25/05/2007 and 27/07/2007.

I have no idea what's going on here!

 

[Rabbie]

If [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate] are txt fields as the names suggest you need to make sure that the SQL string has them surrounded with # like this

strSQL = " first bit of your SQL"

strSQl = strSQL & "WHERE #" & [Forms]![frmReports]![txtStartDate] & "# And #" & [Forms]![frmReports]![txtEndDate] & "#"


Good luck.

 

[Jon K]

Can you post a stripped version of your database, preferably in Access 2000 format, with just some sample data, the queries and the form for us to have a look at it?
.