Setting default values in a query criteria form?

[Matt_O]

Hi Folks,

I'm sure there is a simple explanation but I am stumped on how to ensure that a default value is used if a user doesn't select a start or end date in a form that feeds the between dates criteria in a simple query. Thanks in advance to anyone who can answer or point me to helpful documentation.

My form is called DateRange_form and has 3 Unbound text boxes for Start (txtStartDate), End (txtEndDate), and Clients (cboClients).

My query criteria for dates is: Between [Forms]![DateRange_form]![txtStartDate] And [Forms]![DateRange_form]![txtEndDate]
My query criteria for clients is: [Forms]![DateRange_form]![cboClients]

In the properties window for txtStartDate I set the Default Value to #1/1/2019# - have also tried using the = sign in front of #1/1/2019#
For txtEndDate I set the Default Value to = Date()

My form has two command buttons. One clears all data using VBA e.g. Me.txtStartDate = ""
The other button runs the query.

When I only select a client name from the combo box and leave the date fields empty I get an error message about the expression is typed incorrectly or is too complex to be evaluated.

The form works if I select dates from the date picker.

How come it appears as though the default values are not being recognized?

Bonus questions...

I also can't figure out how to set a default wildcard for the Clients combo box so if no client is select the query will pull up all clients within the date range.

Finally, I'm new to Access and I am unfamiliar with the ways to create and run macros, code, or expressions. Sometimes I'm offered 3 options when I select the build function in a properties sheet. Using VBA in Excel I can verify that a variable contains the correct data by looking at the properties window or doing a Debug.Print function as I walk through the code. I'm making the assumption that the unbound text box txtStartDate would be considered my first date variable. Is there a way I can see what value is about to be passed as criteria for the query?

Thanks,
Matt

 

[welshgasman]

AFAIAA default values are only good for new record.?

In the button click event, set the values there before running the query.

You can use the same debugging techniques in Access if using VBA.

 

[Matt_O]

Thanks Welshgasman,

First, thanks for replying!

From what I can tell, when I first open the form the default data set in the property window for the two text fields now appears. I didn't notice this yesterday as I constantly tried to refresh the data and see if the text fields would get populated with the default values. I still need to find a way to re-populate the text fields with default values without closing and reopening the form.

Also, thanks for the note about debugging in VBA. I'll keep playing around and try figure this out.

Matt

 

[welshgasman]

Well from the little you have said, you could set them in the query button Click event if they are empty or set them in the Load Event of the form and the clear button event.?

When you are opening the form, you must be on a new record.?

FWIW I think these controls should be unbound?

EDit:
I've just created an unbound form and default values of Date()-14 and Date() for start and end dates are then on form open. Reset them on the clear button.

 

[Matt_O]

Thanks Welshgasman,

I set the values for the two date text fields to load with a button click in VBA. I now don't need to close and re-open the form to see the default date values.

I was also able to add a second criteria of having a Null value for the Client combo box and search all clients if the user doesn't select a client. The odd thing is that I originally had the following all as a single criteria in the query criteria.

[Forms]![DateRange_form]![cboClients] Or Like [Forms]![DateRange_form]![cboClients] Is Null

Access removed everything from "Or" to "Is Null" and placed it in a new column and created a hidden field shown as this:

([Scripts_Table].[Clients]) Like [Forms]![DateRange_form]![cboClients]

In the 'Or' row for criteria Access placed "Is Null". I'm not sure why this happened but the query functions as it should for having no clients selected from the combo box.

My new challenge is to determine why the date parameters are not followed when using the Null value for clients.

When I have a start date of 1/1/2019 and and end date of 1/31/2019 and the Client field is left empty my query returns all clients and all dates from 1/1/2019 to 4/25/2019. I wonder if there's some type of hierarchy whereby the last criteria (reading left to right in query design view) trumps the other criteria. This doesn't seem logical to me but I am new to Access.

Matt

 

[welshgasman]

Not that I am aware of.

It is probably down to your logic with ANDs and ORs ?
If you have OR Client equal NULL then that is what I would expect?