Querying All Data When Form Field Is Blank

[jre1229]

I have a report that is feeding from a query. The query using criteria from my form with the following code:

IIf([Forms]![frmBids]![BidDateSelection]="","7/19/2012",[Forms]![frmBids]![BidDateSelection])

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 have tried using "" and Null but neither work. Does anyone know what the field value is when there is no entry?

[ssanfu]

If you used "[Forms]![frmBids]![BidDateSelection]= NULL", it will always return False. Nothing is equal to NULL, not even NULL...

Try this:

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

[jre1229]

If you used "[Forms]![frmBids]![BidDateSelection]= NULL", it will always return False. Nothing is equal to NULL, not even NULL...

Try this:

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

I tried this but it said "This expression is typed incorrectly, or it is too complex to be evaluated".

[ssanfu]

I should learn to read better.

In a query, you don't refer to a control on a form, but to a field in the query (in this instance). For criteria in a query, you can refer to a control on a form.
So the column in the query should be:

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

Change "SomeName" to what you want the column name to be.

[jre1229]

I should learn to read better.

In a query, you don't refer to a control on a form, but to a field in the query (in this instance). For criteria in a query, you can refer to a control on a form.
So the column in the query should be:

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

Change "SomeName" to what you want the column name to be.

I'm a little confused. I understand that I need to add that as a new column, which I did as below:

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

But then do I have to set my original field, which is BidDate, equal to "SomeName" in order to get the query to pull the selected date?

[ssanfu]

No..
Remove the references to the form. You can't reference the form. Reference the field in the query

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

[jre1229]

No..
Remove the references to the form. You can't reference the form. Reference the field in the query

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

So how do I pass the value from the for to "BidDateSelection"? It pops up with a prompt wanting an entry but I want the entry to come from the form.

[ssanfu]

So how do I pass the value from the for to "BidDateSelection"? It pops up with a prompt wanting an entry but I want the entry to come from the form.

I am not understanding what you are trying to do.

So:
You have a report.
The row source for the report is a query.
The query has a field named "BidDateSelection".
It has a date or is NULL.

For each record returned in the query:
In a new column in the query, if the field "BidDateSelection" is NULL, then you want a date entered of "7/19/2012".
If the field "BidDateSelection" is NOT NULL, then the date in the new column should be the date in the field "BidDateSelection".

Is this correct?

[jre1229]

Not really. Let me walk through it.
I have a report
The row source for the report is a query
The query has a field named "BidDateSelection"
This field has a date, there are no NULL fields

I then have on my form a date picker
I was using 7/19 as a place holder but what would be better is if the date picker is left blank, for it to pull all records
Otherwise it would pull the date selected.

I am not understanding what you are trying to do.

So:
You have a report.
The row source for the report is a query.
The query has a field named "BidDateSelection".
It has a date or is NULL.

For each record returned in the query:
In a new column in the query, if the field "BidDateSelection" is NULL, then you want a date entered of "7/19/2012".
If the field "BidDateSelection" is NOT NULL, then the date in the new column should be the date in the field "BidDateSelection".

Is this correct?

[ssanfu]

I then have on my form a date picker

What is the name of the date picker control? If it is "BidDateSelection', change it to something else.

[jre1229]

What is the name of the date picker control? If it is "BidDateSelection', change it to something else.

It is called "BidDateSelector", I'm completely confused. I attached a stripped down version of my DB. Use the shift key to open it and look at the frmBids form. There is a date and vendor on the bottom right, this is where I am trying to do this.

[ssanfu]

I have A2K/A2K3 and can't open the attached accmdb.
Try this:

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

[jre1229]

I have A2K/A2K3 and can't open the attached accmdb.
Try this:

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

All I get is an error saying invalid bracketing on the BidDateSelector

[orange]

Just a guess, but try removing all square brackets ( "[" and "]" ).

[jre1229]

All I get is an error saying invalid bracketing on the BidDateSelector

Nope, it asks me to enter the parameter again.