Solved

Access 2010 Query default value

Posted on 2011-09-05
13
328 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:myhc
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36485874

for default values( criteria)

where [OS]="Windows 7"


in graphical view


field      [OS]
table     NameOftable

Criteria  "Windows 7"
0
 
LVL 7

Author Comment

by:myhc
ID: 36485892
This picks 7. I want it to prompt with default of win 7
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36485919
if you want a prompt, you have to type in the value ...

field      [OS]
table     NameOftable

Criteria  [Enter OS]


0
 
LVL 7

Author Comment

by:myhc
ID: 36485927
So you can't have a prompt with a defult value. I'm sure you could fo this in 2003...
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36485932
<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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36485941
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Author Comment

by:myhc
ID: 36485943
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36485954
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().
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36485965
yes that will work but you will not see the "default value" in the input box of the prompt.
0
 
LVL 7

Author Comment

by:myhc
ID: 36485975
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?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36486126
well, i guess i did not fully understandd what you really wanted
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36486167
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.

0
 
LVL 44

Expert Comment

by:GRayL
ID: 36496493
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.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now