Date Parameter SQL

  • Thread starter Thread starter Scott M.
  • Start date Start date
S

Scott M.

If anyone who knows SQL would be so kind as to write a
statement for me, this is what I need:

I have two date boxes on a form, one to specify the lower
of the date, and the other the upper limit. I wish to be
able to place dates in both and the query return all
matches between those dates...simple enough. However, I
also need to be able to leave, say, the lower date limit
blank, and it include ALL records with dates below the
upper limit (in other words, the lower limit is null).
The problem I have is that I don't know how to write that
in SQL, and I mess all my other criteria up when I place
it in the query grid. I found the article online that
addresses this, but it only gives design grid
instructions :(

(here's the link to the article, jic)
http://office.microsoft.com/search/redir.aspx?
AssetID=XT010984991033&CTT=5&Origin=HA011170771033

TIA,
-Scott
 
WHERE (TestSponsor.[Date] >= Forms!TestSearch!Date1
OR Forms!TestSearch!Date1 IS NULL)
AND (TestSponsor.[Date] <= Forms!TestSearch!Date2
OR Forms!TestSearch!Date2 IS NULL);
 
If anyone who knows SQL would be so kind as to write a
statement for me, this is what I need:

I have two date boxes on a form, one to specify the lower
of the date, and the other the upper limit. I wish to be
able to place dates in both and the query return all
matches between those dates...simple enough. However, I
also need to be able to leave, say, the lower date limit
blank, and it include ALL records with dates below the
upper limit (in other words, the lower limit is null).
The problem I have is that I don't know how to write that
in SQL, and I mess all my other criteria up when I place
it in the query grid. I found the article online that
addresses this, but it only gives design grid
instructions :(

(here's the link to the article, jic)
http://office.microsoft.com/search/redir.aspx?
AssetID=XT010984991033&CTT=5&Origin=HA011170771033

TIA,
-Scott
Click to expand...

If you are using a form for the entry of the dates, to me, the
simplest way is to use the form to give the empty control a valid date
earlier than the earliest possible date in the table.

Code the command button on the form:
If IsNull([StartDate]) Then
[StartDate] = #1/1/1900#
End If

Then continue with what ever code your command button has to run the
query.

The query parameter remains the same:
Between forms!ParameterForm!StartDate and forms!ParameterForm!EndDate

The above will either return dates from Jan 1, 1900 if the control was
left blank, or from whatever date the user enters.

If you wish to do this wholly within the query, then you could use:
Between
IIf(IsNull(forms!ParameterForm!StartDate),#01/01/1900#,forms!ParameterForm!StartDate)
AND forms!ParameterForm!EndDate
 
Hi,

I am still a novice, but I have done a similar thing in one of my projects.

In the SQL builder place the following statement :

SELECT DISTINCT TOP 1 tblYourTable.Yourdatefield

FROM tblYourTable

ORDER BY tblYourTable.Yourdatefield;

Save as something like qryMaxdate.

Then do the same with the following SQL :

SELECT DISTINCT TOP 1 tblYourTable.Yourdatefield

FROM tblYourTable

ORDER BY tblYourTable.Yourdatefield DESC;

Save as qryMindate

At your command button, you code the same as suggested before, except now
use a lookup to get the first and last dates in your database.

If IsNull([StartDate]) Then
[StartDate] = Dlookup("Yourdatefield","qryMindate")
End If

If IsNull([EndDate]) Then
[EndDate] = Dlookup("Yourdatefield","qryMaxdate")
End If

Just a thought.........will achieve the same as suggested by Fred.

Kenny


fredg said:
If anyone who knows SQL would be so kind as to write a
statement for me, this is what I need:

I have two date boxes on a form, one to specify the lower
of the date, and the other the upper limit. I wish to be
able to place dates in both and the query return all
matches between those dates...simple enough. However, I
also need to be able to leave, say, the lower date limit
blank, and it include ALL records with dates below the
upper limit (in other words, the lower limit is null).
The problem I have is that I don't know how to write that
in SQL, and I mess all my other criteria up when I place
it in the query grid. I found the article online that
addresses this, but it only gives design grid
instructions :(

(here's the link to the article, jic)
http://office.microsoft.com/search/redir.aspx?
AssetID=XT010984991033&CTT=5&Origin=HA011170771033

TIA,
-Scott
Click to expand...

If you are using a form for the entry of the dates, to me, the
simplest way is to use the form to give the empty control a valid date
earlier than the earliest possible date in the table.

Code the command button on the form:
If IsNull([StartDate]) Then
[StartDate] = #1/1/1900#
End If

Then continue with what ever code your command button has to run the
query.

The query parameter remains the same:
Between forms!ParameterForm!StartDate and forms!ParameterForm!EndDate

The above will either return dates from Jan 1, 1900 if the control was
left blank, or from whatever date the user enters.

If you wish to do this wholly within the query, then you could use:
Between
IIf(IsNull(forms!ParameterForm!StartDate),#01/01/1900#,forms!ParameterForm!S
tartDate)
AND forms!ParameterForm!EndDate
Click to expand...
 
Back
Top