string as date parameter for query 2

[schnigger]

Hi all,

I'm also getting crazy:
I'm trying to base a query for a date field on a string that has been entered by the user using a form, e.g. "> #1/1/2000#".

How can I directly use this string as criteria for the respective query? 'DateValue(Date_String)' doesn't work cos I have to include the &quot;>&quot; or &quot;<&quot; - symbols.....

anybody out there to help me?
Thx!

 

[LarryDeLaruelle]

Schnigger:

Are you using this in a query or in an SQL statement?

If in a query, simply reference the form/text control in the criteria cell of the query as:

>[Forms]![frmFormName]![txtControlName]

You can use the expression builder to help with the syntax; right click in the criteria cell and select Build then select the form and control.

You don't need to add the pound signs, Access will do that for you.

If in an SQL statement:

&quot;SELECT * FROM tblTableName WHERE DateField = #&quot; & txtControlName & &quot;#;&quot;

In this case, you do need to enclose the date string within the pound signs.

Hope this helps. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[schnigger]

Hi Larry,
thx for your reply. I would like to use it in a query since I have quite a number of search options in my form and creating a proper SQL statement is getting complicated and confusing.

The solutions you suggest work fine for other fields but in the special case of the date fields the problem is that the criteria is basing on 2 control fields in the form: one for the choice &quot;before&quot; or &quot;after (resulting in &quot;<&quot; or &quot;>&quot, one for the choice of the year &quot;1999&quot; or &quot;2000&quot;...

and I don't achieve in basing my criteria for the date in the query on these 2 control boxes - or on the one string Variable in that I can combine it, e.g. : &quot;< 2000&quot;

any further ideas?
cheers!

schnigger

 

[LarryDeLaruelle]

Schnigger:

Two questions.

Are the date fields full dates or only the year?

Is the search parameter using AND or OR? That is, before 1999 AND after 2000; before 1999 OR after 2000? Or, is there only one criteria for the year?

Let me know and we'll see what we can do.
Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[schnigger]

The date fields are full dates, format: dd/mm/yyyy.

My database is on projects including one date field for the start and one for the end of each project. I want to allow the user to apply search functions like:

&quot;StartDate before 1999 AND EndDate before 2002&quot;
or: &quot;StartDate after 1990 AND EndDate after 2000&quot;

What I don't need:
&quot;StartDate before 1999 AND Startdate after 2001&quot;

If there is no solution for this problem by using directly the queries I might go back to the SQL-Statements - I left this path when I had too many problems to base my SQL-SELECT statement on more than 2 tables connected with the joint options....

thx again

schnigger

 

[LarryDeLaruelle]

schnigger:

Ok. That clears it up a bit.

First, in the query or SQL you will need to create a field for the year portion of the date field using DatePart:

In the query set two field names to:
LowDate: DatePart(&quot;YYYY&quot;, StartDate); and
HiDate: DatePart(&quot;YYYY&quot;, EndDate)

You can now set the criteria for each date field on the year only.

In SQL (I'm not positive on the syntax) DatePart(&quot;YYYY&quot;, StartDate) as LowDate and DatePart(&quot;YYYY&quot;, DateEnd) as HiDate.

Does this help? Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[schnigger]

HI Larry,

thanks, to create the respective SQL statement should not be a problem.

The question is if there is the possibility to avoid writing this SQL code but entering this criteria directly into the line &quot;criteria&quot; of my query (in Design format). Meaning if it is possible to base the filter of my date field in the query directly on a string Var that contains the symbols &quot;>&quot; or &quot;<&quot;.....

Schnigger

 

[LarryDeLaruelle]

Schnigger:

If you do what I suggested in the first part of may last response (create two columns in the query for the year part of each date field) you can then set references in the criteria cells to the controls on your form where the years are selected/entered by the user.

The format would be:

<[Forms]![frmFormName]![txtLowDate]
>[Forms]![frmFormName]![txtHiDate]

or however you need to limit the selections.

If you right click in the criteria cell and select 'Build', a dialog will open that allows you to enter the conditionals and select the appropriate controls from your form. Thus avoiding syntax errors. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[Guest_imported]

Sir,

I want a query which has to fetch me a results like

August 28,2001
july 29,2001
june 30, 2001
may 29, 2001
april 30, 2001
August 27,2001
july 27,2001
june 24, 2001
may 21, 2001
april 26, 2001
August 24,2001
july 23,2001
june 22, 2001
may 19, 2001
april 22, 2001
August 23,2001
july 21,2001
june 20, 2001
may 18, 2001
april 20, 2001

If I need to get a result in this format what shall i do, What is going to be the query that will me the result in this jumbled way.

Regards

Ananth

 

[LarryDeLaruelle]

Ananth:

Sorry for the delay in responding, I've been on vacation.

The data are stored in either Primary Key order, if a Primary Key has been set, or natural (entry) order, if no key has been set. Getting a 'jumbled' result, as you describe, may be difficult.

From your post, it sounds like you want a random selection and ordering of data. Is that the case? Larry De Laruelle
ldelaruelle@familychildrenscenter.org