makes table with dates chosen by the user (Access 2000)

Topic

New Reply

Make table with dates chosen by the user

I have copied from a book a very nice looking form containing a calendar and 2 controls, named
txtBeginDate and txtEndDate. When i choose a date from the calendar, for example 06.02.2001, this date appears on the control,and then the same for the txtEnddate.
I have a maketable function and i want very much to tie up my function with the calendar,but i do not know why,if possible at all.

My maketable function is the folowing:
Dim strOrders As String
Dim strWhere As String

strWhere = ” WHERE ((([orders].[orderdate]) between >#1/1/2001# And #1/2/2001));” ‘ the setting is dd.M.yyyy
‘ ====================================

strOrders = ” SELECT orders.orderid, orders.customerid, orders.orderdate, orders.[required date], orders.SalesTaxRate, orders.freigth,

orders.paymentid, orders.PaymentMethodID, orders.bankid, orders.FreightCharge, orders.invoicedate, orders.AuftragNr INTO orders1

FROM orders”

CurrentDb.Execute strOrders & strWhere
End Function

My question is, can i pass the values chosen in the controls txtBeginDate and txtEndDate into my maketable function,
in order to obtain a new table based on the values i have chosen in the calendar form.If i succedd i could gain a big flexibility
for the user to choose dates.

Reply | Quote

Replies

Yes, but how you do it depends on exactly what you want to do. If you want to call the function from the form containing the calendar control, it would be simple to pass the two dates as arguments into your function. An alternative would be to call the function while the form is open, either hidden or otherwise, and refer to the two textboxes to get the date values.

Reply | Quote

Thank you very much for your kind reply.I have tried to substitute the controls in my function,but still
i receive the error 3075 missing item in query expression.

What have i done.In my working strWhere expression
strWhere = ” WHERE ((([orders].[orderdate])>#1/1/2001#));”
i have replaced the right side part of >#1/1/2001#)) with between Forms![frmDateRange![BeginDate] And

Forms![frmDateRange![EndDate

So my new strWhere looks like this, but does not work:

strWhere = ” WHERE ((([orders].[orderdate]) between >#” & txtBeginDate & “# And #” & txtEndDate & “#));”

So my mistake lies somwehere in the above code.I cannot understand.

My previous working function was the following

Public Function FncRecentTables()
Dim strOrders As String
Dim strOrderDetails As String
Dim strWhere As String
‘******************** note that the strWhere is valid for all the tables so it could be changed only once

strWhere = ” WHERE ((([orders].[orderdate])>#1/1/2001#));” ‘ the setting is dd.M.yyyy
‘ ====================================

strOrders = ” SELECT orders.orderid, orders.customerid, orders.orderdate, orders.[required date], orders.SalesTaxRate, orders.freigth,

orders.paymentid, orders.PaymentMethodID, orders.bankid, orders.FreightCharge, orders.invoicedate, orders.AuftragNr INTO orders1

FROM orders”
CurrentDb.Execute strOrders & strWhere
End Function
I simply cannot find my way out of this problem

May i wish you a merry Christmas

Reply | Quote

Hi,
Try removing the > sign after the word ‘between’ – so your line should read:

strWhere = ” WHERE ((([orders].[orderdate]) between #” & txtBeginDate & “# And #” & txtEndDate & “#));”

Hope that helps.

Reply | Quote