Access 2010 Query default value

myhc
myhc used Ask the Experts™
on
So, i have a basic form from query. In my query (from mem) i have the following code.
I would like to have a date default in this box...  how do I set the default input?

I have many other fields like [OS]  I would to default to say e.g "Windows 7"


like "*" & [Enter Date] & "*"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:

for default values( criteria)

where [OS]="Windows 7"


in graphical view


field      [OS]
table     NameOftable

Criteria  "Windows 7"

Author

Commented:
This picks 7. I want it to prompt with default of win 7
Top Expert 2016

Commented:
if you want a prompt, you have to type in the value ...

field      [OS]
table     NameOftable

Criteria  [Enter OS]


Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
So you can't have a prompt with a defult value. I'm sure you could fo this in 2003...
Top Expert 2016

Commented:
<I'm sure you could fo this in 2003...>

if you can do this in 2003, just adapt what you did in A2003. But i doubt that you can do this.
Top Expert 2016

Commented:
it does not make sense that you will prompt for a value, and set the default value.
might as well use the format i posted above at http:#a36485874 

Author

Commented:
Sure if I still had my db from back in that job.

Not being rude. But how well do you know access. Just need to know if your word will save me more google time or if your just not sure.

Commented:
WHERE myDate = Nz(MyDate,#2011-09-05#)

given the default you are looking for is the date 2011-09-05, you will be prompted for the parameter MyDate, if you choose not to enter a date and hit ENTER, #2011-09-05# will be provided.  You can use the same approach for your operating system question.  The solution is not to use LIKE but the function Nz().
Top Expert 2016

Commented:
yes that will work but you will not see the "default value" in the input box of the prompt.

Author

Commented:
But the query will bring back some data. I could add a note about the default in the input title.

What is Nz standing for?
Top Expert 2016

Commented:
well, i guess i did not fully understandd what you really wanted
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
You cant do what you are asking through the 'input box' that pops up when running a stored query (ie: all SQL, no Visual basic)

However, CAN build up your criteria by defining you query in Visual Basic code and using the VBA inputbox function, which allows a 'default value':

InputBox("Enter the OS criteria","OS","Windows 7")

That said, if you have many criteria that need to be defined like this, I would suggest steering away from the input boxes, and creating a "filter form", with textboxes, combos, etc for all of the fields that you are allowing as criteria.  Any of these controls can be set up to display a default value.  You would add a button to open the form whose recordsource you are defining with this query.

The code behind that button would be:

Docmd.openform "YourFormName",,,"DateField=#" & me.txtDate & "# AND OperatingSystem = '" & me.txtOS & "' " 

etc.

Commented:
Nz() is a function which replaces a null value with anything you like.  Press Alt+F11 to get to the VB editor and in help type - Nz function.  

On a form, report, as a default value in a table - there are a number of instances where it fits the problem - this appeared to be one of them.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial