Searching for records between two given dates 1

[Michaelcree]

Hi I need to be able to search an Access database table for the occurances of an Item Code between two given dates.

I have created a form with three fields and one button on txtItemCode txtItemCodeReportStartDate txtItemCodeReportEndDate
cmdRunItemCodeReport

I want the user to enter a Item Code in the txtItemCode textbox a start and end date and on clicking the button open a report to display all the occurances of that Item code within the date range.

I have managed to get it to work searching with just the ItemCode but am now not sure how to add the date variables in?

This is the code I have so far:

Private Sub cmdRunItemCodeReport_Click()
If TxtItemCode > 0 Then
DoCmd.OpenReport "rptItemCodes", , , ReportResults
TxtItemCode = ""
Else:
MsgBox "Please enter an Item Code"
End If
End Sub

Private Function ReportResults() As String
If Len(TxtItemCode) <> 0 And Not IsNull(TxtItemCode) Then
ReportResults = ReportResults & " (tblReturns.ItemCode LIKE '%" & Trim(TxtItemCode) & "%' "
' ReportResults = ReportResults & " And tblReturns.ReceivedDate LIKE '%" & (DATEDIFF(dd, txtItemCodeReportStartDate, txtItemCodeReportEndDate) & "%' "
ReportResults = ReportResults & ")"
End If
End Function

Many Thanks for anyhelp in advance.

 

[IGPCS]

what you can do you can base the report you want to gentrate based on the sql builder and in the date criteria wirte something like this

 between [form1]![txtItemCodeReportStartDate] and [form1]![txtItemCodeReportEndDate]

hope you got it

 

[Michaelcree]

I think I understand Is this where you open the report based on a query of the table?

And in the criteria field for the date add in

between [frmReports]![txtItemCodeReportStartDate] and [frmReports]![txtItemCodeReportEndDate]

I have tried this but it gives amessage "Your entry cannot be converted to a valid date time value"

Would this be because nothing is yet in the txtfields to be converted

 

[IGPCS]

is it the field where the criteria based on is a date field and if yes is it only the date or also time

 

[IGPCS]

sorry,

yes please try to ruin this query when the form is open and there is data in the txt boxes

 

[Michaelcree]

Tried that but when you go to save the qry a message is displayed "Your entry cannot be converted to a valid date time value

between [frmReports]![txtItemCodeReportStartDate] and [frmReports]![txtItemCodeReportEndDate]

 

[IGPCS]

can you paste your sql here please i will try to review it

 

[IGPCS]

what ms access version are you using is it xp or 2003

 

[randy700]

between [COLOR=red]forms![/color][frmReports]![txtItemCodeReportStartDate] and [COLOR=red]forms![/color][frmReports]![txtItemCodeReportEndDate]

Randy

 

[Michaelcree]

Hi

Thanks for all your help

In the end a friend helped me solve the issue with the following amendments to the code as follows

Private Function ReportResults() As String
If Len(TxtItemCode) <> 0 And Not IsNull(TxtItemCode) Then
ReportResults = ReportResults & " (tblReturns.ItemCode LIKE '%" & Trim(TxtItemCode) & "%' "
If IsDate(txtItemCodeReportStartDate) Then
ReportResults = ReportResults & " And ReceivedDate>CONVERT(DATETIME, '" & txtItemCodeReportStartDate & "', 103)"
End If
If IsDate(txtItemCodeReportEndDate) Then
ReportResults = ReportResults & " And ReceivedDate<CONVERT(DATETIME, '" & txtItemCodeReportEndDate & "', 103)"
End If
ReportResults = ReportResults & ")"
End If
End Function

Once again many thanks for your help

 

[WildHare]

MichaelCree - Please pardon me for jumping in here, perhaps your problem is solved, and you don't need any latecomers mucking up the works, but after reading this thread, I think I can suggest a perhaps easier and more error-proof way of solving your problem. However, as noted, please feel free to discard this suggestion as you see fit....

1) Make the ItemCode guy a COMBO BOX of the valid ITEMCODES, making the selection easier. Keystroke errors are to be avoided at all costs, and users can always be trusted to type "XGTK-12990" when they might mean "XGKT-12990" - combo boxes make keystroke errors much less likely.

This will obviate the need for your "If LEN(txtItemCode)..." stuff - just check for a NULL combo box:

...If IsNull(Me!Combo1) then ....

Next, assuming a form name of "Form1" for simplicity...

2) Run your report based on a prewritten QUERY, with critieria for the ItemCode equivalent to "Forms!Form1!Combo1" and a criteria string in your ReceiveDate field of
Between #" & Forms!Form1!txtItemCodeReportStartDate "# and #" & Forms!Form1!txtItemCodeReportEndDate & "#"


3) Change your form's text guys names to "StartDate" and "EndDate", making the above code-let so much smaller:

Between #" & Forms!Form1!StartDate "# and #" & Forms!Form1!EndDate & "#"


Now, your button need only check for a null combo box, and then run the report. (Assuming your report is based on your query, with the two criteria as outlined above...)

I just quiver when I see code like "..if TxtItemCode > 0..." because that only tests for a non-blank-ish item code - not a VALID item code..what if your user types in "1", but there is no item code "1" - all that code goes to waste...

It's much better to "force" a correct entry via a combo box, than simply test for a "non-empty" text box, and go from there, don't you think?

Anyway, just my .02 worth...thanks for listening.

Jim



--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman