Between date and date query do not show values, how change this?

L

ldiaz

Between date and date query do not show values when field criterias are null

I have a query with this criteria.
====================================================
Between [Forms]![frm_Shipment_SystemAllDatas]![StartDate] And
[Forms]![frm_Shipment_SystemAllDatas]![EndDate]
====================================================

this shows values from the date entered in the StartDate and EndDate, the
problem is that when the main form is loaded StartDate and EndDate are null
therefore the subform conected to this qyery do not show any value,

I want all datas on the subform even when StartDate and EndDate are null at
the time to load the form

and the criteria must be used after to fill StartDate and EndDate and make a
requery

I was using this criteria in query:
====================================================
Like Nz (>=[Forms]![frm_Shipment_SystemAllDatas]![StartDate] And
<=[Forms]![frm_Shipment_SystemAllDatas]![EndDate],"*")
====================================================
but I just noticed it does not work..


please Help..
 
K

Ken Snell \(MVP\)

Add criteria for the Null condition:

Between [Forms]![frm_Shipment_SystemAllDatas]![StartDate] And
[Forms]![frm_Shipment_SystemAllDatas]![EndDate] Or
([Forms]![frm_Shipment_SystemAllDatas]![StartDate] Is Null And
[Forms]![frm_Shipment_SystemAllDatas]![EndDate] Is Null)
 
J

John W. Vinson/MVP

I want all datas on the subform even when StartDate and EndDate are null
at
the time to load the form
Click to expand...

Consider setting the DefaultValue properties of the StartDate and EndDate
form controls to a very early and very late date respectively - either a
date range that makes sense for your business rules, or to cover absolutely
everything, #1/1/100# and #12/31/9999#.
 
K

Ken Sheridan

Lorenzo:

A possible alternative would be along these lines:

PARAMETERS
[Forms]![frm_Shipment_SystemAllDatas]![StartDate] DATETIME,
[Forms]![frm_Shipment_SystemAllDatas]![EndDate] DATETIME;
SELECT *
FROM YourTable
WHERE
(YourDateField >= [Forms]![frm_Shipment_SystemAllDatas]![StartDate]
OR [Forms]![frm_Shipment_SystemAllDatas]![StartDate] IS NULL)
AND
(YourDateField <= [Forms]![frm_Shipment_SystemAllDatas]![EndDate]
OR [Forms]![frm_Shipment_SystemAllDatas]![EndDate] IS NULL);

This would make both parameters optional, i.e. if only a start date is
entered then all rows with dates on or after that would be returned, if only
an end date is entered then all rows with dates on or before that would be
returned. If both are entered then rows with dates between the two are
returned. If neither are entered all rows are returned.

Note that the parameters are declared. This is a good idea with date/time
values as otherwise a date parameter entered in short date format could be
interpreted as an arithmetical expression and give the wrong results.

In the AfterUpdate event procedures of both the StartDate and EndDate
controls requery the subform, e.g.

Me.YourSubformControl.Requery

Finally one precaution I'd advise you to take is to make sure that only
dates with a zero time of day can be entered into the table. You can do this
with a validation rule in the table design requiring YourDateField to equal
DateValue([YourDateField]). If any dates on the final day of the range
inadvertently had a non-zero time of day, which you would not see if the date
is formatted as a date format without times, then these would not be returned
by the query if they fall on the final day of the range.

Ken Sheridan
Stafford, England

ldiaz said:
Between date and date query do not show values when field criterias are null

I have a query with this criteria.
====================================================
Between [Forms]![frm_Shipment_SystemAllDatas]![StartDate] And
[Forms]![frm_Shipment_SystemAllDatas]![EndDate]
====================================================

this shows values from the date entered in the StartDate and EndDate, the
problem is that when the main form is loaded StartDate and EndDate are null
therefore the subform conected to this qyery do not show any value,

I want all datas on the subform even when StartDate and EndDate are null at
the time to load the form

and the criteria must be used after to fill StartDate and EndDate and make a
requery

I was using this criteria in query:
====================================================
Like Nz (>=[Forms]![frm_Shipment_SystemAllDatas]![StartDate] And
<=[Forms]![frm_Shipment_SystemAllDatas]![EndDate],"*")
====================================================
but I just noticed it does not work..


please Help..
Click to expand...
 
L

ldiaz

This works perfectly,

Thanks Ken, also Thanks other MVPs that answered my question.

Sincerely

--
Lorenzo Díaz
Cad Technician


Ken Snell (MVP) said:
Add criteria for the Null condition:

Between [Forms]![frm_Shipment_SystemAllDatas]![StartDate] And
[Forms]![frm_Shipment_SystemAllDatas]![EndDate] Or
([Forms]![frm_Shipment_SystemAllDatas]![StartDate] Is Null And
[Forms]![frm_Shipment_SystemAllDatas]![EndDate] Is Null)
--

Ken Snell
<MS ACCESS MVP>




ldiaz said:
Between date and date query do not show values when field criterias are
null

I have a query with this criteria.
====================================================
Between [Forms]![frm_Shipment_SystemAllDatas]![StartDate] And
[Forms]![frm_Shipment_SystemAllDatas]![EndDate]
====================================================

this shows values from the date entered in the StartDate and EndDate, the
problem is that when the main form is loaded StartDate and EndDate are
null
therefore the subform conected to this qyery do not show any value,

I want all datas on the subform even when StartDate and EndDate are null
at
the time to load the form

and the criteria must be used after to fill StartDate and EndDate and make
a
requery

I was using this criteria in query:
====================================================
Like Nz (>=[Forms]![frm_Shipment_SystemAllDatas]![StartDate] And
<=[Forms]![frm_Shipment_SystemAllDatas]![EndDate],"*")
====================================================
but I just noticed it does not work..


please Help..
Click to expand...
Click to expand...
 
L

ldiaz

I used this before but after to close the form and open it again,
the date appears as 01/01/2007 12:00:00 Am and didn't works.

maybe I forgot to use # #, by the moment I have fixed the issue with Ken's
answer, but I really appreciate your answer and support to this newsgroup.

Thanks
 
L

ldiaz

Hi Sheridan, This code works perfectly,

Thanks for your help.
Have an excellent Day

sincerely..
--
Lorenzo Díaz
Cad Technician


Ken Sheridan said:
Lorenzo:

A possible alternative would be along these lines:

PARAMETERS
[Forms]![frm_Shipment_SystemAllDatas]![StartDate] DATETIME,
[Forms]![frm_Shipment_SystemAllDatas]![EndDate] DATETIME;
SELECT *
FROM YourTable
WHERE
(YourDateField >= [Forms]![frm_Shipment_SystemAllDatas]![StartDate]
OR [Forms]![frm_Shipment_SystemAllDatas]![StartDate] IS NULL)
AND
(YourDateField <= [Forms]![frm_Shipment_SystemAllDatas]![EndDate]
OR [Forms]![frm_Shipment_SystemAllDatas]![EndDate] IS NULL);

This would make both parameters optional, i.e. if only a start date is
entered then all rows with dates on or after that would be returned, if only
an end date is entered then all rows with dates on or before that would be
returned. If both are entered then rows with dates between the two are
returned. If neither are entered all rows are returned.

Note that the parameters are declared. This is a good idea with date/time
values as otherwise a date parameter entered in short date format could be
interpreted as an arithmetical expression and give the wrong results.

In the AfterUpdate event procedures of both the StartDate and EndDate
controls requery the subform, e.g.

Me.YourSubformControl.Requery

Finally one precaution I'd advise you to take is to make sure that only
dates with a zero time of day can be entered into the table. You can do this
with a validation rule in the table design requiring YourDateField to equal
DateValue([YourDateField]). If any dates on the final day of the range
inadvertently had a non-zero time of day, which you would not see if the date
is formatted as a date format without times, then these would not be returned
by the query if they fall on the final day of the range.

Ken Sheridan
Stafford, England

ldiaz said:
Between date and date query do not show values when field criterias are null

I have a query with this criteria.
====================================================
Between [Forms]![frm_Shipment_SystemAllDatas]![StartDate] And
[Forms]![frm_Shipment_SystemAllDatas]![EndDate]
====================================================

this shows values from the date entered in the StartDate and EndDate, the
problem is that when the main form is loaded StartDate and EndDate are null
therefore the subform conected to this qyery do not show any value,

I want all datas on the subform even when StartDate and EndDate are null at
the time to load the form

and the criteria must be used after to fill StartDate and EndDate and make a
requery

I was using this criteria in query:
====================================================
Like Nz (>=[Forms]![frm_Shipment_SystemAllDatas]![StartDate] And
<=[Forms]![frm_Shipment_SystemAllDatas]![EndDate],"*")
====================================================
but I just noticed it does not work..


please Help..
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform based in a query criteria is blank at the time to load, ho 3
Parameter Query 2
Problem with 1 Query with Criteria feeding another query 3
Date Range Query. Criteria based in a table. 4
Dates from a Table in Query 4
MS Office cant find object "DoCmd" 27
Parameter from Form 6
Report help 8

Top