Help Please ..... !!!

[razaqad]

Im stuck with this problem for the past 2 days ....... plz plz plz help me ......!!!

i have a table containing "production_orders". With feilds (BatchNumber, IssueDate) and some other fields

I want to get thoses production orders who fall between the two given dates.

The user inputs these dates on the 2 textboxes i have mentioned above. Or selects one of the frame options eg. this Month, Last Month so these textboxes are filled automatically with dates Plz check the attached picture

But the query is not working correctly. There is some problem with the dates.

i have built some funtions to get dates eg ( First date of current month , fisrt date of previous month, last date of previous month, last date of current month).

CritIssueDt = " Where ((Production_Orders.IssueDate) Between [Forms]![Production Menu]![StartDate] And [Forms]![Production Menu]![EndDate])"

Now towards the real problem:
I have checked that the date funtions return the dates correctly. The functions first update the text boxes and the date values for criteria are taken from the text boxes and not the funtions dirctly. So I'm confident that the funtions for date calculation are working fine.

For example: the user selects "This Month" option then text boxes are alloted values 01/03/2006 and 31/03/2006 respectively. Then the listbox record source if requeryed.

But incorrect records are retieved. After requry of listbox record source, I have checked the criteria in the query editor. The dates are changed to 03/01/2006 and 03/31/2006 respectively. I have even tried to format these dates eg. format(StatDate,"dd/mm/yyyy") before passing dates to the query.

The Short Date Format in the regional settings is dd/MM/yyyy.

I am using access 2003 but my database file is Acess 2000 File. My Operating System = WinXp

Plz help me

 

[razaqad]

Functions for date calculation

'previous month first date
Public Function PreMonthFirst()
PreMonthFirst = "01/" & Month(PreMonthLast) & "/" & Year(PreMonthLast)
End Function

'previous month last date
Public Function PreMonthLast()
Dt = Date
PreMonthLast = DateAdd("d", -DatePart("d", Dt), Dt)
End Function

' current month first date
Public Function CurMonthFirst()
Dt = Date
CurMonthFirst = DateAdd("d", -DatePart("d", Dt) + 1, Dt)
End Function

Public Function CurMonthLast()
Dt = Date
CurMonthLast = DateAdd("d", -1, DateAdd("m", 1, DateAdd("d", -DatePart("d", Dt) + 1, Dt)))
End Function

 

[The_Doc_Man]

The usual reasons for a search to return nothing or perhaps the unexpected have to do with the comparands. (Things being compared.) The oddball cases occur when the comparands are not both the same exact data type or are not conversions of the input to the same exact data type.

For instance, if your IssueDate field is a Date type but the item in the form is a text representation of that date, you will get back garbage or nothing. In other words, in your query, are you comparing a formatted date/time to an unformatted date/time? Frequently, when one of your items comes from a text box on a form, it is formatted.

 

[razaqad]

you are right. The issue date is a date feild.

but i have tried to pass the dates like
datevalue(startdate) and
datevalue( format(startdate))

But still not working.

Doc MAn plase help me.... Im in great trouble.

 

[Jon K]

CritIssueDt = " Where ((Production_Orders.IssueDate) Between [Forms]![Production Menu]![StartDate] And [Forms]![Production Menu]![EndDate])"

You can keep the above criteria in the SQL statement of the list box Row Source rather than converting them to 01/03/2006 and 31/03/2006.

If you really want to convert them to 01/03/2006 and 31/03/2006 in the SQL statement, you can pass each date as a string to the DateValue() function.


I have attached a small example with a form. On a UK date system, you can enter the StartDate and EndDate 1/3/2006 and 31/3/2006 and click on one of the two search buttons to display the March 2006 records in the list box.

The first button uses your CritIssueDt string in the code:

   CritIssueDt = " Where ((Production_Orders.IssueDate)" & _
                 " Between [Forms]![Production Menu]![StartDate]" & _
                 " And [Forms]![Production Menu]![EndDate])"
   
   SQL = "Select * from [Production_Orders]" & CritIssueDt

   Me.lstSearchResults.RowSource = SQL

The second button uses the DateValue() function:

   CritIssueDt = " Where Production_Orders.IssueDate" & _
       " Between DateValue('" & [Forms]![Production Menu]![StartDate] & "')" & _
       " And DateValue('" & [Forms]![Production Menu]![EndDate] & "')"
   
   SQL = "Select * from [Production_Orders]" & CritIssueDt

   Me.lstSearchResults.RowSource = SQL

Both should produce the same search results.

They should also work on systems using US date format.
.

 

[razaqad]

Thank you ...... JON

I am very thankful to you for taking your time to solve my problem.

Both the methods worked perfectly alright.....

U Rock ....!!