Querying All Data When Form Field Is Blank

[ssanfu]

My bad... I didn't see an extra bracket in front of FORMS:

SomeDate:IIf(Len(Trim([[Forms]![frmBids].[BidDateSelector] & ""))=0,"7/19/2012",[BidDateSelection])

This doesn't error out:

SomeDate:IIf(Len(Trim([Forms]![frmBids].[BidDateSelector] & ""))=0,"7/19/2012",[BidDateSelection])

From your first post:

What I am trying to do, is have the "BidDateSelection" set to 7/19/2012 when the field is blank but my code does not work. I

I would think the formula would be:

SomeDate:IIf(Len(Trim([BidDateSelection] & ""))=0,"7/19/2012",[BidDateSelection])

[jre1229]

Both of these gives me the information for 7/19 regardless of the date I selected on the "BidDateSelector"

[orange]

Can you post a copy of your data base - remove any confidential/personal info?

Can you post the code for the Query that gets data for the report?

[jre1229]

Can you post a copy of your data base - remove any confidential/personal info?

Can you post the code for the Query that gets data for the report?

Here is an .mdb version that should work. Copy of 8-2.zip

[orange]

I looked at your database.
Error onLoad see attached.
Error when trying to open Form see attached.

I can not open you database without errors. If i try to open a Form I also get an error.
I think, based on my small test, you have serious structure issues.

I suggest you tell us in 3-5 lines what exactly this database is for, so that we can get some feel for what it is you are trying to do.
You may want to tell us about your tables, forms and reports as well.

[jre1229]

I looked at your database.
Error onLoad see attached.
Error when trying to open Form see attached.

I can not open you database without errors. If i try to open a Form I also get an error.
I think, based on my small test, you have serious structure issues.

I suggest you tell us in 3-5 lines what exactly this database is for, so that we can get some feel for what it is you are trying to do.
You may want to tell us about your tables, forms and reports as well.

The error onLoad came from me taking out 90% of the database due to confidential information. I'm not sure why you get the second error, as I don't get that error. I am going to try and make this as simple as possible using a separate example, maybe I can better relay it that way.

I have a form, called "FORM"
On FORM, I have a datepicker, called "DATEPICKER"
On FORM, I have a button, called "BUTTON"
On FORM, I have a subreport, called "SUBREPORT"
The data for this subreport pulls from a query, that populates from a table. We will call these "TABLE" and "QUERY".
In TABLE, I have a field called "DATEFIELD" along with other fields that aren't relevant. QUERY pulls all fields from TABLE.

What I am trying to do, is select a date in DATEPICKER, click BUTTON to update SUBREPORT. What I want QUERY to pull, is all data where DATEFIELD = DATEPICKER.VALUE. If DATEPICKER is left blank, and BUTTON is clicked, I want QUERY to show me all data.

That's about as simple and straightforward as I can come up with.

[orange]

But you didn't attach anything...?

[jre1229]

No, what did you want me to attach? I was just describing a hypothetical situation to try and better explain what I am doing.

[ssanfu]

On FORM, I have a subreport, called "SUBREPORT"

You need to start by changing your design. You cannot have a subREPORT on a form - on a form, it *MUST* be a subFORM.

What I am trying to do, is select a date in DATEPICKER, click BUTTON to update SUBREPORT. What I want QUERY to pull, is all data where DATEFIELD = DATEPICKER.VALUE. If DATEPICKER is left blank, and BUTTON is clicked, I want QUERY to show me all data.

Can you post the SQL of the query?

See this site
http://access.mvps.org/access/queries/qry0001.htm

[jre1229]

You need to start by changing your design. You cannot have a subREPORT on a form - on a form, it *MUST* be a subFORM.


Can you post the SQL of the query?

See this site
http://access.mvps.org/access/queries/qry0001.htm

Ok, the subreport is actually in a subform, I just left that part out. I don't have code for the SQL of the query as I was just making up a simple example of what I am trying to do without getting everyone caught up in how I was naming everything in my actual DB.

[jre1229]

You need to start by changing your design. You cannot have a subREPORT on a form - on a form, it *MUST* be a subFORM.

Can you post the SQL of the query?

See this site
http://access.mvps.org/access/queries/qry0001.htm

I looked at the link you posted and used they code they have and it worked perfectly! Thanks!