Unbound fields in queries - Access 2000

Mirage · 10 Apr 2007 at 16:12

I'm attempting to run a query based on criteria in unbound fields, for some reason the query does not even pick up the values.

The fields are [Forms]![frmStatus_Report]![txtFrom] and [txtTo], both are Formatted as Short Date.
The relating field in the query is [tblCase]![CheckDate]

I want the query to return all records where CheckDate is >=[txtFrom] and <=[txtDate], so I currently have:

Field: CheckDate
Table: tblCase
Total: Where
Criteria: >=[Forms]![frmStatus_Report]![txtFrom] And <=[Forms]![frmStatus_Report]![txtTo]

but it returns no records (there are definately records between the dates I've entered.

Even if I create an expression to just return the dates nothing appears, if I remove the ShortDate format then the values of the fields are returned but obviously ShortDate is a requirement.

Can anyone shed some light?

Cheers.

|Ric| · 10 Apr 2007 at 17:54

Maybe try:
Criteria: >=#[Forms]![frmStatus_Report]![txtFrom]# And <=#[Forms]![frmStatus_Report]![txtTo]#

|Ric| · 10 Apr 2007 at 17:57

This could be drivel, I haven't done this kind of thing for ages

Mirage · 11 Apr 2007 at 06:43

I've tried that, the problem I'm having is that the fields simply return nothing, i,e. when I do Expr1: [Forms]![frmStatus_Report]![txtFrom] and run it then it produces an empty Expr1 field for all rows.

If I take the Small Date format off the text box it returns the value, problem is I need to enforce dd/mm/yyyy format, and I need the "/"s to be in there (using just an input mask isn't enough as it removes the "/").

Mirage · 11 Apr 2007 at 11:04

Just incase anyone ever has this problem and needs a solution.

I simply removed the SmallDate format, enforced 00/00/0000 as input mask.
When output by a query it would give the value 04042007 (it had removed the "/"), which when converted to date would be incorrect.

To get around this I formatted the field value:
From: Format(Format([Forms]![frmReport_Status]![txtFrom], "00\/00\/0000"), "d mmmm yyyy")

Which first adds the "/" in and then converts to the format I want.

|Ric| · 11 Apr 2007 at 11:25

Yeh I was trying to avoid that nastiness

On the basis that it only breaks when you make it a small data it is probably something to do with the #'s because that's how to specify a date in the SQL.
Just can't remember how to use them in this situation