I am picking up data from a table that satisfy my date condition thru following code.
Set rst = CurrentDb.OpenRecordset("SELECT * FROM T_PaperRoll_Consumption " & _
"WHERE ((ShiftDate)> #03/08/2006#) " & _
"order by RolNo, RollSize;")
This runs fine.
Now, I have placed 2 textboxes named "From" and "To" on form F_PeriodicPaper_Consum.
I need to know what would be the code in Where clause of the above code if I need to get the data from same table but the date range must be pick up from form F_PeriodicPaper_Consum's text boxes i.e. "From" and "To" ?
You can refer to the values in ojbects on your current form using Me!... So the value in the box called From would be Me!From.
So you code becomes...
Set rst = CurrentDb.OpenRecordset("SELECT * FROM T_PaperRoll_Consumption WHERE ((ShiftDate)> #" & Me!From & "# And ShiftDate< #" & Me!To & "# ) order by RolNo, RollSize;")
I've put it all on one line for clarity. If you are going to put this on seperate lines, make sure you break it up in the string parts not the value parts.
I've a feeling you might have a problem with the date being in non-USA format i.e. I think you have to feed the date in USA format mm/dd/yyyy. There's some code out there somewhere for converting. But try it and see.
Regarding the date format I am comfortable with UK date format as my client like the same.
You might like it and your client might like it too but Bill just doesn't.
Try entering a few dates that could be interpreted either as dd/mm/yyyy or mm/dd/yyyy (e.g. 4th september, 12th July etc) and scrutinse the results. Be certain the records that are returned are correct.