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  

Parameters to search between years



 
 
Thread Tools Display Modes
  #1  
Old May 8th, 2010, 06:21 PM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default Parameters to search between years

Hi Everyone,

Access 2003. I have a query that I have set up so the user can type in
in a txtbox (in a form) the year (string) and the query workks great. This
is my parameter:
Like [Forms]![Search].[txtYyear] & "*"

1. I would like to include a criteria to search between two years in the
same txtbx. Could you suggest me how to do it?

2.In the same query I have another parameter:
Like [Forms]![Search].[txtTwn] & "*"
This allow me to write only one word in a txtbx same form), but I would like
to write two or three words.Could you help me?

Your help is greatly appreciated


.. They pull data for the month so the first date they enter
is 3/1/10 and the end date is 3/31/10. I want all records between these
dates but anything that is 3/31 doesn't show up inless I type in 4/1. Is
there a way I can add something in the expression so it automatically adds
the one day?
  #2  
Old May 9th, 2010, 01:08 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Parameters to search between years

On Sat, 8 May 2010 10:21:01 -0700, sebastico
wrote:

Hi Everyone,

Access 2003. I have a query that I have set up so the user can type in
in a txtbox (in a form) the year (string) and the query workks great. This
is my parameter:
Like [Forms]![Search].[txtYyear] & "*"

1. I would like to include a criteria to search between two years in the
same txtbx. Could you suggest me how to do it?

2.In the same query I have another parameter:
Like [Forms]![Search].[txtTwn] & "*"
This allow me to write only one word in a txtbx same form), but I would like
to write two or three words.Could you help me?

Your help is greatly appreciated


. They pull data for the month so the first date they enter
is 3/1/10 and the end date is 3/31/10. I want all records between these
dates but anything that is 3/31 doesn't show up inless I type in 4/1. Is
there a way I can add something in the expression so it automatically adds
the one day?


Date/Time fields are not stored as strings. Instead, they're stored as numbers
- a count of days and fractions of a day (times) since midnight, December 30,
1899. If you use a LIKE operator Access will convert the number to a formatted
date string and search that, but you might not get the results you expect!

Try using a Form with two unbound textboxes txtStartDate and txtEndDate. To
pick up date/time values *on* txtEndDate (i.e. later than midnight on that
date but before midnight the next night), you can use a query criterion

= NZ([Forms]![Search]![txtStart], #1/1/100#) AND DateAdd("d", 1, NZ([Forms]![Search]![txtEnd], #12/30/9999#))


If the txtStart box is left blank it will find all records up to the end of
the day on txtEnd; if txtEnd is left blank, it will find all records on or
later than txtStart.
--

John W. Vinson [MVP]
  #3  
Old June 2nd, 2010, 07:45 PM posted to microsoft.public.access.queries
sebastico
external usenet poster
 
Posts: 74
Default Parameters to search between years


I tested this parameter and works as well.
Many thanks. I learned more with your help
"sebastico" wrote:

Hi Everyone,

Access 2003. I have a query that I have set up so the user can type in
in a txtbox (in a form) the year (string) and the query workks great. This
is my parameter:
Like [Forms]![Search].[txtYyear] & "*"

1. I would like to include a criteria to search between two years in the
same txtbx. Could you suggest me how to do it?

2.In the same query I have another parameter:
Like [Forms]![Search].[txtTwn] & "*"
This allow me to write only one word in a txtbx same form), but I would like
to write two or three words.Could you help me?

Your help is greatly appreciated


. They pull data for the month so the first date they enter
is 3/1/10 and the end date is 3/31/10. I want all records between these
dates but anything that is 3/31 doesn't show up inless I type in 4/1. Is
there a way I can add something in the expression so it automatically adds
the one day?

 




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 10:44 PM.


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