A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Offer a Default in a Criteria



 
 
Thread Tools Display Modes
  #1  
Old July 10th, 2008, 02:24 AM posted to microsoft.public.access.queries
Ross[_3_]
external usenet poster
 
Posts: 11
Default Offer a Default in a Criteria

Hello all,

I want to create a query that offers a Default in the criteria under a Date
field, for example:

[Enter Date From] "default as Date today -7"

This will allow the user to either press Enter for all records a week ago
or type in the Date Required.

Ross


  #2  
Old July 10th, 2008, 03:12 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default Offer a Default in a Criteria

You cannot assign a default value to parameters in JET, but you can craft
the criterion so as to use a specific value if left blank.

The expression in the Criteria row under your date/time field would be
something like this:
= IIf([StartDate] Is Null, Date() - 7, [StartDate])


Be sure to declare the parameters (Parameters on the Query menu), so JET
knows it's a Date/Time type. It will not work correctly if JET treats it as
a text value.

To be honest, I rarely use parameters like that. If you use a form, you
don't get multiple parameter boxes popping up one after the other, you can
choose which ones to use more easily, you can set default values, you can
use combo boxes, you can validate sensible entries, and you can more easily
build filter strings for forms/reports to ignore the boxes the user left
blank. Here's an example:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ross" wrote in message
...

I want to create a query that offers a Default in the criteria under a
Date field, for example:

[Enter Date From] "default as Date today -7"

This will allow the user to either press Enter for all records a week ago
or type in the Date Required.


  #3  
Old July 10th, 2008, 03:40 AM posted to microsoft.public.access.queries
Ross[_3_]
external usenet poster
 
Posts: 11
Default Offer a Default in a Criteria

Allen,

Again, thanks for the Help and after all that you are correct I will do it
in a Form with other Criteria as the users now want extra's.

Regards,

Ross

"Allen Browne" wrote in message
...
You cannot assign a default value to parameters in JET, but you can craft
the criterion so as to use a specific value if left blank.

The expression in the Criteria row under your date/time field would be
something like this:
= IIf([StartDate] Is Null, Date() - 7, [StartDate])


Be sure to declare the parameters (Parameters on the Query menu), so JET
knows it's a Date/Time type. It will not work correctly if JET treats it
as a text value.

To be honest, I rarely use parameters like that. If you use a form, you
don't get multiple parameter boxes popping up one after the other, you can
choose which ones to use more easily, you can set default values, you can
use combo boxes, you can validate sensible entries, and you can more
easily build filter strings for forms/reports to ignore the boxes the user
left blank. Here's an example:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ross" wrote in message
...

I want to create a query that offers a Default in the criteria under a
Date field, for example:

[Enter Date From] "default as Date today -7"

This will allow the user to either press Enter for all records a week
ago or type in the Date Required.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.